DB To File 예제

DbToFileJob.xml 작성

---

▩ Job ID 설정

<job id="dbToFileJob" xmlns="http://www.springframework.org/schema/batch">
	<step id="step1">
		<tasklet>
			<chunk reader="dbToFileReader"
				writer="dbToFileWriter"
				commit-interval="1000"/>
		</tasklet>
	</step>
</job>

▩ Reader 설정 - DbToFileReader

<!-- Reader Task 작성 -->
<bean id="dbToFileReader" class="org.mybatis.spring.batch.MyBatisPagingItemReader" scope="step">
	<!-- Query ID 설정 -->
	<property name="queryId" value="selectFifaRankings" />
	
	<!-- PageSize 설정. 생략하면 default 적용. default: 10 -->
	<property name="pageSize" value="1000" />
	
	<!-- parameter 설정: 파라미터로 전달받은  날짜 이후 순위 조회를 위한 날짜 파라미터 설정 -->
	<property name="parameterValues">
		<map>
			<entry key="rnkDate" value="20170101" />
		</map>
	</property>
	
	<!-- sqlSession -->
	<property name="sqlSessionFactory" ref="sqlSession" />
</bean>

- MyBatisPagingItemReader 를 이용하여 페이징 처리하여 데이터를 조회한다.

- pageSize 크기를 주면 자동으로 Framework 내부적으로 페이징 처리를 한다.

- 파라미터 추가

▩ MySQLQuerySQL.xml

<?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="dbToFileSQL">
    <select id="selectFifaRankings" resultType="com.batchguide.vo.FifaRankingVO">
        select /* id:selectFifaRankings| Fifa 순위 조회 | DbToFileSQL.xml */
               a.rnk 
             , a.rnk_date 					
     	     , a.country_full 				
             , a.country_abrv
             , a.total_points
             , a.previous_points
             , a.rank_change
             , a.cur_year_avg
             , a.cur_year_avg_weighted
             , a.last_year_avg
             , a.last_year_avg_weighted
             , a.two_year_ago_avg
             , a.two_year_ago_weighted
             , a.three_year_ago_avg
             , a.three_year_ago_weighted
             , a.confederation
          from fifa_ranking a
         where rnk_date > #{rnkDate}
         limit #{_skiprows}, #{_pagesize}
    </select>	
</mapper>

- mySql 기준 쿼리

- File To DB와 달리 CamelCase가 적용 되므로 VO 클래스는 Camel 표기법 작성

- #{_skiprows}, #{_pageSize}는 변수명 그대로 사용해야함. Framework에서 넘어오는 변수명

- 오라클의 경우 아래와 같이 ROWNUM 을 이용하여 아래와 같이 작성

SELECT * 
  FROM (SELECT A.*
             , ROWNUM AS RN
          FROM (SELECT *
                  FROM ALL_TABLES) A
		 WHERE ROWNUM <= (#{_page}+1)*#{_pagesize})
 WHERE RN > (#{_page}*#{_pagesize})

▩ Writer 설정 - DbToFileWriter

<!-- Writer Bean Injection -->
<bean id="dbToFileWriter" class="org.springframework.batch.item.file.FlatFileItemWriter" scope="step">
  <property name="resource" value="file:{파일OUTPUT경로}/fifa_ranking_out.csv" />
  <property name="encoding" value="utf-8" />
  <property name="lineAggregator">
    <bean class="org.springframework.batch.item.file.transform.DelimitedLineAggregator">
      <property name="delimiter" value="," />
      <property name="fieldExtractor">
        <bean class="egovframework.rte.bat.core.item.file.transform.EgovFieldExtractor">
          <property name="names" value="rnk,rnkDate,countryFull,countryAbrv,totalPoints,previousPoints,rankChange
            ,curYearAvg,curYearAvgWeighted,lastYearAvg,lastYearAvgWeighted,twoYearAgoAvg,twoYearAgoWeighted
            ,threeYearAgoAvg,threeYearAgoWeighted,confederation" />
        </bean>
      </property>
    </bean>
  </property>
</bean>

- Reader에서 조회된 데이터 맵핑을 위해서 names value에 VO에 선언된 변수명을 적어주면 자동 맵핑 처리 된다.

▩ DbToFileJob 전체 설정

<?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">
						
	<job id="DbToFileJob" xmlns="http://www.springframework.org/schema/batch">
		<step id="step1">
			<tasklet>
				<chunk reader="dbToFileReader"
					writer="dbToFileWriter"
					commit-interval="1000"/>
			</tasklet>
		</step>
	</job>
	
	<!-- Reader 설정 -->
	<bean id="dbToFileReader" class="org.mybatis.spring.batch.MyBatisPagingItemReader" scope="step">
		<!-- Query ID 설정 -->
		<property name="queryId" value="selectFifaRankings" />
		
		<!-- PageSize 설정. 생략하면 default 적용. default: 10 -->
		<property name="pageSize" value="1000" />
		
		<!-- parameter 설정: 파라미터로 전달받은  날짜 이후 순위 조회를 위한 날짜 파라미터 설정 -->
		<property name="parameterValues">
			<map>
				<entry key="rnkDate" value="20170101" />
			</map>
		</property>
		
		<!-- sqlSession -->
		<property name="sqlSessionFactory" ref="sqlSession" />
	</bean>
	
	<!-- Writer 설정 -->
	<bean id="dbToFileWriter" class="org.springframework.batch.item.file.FlatFileItemWriter" scope="step">
		<property name="resource" value="file:{파일OUTPUT경로}/fifa_ranking_out.csv" />
		<property name="encoding" value="utf-8" />
		<property name="lineAggregator">
			<bean class="org.springframework.batch.item.file.transform.DelimitedLineAggregator">
				<property name="delimiter" value="," />
				<property name="fieldExtractor">
					<bean class="egovframework.rte.bat.core.item.file.transform.EgovFieldExtractor">
						<property name="names" value="rnk,rnkDate,countryFull,countryAbrv,totalPoints,previousPoints,rankChange
							,curYearAvg,curYearAvgWeighted,lastYearAvg,lastYearAvgWeighted,twoYearAgoAvg,twoYearAgoWeighted
							,threeYearAgoAvg,threeYearAgoWeighted,confederation" />
					</bean>
				</property>
			</bean>
		</property>
	</bean>
	
</beans>

[소스레파지토리]

☞ https://github.com/roopy1210/springbatch/blob/master/spring_batch_tutorial