小贝子编程

JdbcTemplate - 使用 SQL MERGE 插入或更新 Oracle BLOB



使用JdbcTemplate，我想调用MERGE SQL语句，该语句将在表中插入新记录，或者在具有特定键的行已经存在时进行更新。关键部分是其中一列是 Oracle BLOB 类型。

这是我到目前为止尝试过的：

尝试 1.

SQL语句：

String sql = ""
+ "MERGE INTO file_thumbnails "
+ "     USING (SELECT ? as file_c_id, ? as thumbnail_type, ? as thumbnail_image FROM DUAL) tmp "
+ "        ON (file_thumbnails.file_c_id = tmp.file_c_id AND "
+ "            file_thumbnails.thumbnail_type = tmp.thumbnail_type) "
+ "      WHEN MATCHED THEN "
+ "        UPDATE "
+ "           SET thumbnail_image = tmp.thumbnail_image "
+ "              ,thumbnail_date = SYSDATE "
+ "      WHEN NOT MATCHED THEN "
+ "        INSERT (c_id, file_c_id, thumbnail_type, thumbnail_image, thumbnail_date) "
+ "        VALUES (cedar_c_id_seq.nextval, tmp.file_c_id, tmp.thumbnail_type, tmp.thumbnail_image, SYSDATE)";

数据库调用：

List<Object[]> x = fileList.stream().map(file -> {
byte[] thumbnail = file.getThumbnail();
SqlLobValue sqlLobValue = new SqlLobValue(new ByteArrayInputStream(thumbnail), thumbnail.length, new DefaultLobHandler());
return new Object[] { file.getFileCId(), file.getType().toString(), sqlLobValue};
}).collect(Collectors.toList());
jdbcTemplate.batchUpdate(sql, x, new int[] { OracleTypes.NUMBER, OracleTypes.VARCHAR, OracleTypes.BLOB});

例外：

Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [MERGE INTO file_thumbnails      USING (SELECT ? as file_c_id, ? as thumbnail_type, ? as thumbnail_image FROM DUAL) tmp         ON (file_thumbnails.file_c_id = tmp.file_c_id AND             file_thumbnails.thumbnail_type = tmp.thumbnail_type)       WHEN MATCHED THEN         UPDATE            SET thumbnail_image = tmp.thumbnail_image               ,thumbnail_date = SYSDATE       WHEN NOT MATCHED THEN         INSERT (c_id, file_c_id, thumbnail_type, thumbnail_image, thumbnail_date)         VALUES (cedar_c_id_seq.nextval, tmp.file_c_id, tmp.thumbnail_type, tmp.thumbnail_image, SYSDATE)]; SQL state [72000]; error code [1461]; ORA-01461: can bind a LONG value only for insert into a LONG column
; nested exception is java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:662) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:950) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.BatchUpdateUtils.executeBatchUpdate(BatchUpdateUtils.java:32) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1000) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at cern.edms.thumbnails.generator.repository.EdmsFileRepository.saveThumbnails(EdmsFileRepository.java:61) ~[classes/:na]
at cern.edms.thumbnails.generator.repository.EdmsFileRepository$$FastClassBySpringCGLIB$$e3d79386.invoke(<generated>) ~[classes/:na]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at cern.edms.thumbnails.generator.repository.EdmsFileRepository$$EnhancerBySpringCGLIB$$70f43ba5.saveThumbnails(<generated>) ~[classes/:na]
at cern.edms.thumbnails.generator.Application.run(Application.java:58) [classes/:na]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:776) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
... 6 common frames omitted
Caused by: java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10401) ~[ojdbc6-11.2.0.3.0.jar:11.2.0.3.0]
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230) ~[ojdbc6-11.2.0.3.0.jar:11.2.0.3.0]
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:966) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:950) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
... 21 common frames omitted

尝试 2.

Sql statement:
String sql = ""
+ "MERGE INTO file_thumbnails "
+ "     USING (SELECT ? as file_c_id, ? as thumbnail_type FROM DUAL) tmp "
+ "        ON (file_thumbnails.file_c_id = tmp.file_c_id AND "
+ "            file_thumbnails.thumbnail_type = tmp.thumbnail_type) "
+ "      WHEN MATCHED THEN "
+ "        UPDATE "
+ "           SET thumbnail_image = ? "
+ "              ,thumbnail_date = SYSDATE "
+ "      WHEN NOT MATCHED THEN "
+ "        INSERT (c_id, file_c_id, thumbnail_type, thumbnail_image, thumbnail_date) "
+ "        VALUES (cedar_c_id_seq.nextval, tmp.file_c_id, tmp.thumbnail_type, ?, SYSDATE)";

数据库调用：

