CompositeItemWriter 예제 - 구현
2018. 12. 2. 23:17ㆍSpring Batch
반응형
CompositeItemWriter 구현
20건의 CSV 파일에서 데이터를 읽어서 MySQL, Oracle 데이터베이스에 적재하도록 구현.
테스트는 정상인 경우 오류인 경우 트랜잭션에 대한 동작 여부 확인
CompositItemWriterJob.xml 작성
▩ Job ID 설정
<job id="compositeItemWriterJob" xmlns="http://www.springframework.org/schema/batch"> <step id="compositeWriterStep"> <tasklet> <chunk reader="compositeReader" writer="compositeWriter" commit-interval="100"/> </tasklet> </step> </job>
▩ Reader 설정 - compositeReader
<bean id="compositeReader" class="org.springframework.batch.item.file.FlatFileItemReader" scope="step">
<property name="resource" value="file:{파일경로}/fifa_ranking_delimiter_1.csv" />
<property name="lineMapper">
<bean class="egovframework.rte.bat.core.item.file.mapping.EgovDefaultLineMapper">
<property name="lineTokenizer">
<bean class="egovframework.rte.bat.core.item.file.transform.EgovDelimitedLineTokenizer">
<property name="delimiter" value=","/>
</bean>
</property>
<property name="objectMapper">
<bean class="egovframework.rte.bat.core.item.file.mapping.EgovObjectMapper">
<property name="type" value="com.batchguide.dto.FifaRankingDTO" />
<property name="names" value="rank,country_full,country_abrv,total_points,previous_points,rank_change,cur_year_avg,
cur_year_avg_weighted,last_year_avg,last_year_avg_weighted,two_year_ago_avg,two_year_ago_weighted,three_year_ago_avg,
three_year_ago_weighted,confederation,rank_date" />
</bean>
</property>
</bean>
</property>
</bean>
▩ Writer 설정 - compositeWriter
<bean id="compositeWriter" class="org.springframework.batch.item.support.CompositeItemWriter"> <property name="delegates"> <list> <ref bean="mysqlDbWriter" /> <ref bean="oracleDbWriter" /> </list> </property> </bean>
▩ Writer Reference Bean 설정 - mysqlDbWriter
<bean id="mysqlDbWriter" class="org.mybatis.spring.batch.MyBatisBatchItemWriter"> <property name="statementId" value="insertFifaRanking" /> <property name="sqlSessionTemplate" ref="batchMysqlSqlSessionTemplate" /> </bean>
▩ Writer Reference Bean 설정 - oracleDbWriter
<bean id="oracleDbWriter" class="org.mybatis.spring.batch.MyBatisBatchItemWriter"> <property name="statementId" value="insertFifaRanking1" /> <property name="sqlSessionTemplate" ref="batchOracleSqlSessionTemplate" /> </bean>
▩ OracleQuerySQL.xml - insertFifaRanking1 등록 쿼리 설정
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="oracleQuerySQL">
<insert id="insertFifaRanking1" parameterType="com.batchguide.dto.FifaRankingDTO">
INSERT /* id:insertFifaRanking1| Fifa 순위 저장 | DbToDbSQL.xml */
INTO FIFA_RANKING
(RNK
, RNK_DATE
, COUNTRY_FULL
, COUNTRY_ABRV
, TOTAL_POINTS
, PREVIOUS_POINTS
, RANK_CHANGE
, CUR_YEAR_AVG
, CUR_YEAR_AVG_WEIGHTED
, LAST_YEAR_AVG
, LAST_YEAR_AVG_WEIGHTED
, TWO_YEAR_AGO_AVG
, TWO_YEAR_AGO_WEIGHTED
, THREE_YEAR_AGO_AVG
, THREE_YEAR_AGO_WEIGHTED
, CONFEDERATION)
VALUES (#{rank}
, #{rank_date}
, #{country_full}
, #{country_abrv}
, #{total_points}
, #{previous_points}
, #{rank_change}
, #{cur_year_avg}
, #{cur_year_avg_weighted}
, #{last_year_avg}
, #{last_year_avg_weighted}
, #{two_year_ago_avg}
, #{two_year_ago_weighted}
, #{three_year_ago_avg}
, #{three_year_ago_weighted}
, #{confederation})
</insert>
</mapper>
CompositItemWriterJob.xml 전체
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/batch http://www.springframework.org/schema/batch/spring-batch.xsd"> <description> <![CDATA[예제는 파일데이터를 읽어서 MySQL, Oracle DB에 적재하는 예제이다. Reader는 FlatItemReader를 사용하며, Writer는 CompositeItemWriter를 사용하여서 처리한다. ]]> </description> <job id="compositeItemWriterJob" xmlns="http://www.springframework.org/schema/batch"> <step id="compositeWriterStep"> <tasklet> <chunk reader="compositeReader" writer="compositeWriter" commit-interval="100"/> </tasklet> </step> </job> <!-- Reader 설정 --> <bean id="compositeReader" class="org.springframework.batch.item.file.FlatFileItemReader" scope="step"> <property name="resource" value="file:C:/Users/pncrm/git/spring_batch_guide/spring_batch_guide/src/main/resources/config/batch/data/inputs/fifa_ranking_delimiter_1.csv" /> <property name="lineMapper"> <bean class="egovframework.rte.bat.core.item.file.mapping.EgovDefaultLineMapper"> <property name="lineTokenizer"> <bean class="egovframework.rte.bat.core.item.file.transform.EgovDelimitedLineTokenizer"> <property name="delimiter" value=","/> </bean> </property> <property name="objectMapper"> <bean class="egovframework.rte.bat.core.item.file.mapping.EgovObjectMapper"> <property name="type" value="com.batchguide.dto.FifaRankingDTO" /> <property name="names" value="rank,country_full,country_abrv,total_points,previous_points,rank_change,cur_year_avg, cur_year_avg_weighted,last_year_avg,last_year_avg_weighted,two_year_ago_avg,two_year_ago_weighted,three_year_ago_avg, three_year_ago_weighted,confederation,rank_date" /> </bean> </property> </bean> </property> </bean> <!-- CompositeWriter 설정 --> <bean id="compositeWriter" class="org.springframework.batch.item.support.CompositeItemWriter"> <property name="delegates"> <list> <ref bean="mysqlDbWriter" /> <ref bean="oracleDbWriter" /> </list> </property> </bean> <!-- MySQL Writer 설정 --> <bean id="mysqlDbWriter" class="org.mybatis.spring.batch.MyBatisBatchItemWriter"> <property name="statementId" value="insertFifaRanking" /> <property name="sqlSessionTemplate" ref="batchMysqlSqlSessionTemplate" /> </bean> <!-- Oracle Writer 설정 --> <bean id="oracleDbWriter" class="org.mybatis.spring.batch.MyBatisBatchItemWriter"> <property name="statementId" value="insertFifaRanking1" /> <property name="sqlSessionTemplate" ref="batchOracleSqlSessionTemplate" /> </bean> </beans>
테스트결과
테스트데이터가 20건이 되지 않으므로 commit-interval=10으로 설정 하여서 테스트 한다.
▩ 정상실행결과
▩ 실패실행결과
[DB조회결과]
- 처번째 10건 조회한 데이터 입력시 오류가 발생하므로 READ_COUNT 는 10으로 조회된다.
[콘솔실행결과]
2018-12-02 22:57:53,194 INFO [com.batchguide.CommandLineJobRunner] ###################### Command Line Job Runner Start ###################### Parameter[0] : TIMESTAMP=1543759073230 Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. 2018-12-02 22:58:01,255 ERROR [org.springframework.batch.core.step.AbstractStep] Encountered an error executing step compositeWriterStep in job compositeItemWriterJob org.springframework.jdbc.UncategorizedSQLException: oracleQuerySQL.insertFifaRanking1 (batch index #1) failed. Cause: java.sql.BatchUpdateException: ORA-12899: "SYSTEM"."FIFA_RANKING"."CONFEDERATION" 열에 대한 값이 너무 큼(실제: 8, 최대값: 5) ; uncategorized SQLException; SQL state [72000]; error code [12899]; ORA-12899: "SYSTEM"."FIFA_RANKING"."CONFEDERATION" 열에 대한 값이 너무 큼(실제: 8, 최대값: 5) ; nested exception is java.sql.BatchUpdateException: ORA-12899: "SYSTEM"."FIFA_RANKING"."CONFEDERATION" 열에 대한 값이 너무 큼(실제: 8, 최대값: 5) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-1.3.1.jar:1.3.1] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[mybatis-spring-1.3.1.jar:1.3.1] at com.sun.proxy.$Proxy26.flushStatements(Unknown Source) ~[?:?] at org.mybatis.spring.SqlSessionTemplate.flushStatements(SqlSessionTemplate.java:394) ~[mybatis-spring-1.3.1.jar:1.3.1] at org.mybatis.spring.batch.MyBatisBatchItemWriter.write(MyBatisBatchItemWriter.java:131) ~[mybatis-spring-1.3.1.jar:1.3.1] at org.springframework.batch.item.support.CompositeItemWriter.write(CompositeItemWriter.java:59) ~[spring-batch-infrastructure-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:185) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:151) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:284) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:209) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:272) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375) ~[spring-batch-infrastructure-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145) ~[spring-batch-infrastructure-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257) ~[spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200) [spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:66) [spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:67) [spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169) [spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144) [spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:136) [spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:308) [spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:141) [spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) [spring-core-5.0.8.RELEASE.jar:5.0.8.RELEASE] at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:134) [spring-batch-core-4.0.1.RELEASE.jar:4.0.1.RELEASE] at egovframework.rte.bat.core.launch.support.EgovCommandLineRunner.start(EgovCommandLineRunner.java:287) [egovframework.rte.bat.core-3.7.0.jar:?] at com.batchguide.util.RunBatch.start(RunBatch.java:78) [classes/:?] at com.batchguide.CommandLineJobRunner.main(CommandLineJobRunner.java:32) [classes/:?] Caused by: java.sql.BatchUpdateException: ORA-12899: "SYSTEM"."FIFA_RANKING"."CONFEDERATION" 열에 대한 값이 너무 큼(실제: 8, 최대값: 5) at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:11190) ~[ojdbc7-12.1.0.jar:12.1.0.1.0] at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:244) ~[ojdbc7-12.1.0.jar:12.1.0.1.0] at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297) ~[commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297) ~[commons-dbcp-1.4.jar:1.4] at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:122) ~[mybatis-3.4.1.jar:3.4.1] at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:129) ~[mybatis-3.4.1.jar:3.4.1] at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:122) ~[mybatis-3.4.1.jar:3.4.1] at org.apache.ibatis.executor.CachingExecutor.flushStatements(CachingExecutor.java:114) ~[mybatis-3.4.1.jar:3.4.1] at org.apache.ibatis.session.defaults.DefaultSqlSession.flushStatements(DefaultSqlSession.java:253) ~[mybatis-3.4.1.jar:3.4.1] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_152] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_152] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_152] at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.8.0_152] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.1.jar:1.3.1] ... 32 more 2018-12-02 22:58:01,328 INFO [com.batchguide.util.RunBatch] Status Code: 1 2018-12-02 22:58:01,328 ERROR [com.batchguide.util.RunBatch] compositeItemWriterJob 실패 [FAIL] 2018-12-02 22:58:01,328 ERROR [com.batchguide.util.RunBatch] Error Message: 2018-12-02 22:58:01,328 INFO [com.batchguide.CommandLineJobRunner] ###################### Command Line Job Runner End ######################
[소스레파지토리]
☞ https://github.com/roopy1210/springbatch/blob/master/spring_batch_tutorial
반응형
'Spring Batch' 카테고리의 다른 글
| Range Partitioning 예제 - 구현 (0) | 2018.12.18 |
|---|---|
| Range Partitioning 예제 - 설정 (0) | 2018.12.17 |
| CompositeItemWriter 예제 - 설정 (0) | 2018.12.02 |
| DB To File 예제 (0) | 2018.11.14 |
| MULTI FILE TO DB - 다중파일 예제 (0) | 2018.11.01 |