[SPRING] JdbcTemplate - SQL MERGE를 사용하여 Oracle BLOB 삽입 또는 업데이트
SPRINGJdbcTemplate - 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.@ gvenzi 대답 덕분에 문제가 해결되었지만 몇 가지 추가 의견이 있기 때문에 제 답변을 게시하기로 결정했습니다.
@ gvenzi 대답 덕분에 문제가 해결되었지만 몇 가지 추가 의견이 있기 때문에 제 답변을 게시하기로 결정했습니다.
따라서 OracleLobHandler는 문제를 해결합니다. 그러나 사실 우리는 더 이상 사용되지 않는 클래스를 사용하지 않아도됩니다. 내가 찾은 OracleLobHandler 문서에서
나는 그것을 테스트하고 작동합니다.
하지만 PreparedStatementSetter에서 OracleTypes.BLOB과 함께 SqlLobValue를 사용하는 또 다른 문제가있었습니다 (여기에 설명되어 있습니다 ClassCastException : PreparedStatementSetter를 사용하여 oracle.sql.BLOB로 SqlLobValue를 캐스팅 할 수 없음)
내 마지막 작업 코드는 다음과 같습니다.
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; }
-
==============================
2.저는 Spring 프레임 워크 전문가는 아니지만 문제를 재현하고 다소 디버깅 할 수 있습니다. 그것은 오류에 의해 BLOB보다는 LONG 데이터 유형으로 바인드되는 것으로 보이는 DefaultLobHandler와 관련이 있습니다.
저는 Spring 프레임 워크 전문가는 아니지만 문제를 재현하고 다소 디버깅 할 수 있습니다. 그것은 오류에 의해 BLOB보다는 LONG 데이터 유형으로 바인드되는 것으로 보이는 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 ()을 사용하여 변수가 BLOB이 아닌 LONG으로 바인딩되도록하는 것입니다. 희망이 도움이!
from https://stackoverflow.com/questions/43349999/jdbctemplate-insert-or-update-oracle-blob-using-sql-merge by cc-by-sa and MIT license
'SPRING' 카테고리의 다른 글
[SPRING] 봄 + JPA + 최대 절전 모드 (0) | 2019.05.15 |
---|---|
[SPRING] Struts2; StrutsSpringTestCase JUnit 테스트를 위해 세션 열기 (0) | 2019.05.15 |
[SPRING] Spring : 애플리케이션 + 웹 컨텍스트를 어떻게 구성해야합니까? (0) | 2019.05.15 |
[SPRING] "이름으로 bean 작성 중 오류 발생"트랜잭션 관리자를 작성할 때 (0) | 2019.05.15 |
[SPRING] 트랜잭션 도중에 트랜잭션 읽기 전용 속성을 변경할 수 없습니다. (0) | 2019.05.15 |