oracle中clob或blob处理

 

oracle中clob或blob处理

create table DOCUMENT (     ID             NUMBER(16) not null,     content clob null ) 注意：直接相关的两个包为： import oracle.sql.CLOB; import java.sql.PreparedStatement; 数据Insert处理： try {         conn = this.getConnection();         conn.setAutoCommit(false);         String id = this.getId("SEQ_document", conn);         stmt = conn.createStatement();         // 先将该记录插入到数据库中         stmt.executeUpdate("insert into document(id,content) values("+id+",cmpty_clob)");         // 然后更新该记录的content字段(该字段的类型为CLOB)         updateContent(conn, id, contentValue); // contentValue为要插入到content字段的值         conn.commit(); //事务结束         conn.setAutoCommit(true);       }       catch (SQLException e) {         try {           conn.rollback();           conn.setAutoCommit(true);         }         catch (SQLException ex) {           ex.getMessage();         }         finally {           throw new DAOException("Failed"+e.getMessage());         }       }       finally {         closeStatement(stmt);         closeConnection(conn);       }     private void updateContent(Connection conn, String infoId,                                DocumentModel model) {       Statement stmt = null;       ResultSet rs = null;       try {         stmt = conn.createStatement();         String sql =             "select content from document where id=" + infoId + " for update";         rs = stmt.executeQuery(sql);         if (rs.next()) {           oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(1);           clob.putString(1, model.getContent());           sql = "update document set content=? where id='" + infoId + "'";           PreparedStatement pstmt = conn.prepareStatement(sql);           pstmt.setClob(1, (java.sql.Clob) clob);           pstmt.executeUpdate();           pstmt.close();         }         this.closeResultSet(rs);         this.closeStatement(stmt);       }       catch (SQLException e) {         throw new DAOException("insert or update content failed!" + e.getMessage());       }     } 数据的修改处理： 针对数据的修改处理，如果修改这个表的其他字段，按正常的方式处理即可，如果要更新clob字段，则如下进行：假设要修改的记录的ID号为infoId：     private void updateContent(Connection conn, String infoId,                                DocumentModel model) {       Statement stmt = null;       ResultSet rs = null;       try {         stmt = conn.createStatement();       stmt.executeUpdate("update document set content=empty_clob() where id='" + infoId + "'"); // 见下面的说明         String sql =             "select content from document where id=" + infoId + " for update";         rs = stmt.executeQuery(sql);         if (rs.next()) {           oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(1);           clob.putString(1, model.getContent());           sql = "update document set content=? where id='" + infoId + "'";           PreparedStatement pstmt = conn.prepareStatement(sql);           pstmt.setClob(1, (java.sql.Clob) clob);           pstmt.executeUpdate();           pstmt.close();         }         this.closeResultSet(rs);         this.closeStatement(stmt);       }       catch (SQLException e) {         throw new DAOException("insert or update content failed!" + e.getMessage());       }     } 说明：在修改之前一定要执行一次这句，否则会出现clob更新后的内容和自己实际的预期不相同的，即：假如更新之前clob字段的内容为 aaaaaa bbbbbb     ，用 11111 来更新之后，这个字段的值基本上会是类似于 11111 a bbbbbb  等，当然这时使用Oracle.sql.Clob包会出现的情形，如果使用weblogic.jdbc.vendor.oracle.OracleThinClob这个包来进行处理，是否有此现象没有试验过！这并非是什么BUG，只是Oracle.sql.CLOB的处理方式导致的。