FILE TO DB - Delimiter 예제

사전준비

---

아래 사이트에서 예제 CSV 파일을 다운 받는다.

https://www.kaggle.com/tadhgfitzgerald/fifa-international-soccer-mens-ranking-1993now

테이블 생성

---

CREATE TABLE `fifa_ranking` (
  `rnk` smallint(6) NOT NULL,
  `rnk_date` varchar(8) NOT NULL,
  `country_full` varchar(50) NOT NULL,
  `country_abrv` varchar(10) DEFAULT NULL,
  `total_points` double DEFAULT NULL,
  `previous_points` smallint(6) DEFAULT NULL,
  `rank_change` smallint(6) DEFAULT NULL,
  `cur_year_avg` double DEFAULT NULL,
  `cur_year_avg_weighted` double DEFAULT NULL,
  `last_year_avg` double DEFAULT NULL,
  `last_year_avg_weighted` double DEFAULT NULL,
  `two_year_ago_avg` double DEFAULT NULL,
  `two_year_ago_weighted` double DEFAULT NULL,
  `three_year_ago_avg` double DEFAULT NULL,
  `three_year_ago_weighted` double DEFAULT NULL,
  `confederation` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

FileDelimiterToDbJob.xml 작성

---

• Job 설정의 Step을 정의한다.
• Commit-interval 옵션을 이용하여 Commit 단위 설정.(Default 값은 100)
• Chunk 부분에서 Reader, Writer ID를 정의한다.(별도의 로직처리는 없으므로 Processor는 생략한다.)

▩ Job ID 설정

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

▩ Reader 설정

<!-- Reader Task 작성 -->
<bean id="fileDelimiterToDbReader" class="org.springframework.batch.item.file.FlatFileItemReader" scope="step">
	<property name="resource" value="file:{파일위치경로}\fifa_ranking_delimiter.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>

- EgovDefaultLineMapper 클래스를 이용하여서 Delimiter 구분자별로 파싱 처리

- EgovObjectMapper 클래스를 이용하여 파싱결과를 DTO 클래스 맵핑 처리

- {파일경로}는 자신의 로컬 환경에 맞게 수정

▩ Writer 설정

<!-- Writer Bean Injection -->
<bean id="fileDelimiterToDbWriter" class="org.mybatis.spring.batch.MyBatisBatchItemWriter">
  <property name="statementId" value="insertFifaRanking" />
  <property name="sqlSessionTemplate" ref="batchSqlSessionTemplate" />
</bean>

▩ FifaRankingDTO 작성

EgovObjectMapper 클래스를 통하여서 값을 설정하는데 내부적으로는 Reflector를 이용하여서 처리 된다. 작성시 주의할점은 objectMapper의 names에 선언된 동일한 변수를 사용해야 한다.

package com.batchguide.dto;

public class FifaRankingDTO {

private String rank;
	
	private String rank_date;
	
	private String country_full;
	
	private String country_abrv;
	
	private double total_points;
	
	private int previous_points;
	
	private int rank_change;
	
	private double cur_year_avg;
	
	private double cur_year_avg_weighted;
	
	private double last_year_avg;
	
	private double last_year_avg_weighted;
	
	private double two_year_ago_avg;
	
	private double two_year_ago_weighted;
	
	private double three_year_ago_avg;
	
	private double three_year_ago_weighted;
	
	private String confederation;

	/**
	 * @return the rank
	 */
	public String getRank() {
		return rank;
	}

	/**
	 * @param rank the rank to set
	 */
	public void setRank(String rank) {
		this.rank = rank;
	}

	/**
	 * @return the rank_date
	 */
	public String getRank_date() {
		return rank_date;
	}

	/**
	 * @param rank_date the rank_date to set
	 */
	public void setRank_date(String rank_date) {
		this.rank_date = rank_date.replace("-", "");
	}

	/**
	 * @return the country_full
	 */
	public String getCountry_full() {
		return country_full;
	}

	/**
	 * @param country_full the country_full to set
	 */
	public void setCountry_full(String country_full) {
		this.country_full = country_full;
	}

	/**
	 * @return the country_abrv
	 */
	public String getCountry_abrv() {
		return country_abrv;
	}

	/**
	 * @param country_abrv the country_abrv to set
	 */
	public void setCountry_abrv(String country_abrv) {
		this.country_abrv = country_abrv;
	}

	/**
	 * @return the total_points
	 */
	public double getTotal_points() {
		return total_points;
	}

	/**
	 * @param total_points the total_points to set
	 */
	public void setTotal_points(double total_points) {
		this.total_points = total_points;
	}

	/**
	 * @return the previous_points
	 */
	public int getPrevious_points() {
		return previous_points;
	}

	/**
	 * @param previous_points the previous_points to set
	 */
	public void setPrevious_points(int previous_points) {
		this.previous_points = previous_points;
	}

	/**
	 * @return the rank_change
	 */
	public int getRank_change() {
		return rank_change;
	}

	/**
	 * @param rank_change the rank_change to set
	 */
	public void setRank_change(int rank_change) {
		this.rank_change = rank_change;
	}

	/**
	 * @return the cur_year_avg
	 */
	public double getCur_year_avg() {
		return cur_year_avg;
	}

	/**
	 * @param cur_year_avg the cur_year_avg to set
	 */
	public void setCur_year_avg(double cur_year_avg) {
		this.cur_year_avg = cur_year_avg;
	}

	/**
	 * @return the cur_year_avg_weighted
	 */
	public double getCur_year_avg_weighted() {
		return cur_year_avg_weighted;
	}

	/**
	 * @param cur_year_avg_weighted the cur_year_avg_weighted to set
	 */
	public void setCur_year_avg_weighted(double cur_year_avg_weighted) {
		this.cur_year_avg_weighted = cur_year_avg_weighted;
	}

	/**
	 * @return the last_year_avg
	 */
	public double getLast_year_avg() {
		return last_year_avg;
	}

	/**
	 * @param last_year_avg the last_year_avg to set
	 */
	public void setLast_year_avg(double last_year_avg) {
		this.last_year_avg = last_year_avg;
	}

	/**
	 * @return the last_year_avg_weighted
	 */
	public double getLast_year_avg_weighted() {
		return last_year_avg_weighted;
	}

	/**
	 * @param last_year_avg_weighted the last_year_avg_weighted to set
	 */
	public void setLast_year_avg_weighted(double last_year_avg_weighted) {
		this.last_year_avg_weighted = last_year_avg_weighted;
	}

	/**
	 * @return the two_year_ago_avg
	 */
	public double getTwo_year_ago_avg() {
		return two_year_ago_avg;
	}

	/**
	 * @param two_year_ago_avg the two_year_ago_avg to set
	 */
	public void setTwo_year_ago_avg(double two_year_ago_avg) {
		this.two_year_ago_avg = two_year_ago_avg;
	}

	/**
	 * @return the two_year_ago_weighted
	 */
	public double getTwo_year_ago_weighted() {
		return two_year_ago_weighted;
	}

	/**
	 * @param two_year_ago_weighted the two_year_ago_weighted to set
	 */
	public void setTwo_year_ago_weighted(double two_year_ago_weighted) {
		this.two_year_ago_weighted = two_year_ago_weighted;
	}

	/**
	 * @return the three_year_ago_avg
	 */
	public double getThree_year_ago_avg() {
		return three_year_ago_avg;
	}

	/**
	 * @param three_year_ago_avg the three_year_ago_avg to set
	 */
	public void setThree_year_ago_avg(double three_year_ago_avg) {
		this.three_year_ago_avg = three_year_ago_avg;
	}

	/**
	 * @return the three_year_ago_weighted
	 */
	public double getThree_year_ago_weighted() {
		return three_year_ago_weighted;
	}

	/**
	 * @param three_year_ago_weighted the three_year_ago_weighted to set
	 */
	public void setThree_year_ago_weighted(double three_year_ago_weighted) {
		this.three_year_ago_weighted = three_year_ago_weighted;
	}

	/**
	 * @return the confederation
	 */
	public String getConfederation() {
		return confederation;
	}

	/**
	 * @param confederation the confederation to set
	 */
	public void setConfederation(String confederation) {
		this.confederation = confederation;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "Ftd001VO [rank=" + rank + ", rank_date=" + rank_date + ", country_full=" + country_full
				+ ", country_abrv=" + country_abrv + ", total_points=" + total_points + ", previous_points="
				+ previous_points + ", rank_change=" + rank_change + ", cur_year_avg=" + cur_year_avg
				+ ", cur_year_avg_weighted=" + cur_year_avg_weighted + ", last_year_avg=" + last_year_avg
				+ ", last_year_avg_weighted=" + last_year_avg_weighted + ", two_year_ago_avg=" + two_year_ago_avg
				+ ", two_year_ago_weighted=" + two_year_ago_weighted + ", three_year_ago_avg=" + three_year_ago_avg
				+ ", three_year_ago_weighted=" + three_year_ago_weighted + ", confederation=" + confederation + "]";
	}

}

▩ 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="mySQLQuerySQL">

	<insert id="insertFifaRanking" parameterType="com.batchguide.dto.FifaRankingDTO">
		insert /* id:insertFifaRanking| Fifa 순위 저장 | fileToDbSQL.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>

▩ 전체설정파일

<?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="fileDelimiterToDbJob" xmlns="http://www.springframework.org/schema/batch">
		<step id="step1">
			<tasklet>
				<chunk reader="fileItemReader"
					writer="dbWriter"
					commit-interval="1000"/>
			</tasklet>
		</step>
	</job>
	
	<!-- Reader 설정 -->
	<bean id="fileItemReader" class="org.springframework.batch.item.file.FlatFileItemReader" scope="step">
		<property name="resource" value="file:{파일경로}/fifa_ranking_delimiter.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 설정 -->
	<bean id="dbWriter" class="org.mybatis.spring.batch.MyBatisBatchItemWriter">
		<property name="statementId" value="insertFifaRanking" />
		<property name="sqlSessionTemplate" ref="batchSqlSessionTemplate" />
	</bean>
	
</beans>

※ 파일경로는 자신의 설정에 맞게 수정하면 된다.

프로그램 수행

---

프로그램 수행결과

---

입력건수: 57793, 적재건수: 57793는 동일하게 일치 해야함

▩ Job 수행결과

# JOB 실행 결과  조회
SELECT A.JOB_INSTANCE_ID
     , A.JOB_NAME
     , B.JOB_EXECUTION_ID
     , B.CREATE_TIME
     , B.START_TIME
     , B.END_TIME
     , B.STATUS
     , B.EXIT_CODE
     , B.EXIT_MESSAGE
  FROM BATCH_JOB_INSTANCE A
       JOIN BATCH_JOB_EXECUTION B ON B.JOB_INSTANCE_ID = A.JOB_INSTANCE_ID
 WHERE A.JOB_INSTANCE_ID = (SELECT JOB_INSTANCE_ID FROM BATCH_JOB_INSTANCE ORDER BY JOB_INSTANCE_ID DESC LIMIT 1)

Job 수행시간은 15초 

▩ Step 수행결과

# STEP 실행결과
SELECT STEP_EXECUTION_ID
     , STEP_NAME
     , START_TIME
     , END_TIME
     , STATUS
     , COMMIT_COUNT
     , READ_COUNT
     , FILTER_COUNT
     , WRITE_COUNT
     , READ_SKIP_COUNT
     , WRITE_SKIP_COUNT
     , PROCESS_SKIP_COUNT
     , ROLLBACK_COUNT
     , EXIT_CODE
     , EXIT_MESSAGE
  FROM BATCH_STEP_EXECUTION
 WHERE JOB_EXECUTION_ID = (SELECT B.JOB_EXECUTION_ID
              		     FROM BATCH_JOB_INSTANCE A
              		     JOIN BATCH_JOB_EXECUTION B ON B.JOB_INSTANCE_ID = A.JOB_INSTANCE_ID
              	           WHERE A.JOB_INSTANCE_ID = (SELECT JOB_INSTANCE_ID FROM BATCH_JOB_INSTANCE ORDER BY JOB_INSTANCE_ID DESC LIMIT 1))

예상대로 입력건수와, 적재건수 일치 확인

[소스레파지토리]

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