List<Object[]> x = fileList.stream().map(file -> {
byte[] thumbnail = file.getThumbnail();
SqlLobValue sqlLobValue = new SqlLobValue(new ByteArrayInputStream(thumbnail), thumbnail.length, new DefaultLobHandler());
SqlLobValue sqlLobValue2 = new SqlLobValue(new ByteArrayInputStream(thumbnail), thumbnail.length, new DefaultLobHandler());
return new Object[] { file.getFileCId(), file.getType().toString(), sqlLobValue, sqlLobValue2 };
}).collect(Collectors.toList());
jdbcTemplate.batchUpdate(sql, x, new int[] { OracleTypes.NUMBER, OracleTypes.VARCHAR, OracleTypes.BLOB, OracleTypes.BLOB });

例外：

Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [MERGE INTO file_thumbnails      USING (SELECT ? as file_c_id, ? as thumbnail_type FROM DUAL) tmp         ON (file_thumbnails.file_c_id = tmp.file_c_id AND             file_thumbnails.thumbnail_type = tmp.thumbnail_type)       WHEN MATCHED THEN         UPDATE            SET thumbnail_image = ?               ,thumbnail_date = SYSDATE       WHEN NOT MATCHED THEN         INSERT (c_id, file_c_id, thumbnail_type, thumbnail_image, thumbnail_date)         VALUES (cedar_c_id_seq.nextval, tmp.file_c_id, tmp.thumbnail_type, ?, SYSDATE)]; SQL state [63000]; error code [3106]; ORA-03106: fatal two-task communication protocol error
; nested exception is java.sql.BatchUpdateException: ORA-03106: fatal two-task communication protocol error
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:662) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:950) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.BatchUpdateUtils.executeBatchUpdate(BatchUpdateUtils.java:32) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1000) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at cern.edms.thumbnails.generator.repository.EdmsFileRepository.saveThumbnails(EdmsFileRepository.java:62) ~[classes/:na]
at cern.edms.thumbnails.generator.repository.EdmsFileRepository$$FastClassBySpringCGLIB$$e3d79386.invoke(<generated>) ~[classes/:na]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656) ~[spring-aop-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at cern.edms.thumbnails.generator.repository.EdmsFileRepository$$EnhancerBySpringCGLIB$$587b6598.saveThumbnails(<generated>) ~[classes/:na]
at cern.edms.thumbnails.generator.Application.run(Application.java:58) [classes/:na]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:776) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
... 6 common frames omitted
Caused by: java.sql.BatchUpdateException: ORA-03106: fatal two-task communication protocol error
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10401) ~[ojdbc6-11.2.0.3.0.jar:11.2.0.3.0]
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230) ~[ojdbc6-11.2.0.3.0.jar:11.2.0.3.0]
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:966) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:950) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-4.3.7.RELEASE.jar:4.3.7.RELEASE]
... 21 common frames omitted

其他说明。

  1. 在数据库调用的第二次尝试中，我不能使用两次相同的SqlLobValue对象，因为我遇到异常：

    原因：java.sql.SQLException：重复流参数：4

  2. 如果我进行第二次尝试，但只放置一次 BLOB 输入参数(例如仅在 MERGE 语句的 INSERT 部分中)，它可以正常工作。但当然，这并不能解决我的问题。

你能帮忙吗？

由于@gvenzi答案，我解决了这个问题，但决定发布我自己的答案，因为我有一些额外的评论。

所以，是的，OracleLobHandler解决了问题。但实际上我们并没有被迫使用已弃用的类。在 OracleLobHandler 文档中，我找到了

荒废的。 支持Oracle 10g驱动程序的DefaultLobHandler 和更高。考虑使用 10g/11g 驱动程序，即使针对 Oracle 也是如此 9i数据库！DefaultLobHandler.setCreateTemporaryLob(boolean) 是 直接等同于这个 OracleLobHandler 的实现策略， 仅使用标准的 JDBC 4.0 API。也就是说，在大多数情况下，常规 DefaultLobHandler 安装程序也可以正常工作。

我测试了它，它可以工作。

但是我在PreparedStatementSetter中将SqlLobValue与OracleTypes.BLOB一起使用时遇到了另一个问题(此处描述了ClassCastException：SqlLobValue不能使用PreparedStatementSetter强制转换为oracle.sql.BLOB)

我的最终工作代码如下：

