在访问Oracle 数据库,对Oracle 的BLOB 和CLOB 进行操作的时候,当通过Oracle JDBC Driver 来调用的时,如下所例:
3。获取blob字段数据
4。更新clob字段数据
5。获取clob字段数据
采用以上这种方式可以避免对连接池提供方的依赖。
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");
}
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);
}
}
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;
}
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;
}
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);
}
}
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);
}
}
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;
}
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;
}