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

calendar Dec 21, 2021   ---   · 3 min read · JDBC Oracle  ·
Share on: linkedin copy
向数据库中插入 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 类型字段的表
1CREATE TABLE item(
2    id INTEGER,
3    description CLOB
4)

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

调用 setStringForClob()

 1Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "oracle");
 2PreparedStatement pstmt = conn.prepareStatement("insert into item(id, description) values(?, ?)");
 3
 4CLOB clob = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);
 5String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));
 6clob.setString(1, longStr);
 7
 8pstmt.setInt(1, 1);
 9((OraclePreparedStatement)pstmt).setStringForClob(2, longStr);
10
11int update = pstmt.executeUpdate();
12
13System.out.println(update);
14System.out.println("string length " + longStr.length());

执行后输出为
1
string length 69998
成功插入一个大 BLOB 类型数据到数据库

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

用 CLOB.createTemporary() 方法

1PreparedStatement pstmt = conn.prepareStatement("insert into item(id, description) values(?, ?)");
2
3CLOB clob = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);
4String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));
5clob.setString(1, longStr);
6
7pstmt.setClob(2, clob);

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

其他的一些尝试

1pstmt.setClob(2, new OracleSerialClob("hello".toCharArray()));

会报错
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
1((OraclePreparedStatement)pstmt).setCLOB(2, new CLOB((OracleConnection) conn, "hello".getBytes()));

出错
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 5
    at oracle.sql.CLOB.<init>(CLOB.java:133)
没有 ojdbc 的源代码，没细究下去

用 SQL 语句，并且字符串长度小于 4K  的话可以用
1INSERT INTO item(id, description) values(1, 'abc');   -- 或者
2INSERT INTO item(id, description) values(2, to_clob('abc'));

如果要插入到 CLOB 的字符串超过 4K，用 to_clob() 函数就会报错
SQL Error [1704] [42000]: ORA-01704: string literal too long
对于如果字符串不超过 4K，JDBC 中可直接用 setString(2, "string value") 赋值
1PreparedStatement pstmt = conn.prepareStatement("insert into item(id, description) values(?, ?)");
2pstmt.setString(2, "string whose length is not larger then 4000");

常见的两步操作法

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

 1Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "oracle");
 2
 3conn.setAutoCommit(false);
 4
 5Statement stmt = conn.createStatement();
 6stmt.executeUpdate("insert into item(id, description) values(1, EMPTY_CLOB())");
 7
 8ResultSet rs = stmt.executeQuery("select description from item where id=1 for update");
 9rs.next();
10oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
11OutputStream out = clob.getAsciiOutputStream();
12String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));
13out.write(longStr.getBytes());
14out.close();
15
16conn.commit();

上面的操作会把 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, 完全一样的传参数的方式，比如存储过程为
1CREATE OR REPLACE PROCEDURE sp_insert_item (
2  v_id IN Integer,
3  v_desc IN CLOB 
4)
5AS
6BEGIN
7    INSERT INTO item(id, description) values(v_id, v_desc);
8END;

调用该存储过程的 Java 代如为
1Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "oracle");
2
3CallableStatement call = conn.prepareCall("{call insert_item(?, ?)}");
4call.setInt(1, 1);
5
6String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));
7((OraclePreparedStatement)call).setStringForClob(2, longStr);
8
9call.executeUpdate();

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

链接：

  1. Java: How to insert CLOB into oracle database
  2. Let's Review How to Insert Clob or Blob via JDBC
永久链接 https://yanbin.blog/java-insert-clob-blob-into-oracle-table/, 来自 隔叶黄莺 Yanbin's Blog
[版权声明] 本文采用 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 进行许可。