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

Question

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

springoracleblobjdbctemplatesql-merge

3

使用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

尝试二。

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

附加说明。

在第二次数据库调用中，我不能使用两个相同的SqlLobValue对象，因为我会得到一个异常：

Caused by: java.sql.SQLException: Duplicate stream parameter: 4
如果我采用第二种方法，但只将BLOB输入参数放入一次（例如仅放入MERGE语句的INSERT部分），它可以正常运行。但这当然无法解决我的问题。

请帮忙看看怎么解决吗？

- kpater87

2个回答

1

我不是Spring框架的专家，但我能够重现并部分地调试您的问题。它与您传递的DefaultLobHandler有关，它似乎会被错误地绑定为LONG数据类型而不是BLOB。

以下是使用批量大小为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。希望这可以帮到你！

- gvenzl

谢谢！它解决了问题，但请也查看我的答案并附加一些评论。 - kpater87

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- kpater87 · Accepted Answer

我通过@gvenzi的答案解决了问题，但我决定发表自己的答案，因为我有一些额外的评论。

是的，OracleLobHandler 解决了这个问题。但实际上我们并不强制使用废弃的类。在OracleLobHandler文档中我找到了以下内容：

已过时。建议使用Oracle 10g驱动程序及更高版本的DefaultLobHandler替代。即使对于Oracle 9i数据库，也应考虑使用10g / 11g驱动程序！ DefaultLobHandler.setCreateTemporaryLob（boolean）是此OracleLobHandler实现策略的直接等效物，只需使用标准JDBC 4.0 API即可。尽管如此，在大多数情况下，常规DefaultLobHandler设置也可以正常工作。

我测试并且它有效。

但是我在PreparedStatementSetter中使用SqlLobValue和OracleTypes.BLOB时遇到了另一个问题（在这里描述了ClassCastException：SqlLobValue不能被转换为oracle.sql.BLOB使用PreparedStatementSetter）。

我的最终可行代码如下：

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;
}