oracle blob数据存取

 

//一个POJO例子
class User {
String name;
String age;
public User(String name,String age){
  this.name=name;
  this.age=age;
}
public String getAge() {
  return age;
}
public void setAge(String age) {
  this.age = age;
}
public String getName() {
  return name;
}
public void setName(String name) {
  this.name = name;
} 
}

//具体的比较类，实现Comparator接口
import java.util.Comparator;
import java.util.List;
import java.util.ArrayList;

public static void blobInsert(int systemId, int projectId, int lineId, byte[] bytes) throws Exception {
        Connection conn = DBConnection.getConnection();
        Statement stmt = null;
        ResultSet rs = null;

        /* 设定不自动提交 */
        conn.setAutoCommit(false);

        try {

            /* 插入一个空的BLOB对象 */
            stmt = conn.createStatement();
            stmt.executeUpdate("INSERT INTO tb_gcxlght VALUES (" + systemId + "," + projectId + "," + lineId + "," +
                               System.currentTimeMillis() / 1000 +
                               ", EMPTY_BLOB())");

            /* 查询此BLOB对象并锁定 */

            rs = stmt.executeQuery("SELECT pictures FROM tb_gcxlght WHERE system_id=" + systemId + " and project_id=" + projectId + " and line_id=" +
                                   lineId + " FOR UPDATE");

            while (rs.next()) {

                /* 取出此BLOB对象 */

                oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("pictures");

                /* 向BLOB对象中写入数据 */

                BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());

                for (int i = 0; i < bytes.length; i++) {
                    out.write(bytes[i]);
                }

                out.close();

            }

            /* 正式提交 */

            conn.commit();

        } catch (Exception ex) {

            /* 出错回滚 */

            conn.rollback();

            throw ex;

        }
        /* 恢复原提交状态 */
//        conn.setAutoCommit(defaultCommit);
    }

    public static BufferedImage blobRead(int systemId, int projectId, int lineId) throws Exception {
        Connection conn = DBConnection.getConnection();
        Statement stmt = null;
        ResultSet rs = null;
        BufferedImage image = null;
        /* 设定不自动提交 */
//        boolean defaultCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);

        try {

            /* 查询BLOB对象 */
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT pictures FROM tb_gcxlght WHERE system_id=" + systemId + " and project_id=" + projectId + " and line_id=" +
                                   lineId);
            if (rs.next()) {
                /* 取出此BLOB对象 */
                oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("pictures");
                /* 以二进制形式输出 */
                image = ImageIO.read(blob.getBinaryStream());
            }
            /* 正式提交 */
            conn.commit();

        } catch (Exception ex) {

            /* 出错回滚 */

            conn.rollback();

            throw ex;

        }
        return image;

    }

    public static void blobModify(int systemId, int projectId, int lineId, byte[] bytes) throws Exception

    {
        Connection conn = DBConnection.getConnection();
        Statement stmt = null;
        ResultSet rs = null;

        /* 设定不自动提交 */
        conn.setAutoCommit(false);

        try {

            /* 插入一个空的BLOB对象 */
            stmt = conn.createStatement();
            /* 查询此BLOB对象并锁定 */

            rs = stmt.executeQuery("SELECT pictures FROM tb_gcxlght WHERE system_id=" + systemId + " and project_id=" + projectId + " and line_id=" +
                                   lineId + " FOR UPDATE");

            while (rs.next()) {

                /* 取出此BLOB对象 */

                oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("pictures");

                /* 向BLOB对象中写入数据 */

                BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());

                for (int i = 0; i < bytes.length; i++) {
                    out.write(bytes[i]);
                }

                out.close();

            }

            /* 正式提交 */

            conn.commit();

        } catch (Exception ex) {

            /* 出错回滚 */

            conn.rollback();

            throw ex;

        }

    }