Java 直接插入 CLOB/BLOB 数据到 Oracle 数据库

向数据库中插入 CLOB 或 BLOB 类型的数据,Oracle 总是比其他类型的数据库操作上要麻烦多了。当然,对于不大于 4K 长度的 CLOB 字符串在 JDBC 中可简单的用 PreparedStatement.setString(idx, "short string") 。如果要插入大于 4K 长度的内容,网上找来的例子许多都是分两步走

  1. 先插入 EMPTY_CLOB() 或 EMPTY_BLOB()
  2. 然后 SELECT 原来的记录 FOR UPDATE, 再更新先前插入的记录

这就存在两个问题,含 CLOB/BLOB 的表必需要有主键,还有因为 FOR UPDATE 的使用我们需要开启事物,不能采用自动提交。

其实还有更简单的方法可直接插入大的 CLOB/BLOB 数据,要用到 Oracle JDBC 驱动的 setStringForClob(),  CLOB.createTemporary(), 或 BLOB.createTemporary() 方法。来看下面的例子,例子中只演示 CLOB, 类似的方法可应用于 BLOB, NCLOB。

本文中所使用的 Oracle JDBC 驱动比较老,是 ojdbc:ojdbc:5。Docker 启动一个本地的 Oracle 11G 作为测试数据库

$ docker run -d -p 1521:1521 -p 8080:8080 wnameless/oracle-xe-11g-r2

默认的 SID 是 xe, 数据库用户名和密码分别是 system/oracle

首先来创建一个含有 CLOB 类型字段的表

最简单的一步插入 CLOB 类型数据的代码如下

调用 setStringForClob()

执行后输出为

1
string length 69998

成功插入一个大 BLOB 类型数据到数据库

或者另一种方法稍稍多两行代码

用 CLOB.createTemporary() 方法

与前一种方法相比较,自然是推荐使用 setStringForClob() 方法

其他的一些尝试

会报错

Exception in thread "main" java.lang.ClassCastException: oracle.jdbc.rowset.OracleSerialClob cannot be cast to oracle.sql.CLOB
    at oracle.jdbc.driver.OraclePreparedStatement.setClob(OraclePreparedStatement.java:6544)

因为 Oracle 驱动的 oracle.jdbc.driver.OraclePreparedStatement.setClob() 会把传入的参数转换为 oracle.sql.CLOB

出错

Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 5
    at oracle.sql.CLOB.<init>(CLOB.java:133)

没有 ojdbc 的源代码,没细究下去

用 SQL 语句,并且字符串长度小于 4K  的话可以用

如果要插入到 CLOB 的字符串超过 4K,用 to_clob() 函数就会报错

SQL Error [1704] [42000]: ORA-01704: string literal too long

对于如果字符串不超过 4K,JDBC 中可直接用 setString(2, "string value") 赋值

常见的两步操作法

附带一下网上搜索到的很多使用的两步插入大 CLOB 数据的方法

上面的操作会把 description 字段值覆盖为 longStr 的内容,无论它是否为 EMPTY。但是在 for update 之前  description 字段不能为 null, 否则 clob.getAsciiOutputStream()  会抛出 NullPointerException 异常,所以为什么先要放一个 EMPTY_CLOB()。

这种方式必须启用事物,autoComit 要设置为 false,并手动 commit。 如果去掉上面的高亮的两行代码,则只会插入一条 description 为空, id 为 1 的记录,后面对 clob 的操作无效果。

如果把 for update 去掉,第二个查询只写成

select description from item where id=1

则报类似下面的错误

Exception in thread "main" java.io.IOException: ORA-22920: row containing the LOB value is not locked
    at oracle.jdbc.driver.OracleClobOutputStream.write(OracleClobOutputStream.java:137)
    at java.io.OutputStream.write(OutputStream.java:75)
    at com.ibbotson.services.cas.dataaccess.ConfigDataAccessImpl.main(ConfigDataAccessImpl.java:717)
Caused by: java.sql.SQLException: ORA-22920: row containing the LOB value is not locked

再就是 CLOB.getAsciiOutputStream() 早已不推荐使用,因此从这方面来讲更不应试使用两步走的方式来插入 CLOB 类型的数据了。

存储过程/函数参数是 CLOB

如果存储过程或函数的参数是 CLOB, 完全一样的传参数的方式,比如存储过程为

调用该存储过程的 Java 代如为

或者愿意的话也能用 CLOB.createTemporary() 的方式来赋值。

链接:

  1. Java: How to insert CLOB into oracle database
  2. Let's Review How to Insert Clob or Blob via JDBC

类别: Database, Java/JEE. 标签: , . 阅读(138). 订阅评论. TrackBack.
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x