Используя 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
Дополнительные замечания.
Во второй попытке в вызове базы данных я не могу дважды использовать один и тот же объект
SqlLobValue
, потому что получаю исключение:Вызвано: java.sql.SQLException: повторяющийся параметр потока: 4
Если я пойду со второй попытки, но поставлю входной параметр BLOB только один раз (например, только в части INSERT оператора MERGE), он будет работать правильно. Но мою проблему это, конечно, не решает.
Не могли бы вы помочь?