用JDBC操纵BLOB和CLOB数据
在访问Oracle 数据库，对Oracle 的BLOB 和CLOB 进行操作的时候，当通过Oracle JDBC Driver 来调用的时，如下所例：
Driver myDriver = (Driver)Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();conn = myDriver.connect("jdbc:oracle:thin:" , props);Statement stmt = conn.createStatement();stmt.execute("select blob_content from lobtest where id=1");ResultSet rs = stmt.getResultSet();System.out.println("ResultSet result:"+rs);while ( rs.next() ) ...{System.out.println("BLOB:"+rs.getBlob("blob_content"));myBlob = (oracle.sql.BLOB)rs.getBlob("blob_content");}?
返回的数据对象正确，是oracle.sql.BLOB 对象，然后可以使用该对象进行方法访问。
但当使用Oracle JDBC Driver 配置连接池后，使用DataSource 取得数据库连接之后，查询CLOB 数据，赋值给oracle.sql.CLOB 的对象，这是就会报类型转换错误。
在网络上有相应的解决方案，但都是依赖于某一种应用服务器所提供的oracle连接环境。
下面是一个比较好的解决方案：
1。必须有支持jdbc3.0以上规范的oracle驱动。
2。更新blob字段数据
public void updateBlob(String tableName, String blobFeild, String pryKey, String pryKeyValue, byte[] blob) throws IOException, SQLException ...{ Statement stmt = null; ResultSet rs = null; try ...{ stmt = conn.createStatement(); String sql = "UPDATE " + tableName + " SET " + blobFeild + "=EMPTY_BLOB() WHERE " + pryKey + "='" + pryKeyValue + "'"; // 如果参数blob为null，清空blob值；否则先清空blob值，然后插入新的blob值。 if (blob == null) ...{ stmt.executeUpdate(sql); } else ...{ stmt.executeUpdate(sql); sql = "SELECT " + blobFeild + " FROM " + tableName + " WHERE " + pryKey + "='" + pryKeyValue + "' FOR UPDATE"; rs = stmt.executeQuery(sql); if (rs.next()) ...{ Blob inBlob = rs.getBlob(blobFeild); int len = inBlob.setBytes(1, blob); log.debug("已成功保存BLOB大对象：" + len / 1024 + "KB"); } } } finally ...{ DbUtils.close(rs); DbUtils.close(stmt); } }?
3。获取blob字段数据
???
public byte[] findBlob(String tableName, String blobFeild, String pryKey, String pryKeyValue) throws SQLException ...{ byte[] blob = null; Statement stmt = null; String sql = "SELECT " + blobFeild + " FROM " + tableName + " WHERE " + pryKey + "='" + pryKeyValue + "'"; ResultSet rs = null; try ...{ stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) ...{ Blob outBlob = rs.getBlob(blobFeild); if (outBlob != null && outBlob.length() > 0) ...{ blob = outBlob.getBytes(1, (int) outBlob.length()); } } } finally ...{ DbUtils.close(rs); DbUtils.close(stmt); } return blob; }?
4。更新clob字段数据
???
public void updateClob(String tableName, String clobFeild, String pryKey, String pryKeyValue, String clob) throws IOException, SQLException ...{ Statement stmt = null; ResultSet rs = null; try ...{ stmt = conn.createStatement(); String sql = "UPDATE " + tableName + " SET " + clobFeild + "=EMPTY_CLOB() WHERE " + pryKey + "='" + pryKeyValue + "'"; // 如果参数clob为null，清空clob值；否则先清空clob值，然后插入新的clob值。 if (clob == null) ...{ stmt.executeUpdate(sql); } else ...{ stmt.executeUpdate(sql); sql = "SELECT " + clobFeild + " FROM " + tableName + " WHERE " + pryKey + "='" + pryKeyValue + "' FOR UPDATE"; rs = stmt.executeQuery(sql); if (rs.next()) ...{ Clob inClob = rs.getClob(clobFeild); int len = inClob.setString(1, clob); log.debug("已成功保存CLOB大对象：" + len / 1024 + "KB"); } } } finally ...{ DbUtils.close(rs); DbUtils.close(stmt); } }?
5。获取clob字段数据
???
public String findClob(String tableName, String clobFeild, String pryKey, String pryKeyValue) throws SQLException ...{ String clob = ""; Statement stmt = null; String sql = "SELECT " + clobFeild + " FROM " + tableName + " WHERE " + pryKey + "='" + pryKeyValue + "'"; ResultSet rs = null; try ...{ stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) ...{ Clob outClob = rs.getClob(clobFeild); if (outClob != null && outClob.length() > 0) ...{ clob = outClob.getSubString(1, pryKeyValue.length()); } } } finally ...{ DbUtils.close(rs); DbUtils.close(stmt); } return clob; }?
采用以上这种方式可以避免对连接池提供方的依赖。
?