Java 直接插入 CLOB/BLOB 数据到 Oracle 数据库 | 隔叶黄莺 Yanbin Blog

2021-12-21 | 阅读(1,278)

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

先插入 EMPTY_CLOB() 或 EMPTY_BLOB()
然后 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 类型字段的表

CREATE TABLE item(
    id INTEGER,
    description CLOB
)

CREATE TABLE item(

id INTEGER,

description CLOB

)

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

调用 setStringForClob()

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "oracle");
PreparedStatement pstmt = conn.prepareStatement("insert into item(id, description) values(?, ?)");

CLOB clob = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);
String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));
clob.setString(1, longStr);

pstmt.setInt(1, 1);
((OraclePreparedStatement)pstmt).setStringForClob(2, longStr);

int update = pstmt.executeUpdate();

System.out.println(update);
System.out.println("string length " + longStr.length());

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "oracle");

PreparedStatement pstmt = conn.prepareStatement("insert into item(id, description) values(?, ?)");

CLOB clob = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);

String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));

clob.setString(1, longStr);

pstmt.setInt(1, 1);

((OraclePreparedStatement)pstmt).setStringForClob(2, longStr);

int update = pstmt.executeUpdate();

System.out.println(update);

System.out.println("string length " + longStr.length());

执行后输出为

1
string length 69998

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

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

用 CLOB.createTemporary() 方法

PreparedStatement pstmt = conn.prepareStatement("insert into item(id, description) values(?, ?)");

CLOB clob = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);
String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));
clob.setString(1, longStr);

pstmt.setClob(2, clob);

PreparedStatement pstmt = conn.prepareStatement("insert into item(id, description) values(?, ?)");

CLOB clob = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);

String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));

clob.setString(1, longStr);

pstmt.setClob(2, clob);

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

其他的一些尝试

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

1	pstmt.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

((OraclePreparedStatement)pstmt).setCLOB(2, new CLOB((OracleConnection) conn, "hello".getBytes()));

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 的话可以用

INSERT INTO item(id, description) values(1, 'abc');   -- 或者
INSERT INTO item(id, description) values(2, to_clob('abc'));

1 2	INSERT INTO item(id, description) values(1, 'abc'); -- 或者 INSERT 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") 赋值

PreparedStatement pstmt = conn.prepareStatement("insert into item(id, description) values(?, ?)");
pstmt.setString(2, "string whose length is not larger then 4000");

1 2	PreparedStatement pstmt = conn.prepareStatement("insert into item(id, description) values(?, ?)"); pstmt.setString(2, "string whose length is not larger then 4000");

常见的两步操作法

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

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "oracle");

conn.setAutoCommit(false);

Statement stmt = conn.createStatement();
stmt.executeUpdate("insert into item(id, description) values(1, EMPTY_CLOB())");

ResultSet rs = stmt.executeQuery("select description from item where id=1 for update");
rs.next();
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
OutputStream out = clob.getAsciiOutputStream();
String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));
out.write(longStr.getBytes());
out.close();

conn.commit();

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "oracle");

conn.setAutoCommit(false);

Statement stmt = conn.createStatement();

stmt.executeUpdate("insert into item(id, description) values(1, EMPTY_CLOB())");

ResultSet rs = stmt.executeQuery("select description from item where id=1 for update");

rs.next();

oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);

OutputStream out = clob.getAsciiOutputStream();

String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));

out.write(longStr.getBytes());

out.close();

conn.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, 完全一样的传参数的方式，比如存储过程为

CREATE OR REPLACE PROCEDURE sp_insert_item (
  v_id IN Integer,
  v_desc IN CLOB 
)
AS
BEGIN
    INSERT INTO item(id, description) values(v_id, v_desc);
END;

CREATE OR REPLACE PROCEDURE sp_insert_item (

v_id IN Integer,

v_desc IN CLOB

)

BEGIN

INSERT INTO item(id, description) values(v_id, v_desc);

END;

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

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "oracle");

CallableStatement call = conn.prepareCall("{call insert_item(?, ?)}");
call.setInt(1, 1);

String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));
((OraclePreparedStatement)call).setStringForClob(2, longStr);

call.executeUpdate();

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "oracle");

CallableStatement call = conn.prepareCall("{call insert_item(?, ?)}");

call.setInt(1, 1);

String longStr = String.join(", ", Collections.nCopies(5000, "Hello World!"));

((OraclePreparedStatement)call).setStringForClob(2, longStr);

call.executeUpdate();

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

链接：

本文链接 https://yanbin.blog/java-insert-clob-blob-into-oracle-table/, 来自隔叶黄莺 Yanbin Blog

seetimee on 体验 Python FastAPI 的并发能力及线, 进程模型感谢
Yanbin on Mockito 3.4.0 开始可 Mock 静态方法有一个补救，新写了一篇 https://yanbin.blog/mockito-mock-static-method-in-multiple...
Yanbin on 升级到 Spring Boot 3 后 javax.inject.Named 不可用怎么，被抄袭了！算是被机器翻译引用的？
oleksi on 升级到 Spring Boot 3 后 javax.inject.Named 不可用https://www.springcloud.io/post/2023-02/springboot-3-javax-inject-name...
Zhe on 体验 Python FastAPI 的并发能力及线, 进程模型你在trio下得用 trio.sleep()，调用asyncio.sleep() 就会报错。