用JDBC操纵BLOB和CLOB数据
来源:互联网 发布:微信淘宝头像图片大全 编辑:程序博客网 时间:2024/04/30 19:50
在访问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;
}
- 用JDBC操纵BLOB和CLOB数据
- JDBC操作大数据CLOB和BLOB
- jdbc+hibernate操纵blob数据
- jdbc大数据 CLOB BLOB
- 利用JDBC操作Oracle CLOB和BLOB类型数据
- jdbc学习:操作Oracle的CLOB和BLOB数据
- jdbc操作clob和blob字段
- jdbc操作clob和blob字段
- Java操纵oracle的CLOB和BLOB字段
- jdbc操作blob,clob
- JDBC CLOB\BLOB操作
- jdbc操作blob,clob
- JDBC:LOB---BLOB、CLOB
- jdbc 使用PreparedStatement来存储和读取大数据(Blob或Clob)
- plsql 查询blob数据和clob数据类型
- 通过JDBC和Hibernate对Clob和Blob的操作
- 通过JDBC和Hibernate对Clob和Blob的操作
- [转]Blob和Clob在JDBC中的简介
- C#的四种排序算法
- Command模式应用实践
- 利用AJAX与数据岛实现无刷新绑定
- 经典C源程序100例
- PetShop之ASP.NET缓存
- 用JDBC操纵BLOB和CLOB数据
- PetShop之业务逻辑层设计
- 2006年,北京的最后一场雪
- 改善脑力的70条方法[翻译]
- 对移动用户的提示
- struts+hibernate+spring入门例子
- PetShop之表示层设计
- 程序员修身养性的十大原则
- 设计一个自动编码解码库