public void saveThumbnails(List<Thumbnail> fileList) throws SQLException, IOException {
BatchPreparedStatementSetter b = new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Thumbnail thumbnail = fileList.get(i);
byte[] thumbnailBytes = thumbnail.getThumbnail();
ps.setObject(1, thumbnail.getFileCId(), OracleTypes.NUMBER);
ps.setObject(2, thumbnail.getType().toString(), OracleTypes.VARCHAR);
DefaultLobHandler lobHandler = new DefaultLobHandler();
lobHandler.setCreateTemporaryLob(true);
lobHandler.getLobCreator().setBlobAsBytes(ps, 3, thumbnailBytes);
}
@Override
public int getBatchSize() {
return fileList.size();
}
};
jdbcTemplate.batchUpdate(getSaveThumbnailSql(), b);
}
private String getSaveThumbnailSql() {
// @formatter:off
String sql = ""
+ "MERGE INTO file_thumbnails "
+ "     USING (SELECT ? as file_c_id, ? as thumbnail_type, ? AS thumbnail_image FROM DUAL) tmp "
+ "        ON (file_thumbnails.file_c_id = tmp.file_c_id AND "
+ "            file_thumbnails.thumbnail_type = tmp.thumbnail_type) "
+ "      WHEN MATCHED THEN "
+ "        UPDATE "
+ "           SET thumbnail_image = tmp.thumbnail_image"
+ "              ,thumbnail_date = SYSDATE "
+ "      WHEN NOT MATCHED THEN "
+ "        INSERT (c_id, file_c_id, thumbnail_type, thumbnail_image, thumbnail_date) "
+ "        VALUES (cedar_c_id_seq.nextval, tmp.file_c_id, tmp.thumbnail_type, tmp.thumbnail_image , SYSDATE)";
//@formatter:on
return sql;
}

我不是真正的 Spring 框架专家，但我可以重现并在某种程度上调试您的问题。它与您传递一个似乎被绑定为LONG数据类型而不是错误BLOB的DefaultLobHandler有关。

下面是上述调用的简化测试用例，批量大小为 1：

String sql = "MERGE INTO file_thumbnails "
+ "     USING (SELECT ? as file_c_id, ? as thumbnail_type, ? as thumbnail_image FROM DUAL) tmp "
+ "        ON (file_thumbnails.file_c_id = tmp.file_c_id AND "
+ "            file_thumbnails.thumbnail_type = tmp.thumbnail_type) "
+ "      WHEN MATCHED THEN "
+ "        UPDATE "
+ "           SET thumbnail_image = tmp.thumbnail_image "
+ "              ,thumbnail_date = SYSDATE "
+ "      WHEN NOT MATCHED THEN "
+ "        INSERT (file_c_id, thumbnail_type, thumbnail_image, thumbnail_date) "
+ "        VALUES (tmp.file_c_id, tmp.thumbnail_type, tmp.thumbnail_image, SYSDATE)";
byte[] content = Files.readAllBytes(Paths.get("/Users/gvenzl/Downloads/image1.JPG"));
ByteArrayInputStream bin = new ByteArrayInputStream(content);
SqlLobValue sqlLobValue = new SqlLobValue(bin, content.length, new DefaultLobHandler());
List<Object []> x =  new ArrayList<Object []>();
x.add(new Object [] { 1, "Test", sqlLobValue});
jdbcTemplate.batchUpdate(sql, x, new int[] { OracleTypes.NUMBER, OracleTypes.VARCHAR, OracleTypes.BLOB});
System.out.print("Successful!");

我正在读取图像，然后创建一个项目数组并以与您相同的方式执行它并出错：

Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [MERGE INTO file_thumbnails      USING (SELECT ? as file_c_id, ? as thumbnail_type, ? as thumbnail_image FROM DUAL) tmp         ON (file_thumbnails.file_c_id = tmp.file_c_id AND             file_thumbnails.thumbnail_type = tmp.thumbnail_type)       WHEN MATCHED THEN         UPDATE            SET thumbnail_image = tmp.thumbnail_image               ,thumbnail_date = SYSDATE       WHEN NOT MATCHED THEN         INSERT (file_c_id, thumbnail_type, thumbnail_image, thumbnail_date)         VALUES (tmp.file_c_id, tmp.thumbnail_type, tmp.thumbnail_image, SYSDATE)]; SQL state [72000]; error code [1461]; ORA-01461: can bind a LONG value only for insert into a LONG column
; nested exception is java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column

现在我将 LOB 处理程序从DefaultLobHandler更改为已弃用的OracleLobHandler：

byte[] content = Files.readAllBytes(Paths.get("/Users/gvenzl/Downloads/image1.JPG"));
ByteArrayInputStream bin = new ByteArrayInputStream(content);
SqlLobValue sqlLobValue = new SqlLobValue(bin, content.length, new OracleLobHandler());
List<Object []> x =  new ArrayList<Object []>();
x.add(new Object [] { 1, "Test", sqlLobValue});
jdbcTemplate.batchUpdate(sql, x, new int[] { OracleTypes.NUMBER, OracleTypes.VARCHAR, OracleTypes.BLOB});
System.out.print("Successful!");

我的出局是：

Successful!

通过它进行调试，我可以看到的区别是OracleLobHandler使用ps.setBlob()方法，而DefaultLobHandler使用ps.setBinaryStream()这似乎导致变量被绑定为LONG而不是BLOB。希望这有帮助！

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium