在postgre里操纵大字段 by quqi

package jcss.search.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import jcss.search.base.DBBaseName;
import jcss.search.base.DataSourceUtil;
import jcss.search.base.Debug;
import jcss.search.base.PKSeq;
import jcss.search.exception.DBOperationException;
import jcss.search.model.BodyPartsModel;

import org.apache.log4j.Logger;
import org.postgresql.PGConnection;
import org.postgresql.largeobject.LargeObject;
import org.postgresql.largeobject.LargeObjectManager;

/*
 * @作者：张华
 * @日期：2007-6-5
 * @说明：
 */
public class BodyPartsDAO {

 Connection con = null;

 PreparedStatement pstmt = null;

 int cid = 0;

 // log4j日志管理
 static Logger log = Logger.getLogger(BodyPartsDAO.class.getName());

 // 连接数据库
 private Connection getConnection() throws DBOperationException {
  try {
   Connection conn = DataSourceUtil.getConnection();
   return conn;
  } catch (Exception e) {
   Debug.print(e);
   throw new DBOperationException("－－获得连接时出错!－－/n" + e.getMessage());
  }
 }

 // 连接数据库，用于测试用
 // public static Connection getConnection(){
 // Connection conn = null;
 // try {
 // Class.forName("org.postgresql.Driver");
 // conn =
 // DriverManager.getConnection("jdbc:postgresql://172.17.1.209:5432/cssdb",
 // "postgres","password");
 // } catch (Exception e) {
 // e.printStackTrace();
 // }
 // return conn;
 // }

 // 插入信息
 public BodyPartsModel addContentParts(BodyPartsModel model)
   throws DBOperationException {
  if (con == null)
   con = this.getConnection();
  try {
   // 创建大对象
   int contentOid = this.writeLargeObject(model.getContent());

   // 插入分类信息
   int id = PKSeq.getNextPublicId().intValue();
   // int id = PKSeq.getNextPublicIdForTest().intValue();
   model.setId(id);

   pstmt = con.prepareStatement("insert into "
     + DBBaseName.DB_CONTENT_PARTS + " values(?,?,?,?,?)");
   pstmt.setInt(1, id);
   pstmt.setInt(2, model.getContentId());
   pstmt.setString(3, model.getType());
   pstmt.setString(4, model.getName());
   pstmt.setInt(5, contentOid);

   pstmt.executeUpdate();
   pstmt.close();

   // 关闭数据库
   con.close();
   con = null;
   // log.info("增加信息（"+model.getTitle()+"）成功！");
   // 返回结果
   return model;
  } catch (Exception e) {
   try {
    if (con != null && !con.isClosed()) {
     if (pstmt != null) {
      pstmt.close();
      pstmt = null;
     }
     con.close();
     con = null;
    }
   } catch (SQLException sqle) {
    Debug.print(sqle);
    throw new DBOperationException("--关闭数据库连接时出错！--/n"
      + sqle.getMessage());
   }
   Debug.print(e);
   throw new DBOperationException("－－增加分类信息时出错!－－/n" + e.getMessage());
  }
 }

 // 修改信息
 public BodyPartsModel updateContentParts(BodyPartsModel model)
   throws DBOperationException {
  if (con == null)
   con = this.getConnection();
  try {
   int id = model.getId();

   int contentOid = 0;
   // 获得大对象oid
   pstmt = con.prepareStatement("select content from "
     + DBBaseName.DB_CONTENT_PARTS + " where id=?");
   pstmt.setInt(1, id);
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    contentOid = rs.getInt("content");
   }
   // 删除大对象
   this.deleteLargeObject(contentOid);
   // 创建大对象
   contentOid = this.writeLargeObject(model.getContent());

   pstmt = con.prepareStatement("update "
     + DBBaseName.DB_CONTENT_PARTS + cid
     + " set content_id=?,type=?,"
     + "name=?,content=? where id=?");

   pstmt.setInt(1, model.getContentId());
   pstmt.setString(2, model.getType());
   pstmt.setString(3, model.getName());
   pstmt.setInt(4, contentOid);
   pstmt.setInt(5, id);

   pstmt.executeUpdate();
   pstmt.close();

   // 关闭数据库
   con.close();
   con = null;
   log.info("修改信息（" + model.getContentId() + "）成功！");
   // 返回结果
   return model;
  } catch (Exception e) {
   try {
    if (con != null && !con.isClosed()) {
     if (pstmt != null) {
      pstmt.close();
      pstmt = null;
     }
     con.close();
     con = null;
    }
   } catch (SQLException sqle) {
    Debug.print(sqle);
    throw new DBOperationException("--关闭数据库连接时出错！--/n"
      + sqle.getMessage());
   }
   Debug.print(e);
   throw new DBOperationException("－－修改信息时出错!－－/n" + e.getMessage());
  }
 }

 // 根据id查找信息,model放基本信息，如大对象文件
 public BodyPartsModel searchContentPartsById(int id)
   throws DBOperationException {
  if (con == null)
   con = this.getConnection();

  BodyPartsModel model = null;
  try {
   // 查找信息
   pstmt = con.prepareStatement("select * from "
     + DBBaseName.DB_CONTENT_PARTS + " where id=?");
   pstmt.setInt(1, id);

   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    model = new BodyPartsModel();

    model.setId(rs.getInt("id"));
    model.setContentId(rs.getInt("content_id"));
    model.setType(rs.getString("type"));
    model.setName(rs.getString("name"));
    model.setContent(this.readLargeObject(rs.getInt("content")));

   }
   rs.close();
   pstmt.close();

   // 关闭数据库
   con.close();
   con = null;
   // 返回结果
   return model;
  } catch (Exception e) {
   try {
    if (con != null && !con.isClosed()) {
     if (pstmt != null) {
      pstmt.close();
      pstmt = null;
     }
     con.close();
     con = null;
    }
   } catch (SQLException sqle) {
    Debug.print(sqle);
    throw new DBOperationException("--关闭数据库连接时出错！--/n"
      + sqle.getMessage());
   }
   Debug.print(e);
   throw new DBOperationException("－－根据id查找规则信息时出错!－－/n"
     + e.getMessage());
  }
 }

 /**
  * 找到一篇资源下的相应type的附件
  *
  * @param id
  * @param type
  * @return
  * @throws DBOperationException
  */
 public List searchContentPartsByIdAndType(int content_id, String type)
   throws DBOperationException {
  if (con == null)
   con = this.getConnection();

  List result = new ArrayList();
  try {
   // 查找信息
   pstmt = con.prepareStatement("select * from "
     + DBBaseName.DB_CONTENT_PARTS
     + " where content_id=? and type=?");
   pstmt.setInt(1, content_id);
   pstmt.setString(2, type);
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    BodyPartsModel model = new BodyPartsModel();

    model.setId(rs.getInt("id"));
    model.setContentId(content_id);
    model.setType(type);
    model.setName(rs.getString("name"));
    model.setContent(this.readLargeObject(rs.getInt("content")));

    result.add(model);
   }
   rs.close();
   pstmt.close();

   // 关闭数据库
   con.close();
   con = null;

  } catch (Exception e) {
   try {
    if (con != null && !con.isClosed()) {
     if (pstmt != null) {
      pstmt.close();
      pstmt = null;
     }
     con.close();
     con = null;
    }
   } catch (SQLException sqle) {
    Debug.print(sqle);
    throw new DBOperationException("--关闭数据库连接时出错！--/n"
      + sqle.getMessage());
   }
   Debug.print(e);
   throw new DBOperationException("－－根据id查找规则信息时出错!－－/n"
     + e.getMessage());
  }
  return result;
 }

 // 统计所有信息
 public int getCount() throws DBOperationException {
  if (con == null)
   con = this.getConnection();

  int count = 0;
  try {
   // 查找信息
   pstmt = con.prepareStatement("select count(*) from "
     + DBBaseName.DB_CONTENT_PARTS);

   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    count = rs.getInt(1);
   }
   rs.close();
   pstmt.close();

   // 关闭数据库
   con.close();
   con = null;
   // 返回结果
   return count;
  } catch (Exception e) {
   try {
    if (con != null && !con.isClosed()) {
     if (pstmt != null) {
      pstmt.close();
      pstmt = null;
     }
     con.close();
     con = null;
    }
   } catch (SQLException sqle) {
    Debug.print(sqle);
    throw new DBOperationException("--关闭数据库连接时出错！--/n"
      + sqle.getMessage());
   }
   Debug.print(e);
   throw new DBOperationException("－－统计规则信息时出错!－－/n" + e.getMessage());
  }
 }

 /*
  * 根据信息id删除信息 输入id 输出
  */
 public void delContentPartsByid(int id) throws DBOperationException {
  if (con == null)
   con = this.getConnection();

  try {
   int contentOid = 0;
   // 获得大对象oid
   pstmt = con.prepareStatement("select content from "
     + DBBaseName.DB_CONTENT_PARTS + " where id=?");
   pstmt.setInt(1, id);
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    contentOid = rs.getInt("content");
   }
   // 删除大对象
   this.deleteLargeObject(contentOid);

   // 删除信息
   pstmt = con.prepareStatement("delete from "
     + DBBaseName.DB_CONTENT_PARTS + " where id=?");
   pstmt.setInt(1, id);
   pstmt.executeUpdate();
   pstmt.close();

   // 关闭数据库
   con.close();
   con = null;
   // 返回结果

  } catch (Exception e) {
   try {
    if (con != null && !con.isClosed()) {
     con.close();
     con = null;
    }
   } catch (SQLException sqle) {
    Debug.print(sqle);
    throw new DBOperationException("--关闭数据库连接时出错！--/n"
      + sqle.getMessage());
   }
   Debug.print(e);
   throw new DBOperationException("－－根据规则id删除规则信息时出错!－－/n"
     + e.getMessage());
  }
 }

 /**
  * 插入大字段，postgre关于oid的处理
  * @param input
  * @return
  * @throws DBOperationException
  */
 private int writeLargeObject(byte[] input) throws DBOperationException {
  Debug.println("writing a large object !/n");
  LargeObjectManager lobj = null;
  int oid_int = 0;

  Connection con = this.getConnection();
  try {
   // All LargeObject API calls must be within a transaction block
   con.setAutoCommit(false);

   Debug.println("Gaining access to large object api");
   //用数据源，确保将池的属性accessToUnderlyingConnectionAllowed设为true
   PGConnection pgCon =(PGConnection)((org.apache.commons.dbcp.DelegatingConnection)con).getInnermostDelegate();
   lobj = pgCon.getLargeObjectAPI();
   //用JDBC
   //lobj = ((org.postgresql.PGConnection) con).getLargeObjectAPI();
   Debug.println("Creating a large object");
   oid_int = lobj.create(LargeObjectManager.READ
     | LargeObjectManager.WRITE);
   DriverManager.println("got large object oid=" + oid_int);

   LargeObject obj = lobj.open(oid_int, LargeObjectManager.WRITE);
   DriverManager.println("got large object obj=" + obj);

   byte buf[] = input;
   obj.write(buf);

   // Close the object
   Debug.println("Closing object");
   obj.close();

   // Finally, commit the transaction.
   con.commit();
   con.close();
   con = null;
  } catch (Exception e) {

   Debug.print(e);
   throw new DBOperationException("－－写入大对象信息时出错!－－/n" + e.getMessage());
  } finally {
   try {
    if (con != null && !con.isClosed()) {
     con.close();
     con = null;
    }
   } catch (SQLException sqle) {
    Debug.print(sqle);
    throw new DBOperationException("--关闭数据库连接时出错！--/n"
      + sqle.getMessage());
   }
  }
  return oid_int;
 }

 private byte[] readLargeObject(int oid) throws DBOperationException {
  Debug.println("read a large object !/n");
  LargeObjectManager lobj = null;
  byte[] result = null;
  Connection con = this.getConnection();
  try {
   // All LargeObject API calls must be within a transaction block
   con.setAutoCommit(false);
   Debug.println("Gaining access to large object api");
//   用数据源，确保将池的属性accessToUnderlyingConnectionAllowed设为true
   PGConnection pgCon =(PGConnection)((org.apache.commons.dbcp.DelegatingConnection)con).getInnermostDelegate();
   lobj = pgCon.getLargeObjectAPI();
   //用JDBC
   //lobj = ((org.postgresql.PGConnection) con).getLargeObjectAPI();
   LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
   DriverManager.println("got large object obj=" + obj);
   // copy the data
   Debug.println("Copying large object to file");
   // byte buf[] = new byte[512];
   // int totalSize = obj.size();
   // result = new byte[totalSize];
   // int pos = 0;
   // int tl = 0;
   // while (totalSize > 0)
   // {
   // int rs = buf.length;
   // if (totalSize < rs)
   // rs = totalSize;
   // obj.read(buf, 0, rs);
   // int bufSize = buf.length;
   // for(int i=0;i<bufSize;i++){
   // result[pos] = buf[i];
   // pos++;
   // }
   // tl += rs;
   // totalSize -= rs;
   // }
   int totalSize = obj.size();
   result = new byte[totalSize];
   obj.read(result, 0, totalSize);

   // Close the object
   Debug.println("Closing object");
   obj.close();

   // Finally, commit the transaction.
   con.commit();
   con.close();
   con = null;
  } catch (Exception e) {
   try {
    if (con != null && !con.isClosed()) {
     con.close();
     con = null;
    }
   } catch (SQLException sqle) {
    Debug.print(sqle);
    throw new DBOperationException("--关闭数据库连接时出错！--/n"
      + sqle.getMessage());
   }
   Debug.print(e);
   throw new DBOperationException("－－读取大对象信息时出错!－－/n" + e.getMessage());
  }
  return result;
 }

 // 删除大对象
 private void deleteLargeObject(int oid) throws DBOperationException {
  Debug.println("deleting a large object");

  LargeObjectManager lobj = null;

  Connection con = this.getConnection();
  try {
   // All LargeObject API calls must be within a transaction block
   Debug.println("Connected... First turn off autoCommit()");
   con.setAutoCommit(false);

   Debug.println("Gaining access to large object api");
//   用数据源，确保将池的属性accessToUnderlyingConnectionAllowed设为true
   PGConnection pgCon =(PGConnection)((org.apache.commons.dbcp.DelegatingConnection)con).getInnermostDelegate();
   lobj = pgCon.getLargeObjectAPI();
   //用JDBC
   //lobj = ((org.postgresql.PGConnection) con).getLargeObjectAPI();

   // deleting large object
   Debug.println("deleting large object from LargeObjectManager!");
   lobj.delete(oid);

   // Finally, commit the transaction.
   con.commit();
   con.close();
   con = null;
  } catch (Exception e) {
   try {
    if (con != null && !con.isClosed()) {
     con.close();
     con = null;
    }
   } catch (SQLException sqle) {
    Debug.print(sqle);
    throw new DBOperationException("--关闭数据库连接时出错！--/n"
      + sqle.getMessage());
   }
   Debug.print(e);
   throw new DBOperationException("－－删除大对象信息时出错!－－/n" + e.getMessage());
  }
 }

 /*
  * 删除所有信息 输出
  */
 public void removeAll() throws DBOperationException {
  if (con == null)
   con = this.getConnection();

  try {
   int contentOid = 0;
   // 获得大对象oid
   pstmt = con.prepareStatement("select content from "
     + DBBaseName.DB_CONTENT_PARTS);
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    contentOid = rs.getInt("content");
    // 删除大对象
    this.deleteLargeObject(contentOid);
   }

   // 删除信息
   pstmt = con.prepareStatement("delete from "
     + DBBaseName.DB_CONTENT_PARTS);
   pstmt.executeUpdate();
   pstmt.close();
   con.commit();
   // 关闭数据库
   con.close();
   con = null;
   // 返回结果

  } catch (Exception e) {
   try {
    if (con != null && !con.isClosed()) {
     con.close();
     con = null;
    }
   } catch (SQLException sqle) {
    Debug.print(sqle);
    throw new DBOperationException("--关闭数据库连接时出错！--/n"
      + sqle.getMessage());
   }
   Debug.print(e);
   throw new DBOperationException("－－根据规则id删除规则信息时出错!－－/n"
     + e.getMessage());
  }
 }

 public static void main(String[] args) {
  BodyPartsDAO test = new BodyPartsDAO();
  BodyPartsModel model = new BodyPartsModel();
  model.setId(1111);
  model.setContentId(2222);
  model.setType("1");
  model.setName("zhang");
  model.setContent("me".getBytes());

  try {
   // 添加
   test.addContentParts(model);

   // 读取
   BodyPartsModel aaa = test.searchContentPartsById(5757);
   System.out.println(aaa.getContent().toString());

   // 更新

  } catch (DBOperationException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

 }

}
 

 

 

数据库配置文件(注意必须将accessToUnderlyingConnectionAllowed设为true)：

<?xml version='1.0' encoding='utf-8'?>
<Context path="/cssweb" docBase="D:/workspace3.1.2/cssweb/WebContent" workDir="D:/workspace3.1.2/cssweb/work">
    <Resource type="javax.sql.DataSource"  auth="Container"  name="jdbc/PostgresDS"/> 
    <ResourceParams name="jdbc/PostgresDS">
      <parameter>
       <name>factory</name>
    <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
   </parameter>
    <parameter>
       <name>accessToUnderlyingConnectionAllowed</name>
    <value>true</value>
   </parameter>
      <parameter>
        <name>maxWait</name>
        <value>5000</value>
      </parameter>
      <parameter>
        <name>maxActive</name>
        <value>4</value>
      </parameter>
      <parameter>
        <name>password</name>
        <value>password</value>
      </parameter>
      <parameter>
        <name>url</name>
        <value>jdbc:postgresql://172.17.1.209:5432/cssdb</value>
      </parameter>
      <parameter>
        <name>driverClassName</name>
        <value>org.postgresql.Driver</value>
      </parameter>
      <parameter>
        <name>maxIdle</name>
        <value>2</value>
      </parameter>
      <parameter>
        <name>username</name>
        <value>postgres</value>
      </parameter>
    </ResourceParams>

</Context>