用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;
    }

 

采用以上这种方式可以避免对连接池提供方的依赖。