Spring Batch Example 2 - DB to DB (1)

지난번에 이어서 이번에는 실제 로직을 처리할까 합니다. DB to DB

우선 Source table을 하나 만듭니다.

  CREATE TABLE "SPRING"."SOURCE_EAI_SEND" 

   ( "OPTY_NO" VARCHAR2(24 CHAR), 

"NAME" VARCHAR2(300 CHAR), 

"ACCNT_NAME" VARCHAR2(300 CHAR), 

"BIZ_NO" VARCHAR2(15 CHAR), 

"EAI_CDATE" DATE, 

"EAI_SEQ" NUMBER(38,0) NOT NULL ENABLE, 

"EAI_ERR_MSG" VARCHAR2(4000 BYTE), 

"EAI_DML_FLAG" VARCHAR2(1 CHAR), 

"EAI_SEND_FLAG" VARCHAR2(1 BYTE)

   ) SEGMENT CREATION IMMEDIATE 

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

그리고 인위적으로 데이터를 등록합니다.

REM INSERTING into table_export

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000100','사업1',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264196,null,null,'N');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000101','사업2',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264197,null,null,'N');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000102','구축1',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264198,null,null,'N');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000103','구축2','씨제이파워캐스트(주)','1298168203',to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264199,null,null,'N');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000104','구축3',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264200,null,null,'N');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000105','구축4',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264201,null,null,'N');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000106','1공구',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264202,null,null,'N');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000107','사업3',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264203,null,null,'N');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000108','시범사업1',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264204,null,null,'N');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000109','사업4',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264205,null,null,'C');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000110','구축5',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264206,null,null,'C');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000111','구축사업추진1',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264207,null,null,'C');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000112','통합 구축1',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264208,null,null,'C');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000113','전환 사업1',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264209,null,null,'C');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000114','구축 사업2',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264210,null,null,'C');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000115','구축공사',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264211,null,null,'C');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000116','구축 사업 A','연구소1','1068306775',to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264212,null,null,'C');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000223','교체',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264259,null,null,'C');

Insert into SOURCE_EAI_SEND (OPTY_NO,NAME,ACCNT_NAME,BIZ_NO,EAI_CDATE,EAI_SEQ,EAI_ERR_MSG,EAI_DML_FLAG,EAI_SEND_FLAG) values ('10000000224','영어사업1',null,null,to_timestamp('12/06/22','RR/MM/DD HH24:MI:SSXFF'),264260,null,null,'C');

job1.xml 파일이 있는 package에 data-source-job1.xml 파일을 만듭니다.

경로 : src/main/resources/job

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

xmlns:p="http://www.springframework.org/schema/p" 

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 

xmlns:batch="http://www.springframework.org/schema/batch"

xmlns:context="http://www.springframework.org/schema/context"

xmlns:jdbc="http://www.springframework.org/schema/jdbc"

xsi:schemaLocation="

http://www.springframework.org/schema/batch http://www.springframework.org/schema/batch/spring-batch-2.1.xsd

http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd

http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd

http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

<!-- ***** DataSoruce job1 ***** -->

<bean id="dataSourceJob1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">

<property name="driverClassName" value="${batch.jdbc.driver}" />

<property name="url" value="${batch.jdbc.url}" />

<property name="username" value="${batch.jdbc.user}" />

<property name="password" value="${batch.jdbc.password}" />

</bean>

<!-- ***** job1 transaction manager ***** -->

<bean id="transactionManagerJob1"

class="org.springframework.jdbc.datasource.DataSourceTransactionManager"

lazy-init="true">

<property name="dataSource" ref="dataSourceJob1" />

</bean>

<!-- ***** job1 SqlSessionFactory ***** -->

<bean id="sqlSessionFactoryJob1" class="org.mybatis.spring.SqlSessionFactoryBean">

<property name="dataSource" ref="dataSourceJob1" />

<property name="configLocation" value="classpath:/sqlmap/sqlmap-config.xml" />

</bean>

</beans>

sqlmap-config.xml 을 만들어야 합니다.

경로 : src/main/resources/sqlmap

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE configuration

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://www.mybatis.org/dtd/mybatis-3-config.dtd">

<!-- <!DOCTYPE configuration

PUBLIC "-//www.mybatis.org//DTD Config 3.0//EN"

"./src/main/resources/sqlmap/mybatis-3-config.dtd"> -->

<configuration>

<!-- mybatis setting -->

<settings>

<setting name="cacheEnabled" value="false" />

<setting name="lazyLoadingEnabled" value="false" />

<setting name="multipleResultSetsEnabled" value="true" />

<setting name="useColumnLabel" value="true" />

<setting name="useGeneratedKeys" value="false" />

<!-- <setting name="defaultExecutorType" value="SIMPLE" /> -->

<setting name="defaultExecutorType" value="BATCH" /> 

<setting name="defaultStatementTimeout" value="30000" />

<setting name="jdbcTypeForNull" value="OTHER"/>

</settings>

 

<!-- * Domain Alias를 설정한다 -->

<typeAliases>

<typeAlias type="com.inho.springbatch.domain.TestInfo" alias="TestInfo" />

</typeAliases>

<!-- * SQL 파일 위치를 지정한다. -->

<mappers>

<mapper resource="sqlmap/test/sql-map-test.xml" />

</mappers>

 

</configuration>

sql-map-test.xml 파일을 만듭니다.

경로 : src/main/resources/sqlmap/test

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper

        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

        "http://www.mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.inho.job1">

    <!-- ======================= -->

    <!-- SOURCE_EMP전 컬럼의 Result Map    -->

    <!-- ======================= -->

    <resultMap id="TestInfoResult" type="TestInfo">

<result property="optyNo" column="OPTY_NO" />

<result property="name" column="NAME" />

<result property="accntName" column="ACCNT_NAME" />

<result property="bizNo" column="BIZ_NO" />

<!-- <result property="rowId" column="ROW_ID" /> -->

<result property="eaiCdate" column="EAI_CDATE" />

<result property="eaiSeq" column="EAI_SEQ" />

<result property="eaiErrMsg" column="EAI_ERR_MSG" />

<result property="eaiDmlFlag" column="EAI_DML_FLAG" />

<result property="eaiSendFlag" column="EAI_SEND_FLAG" />

    </resultMap>

<select id="selectPagingTestInfo" resultMap="TestInfoResult">

<![CDATA[

SELECT EAI_SEQ

    ,OPTY_NO

,NAME

,ACCNT_NAME

,BIZ_NO

,EAI_DML_FLAG

   FROM (

SELECT  ROW_NUMBER() OVER(ORDER BY EAI_SEQ) RN

,EAI_SEQ

,OPTY_NO

,NAME

,ACCNT_NAME

,BIZ_NO

,EAI_DML_FLAG

FROM    SOURCE_EAI_SEND

WHERE   1=1

AND     EAI_SEND_FLAG = 'N'

)

   WHERE 

       RN <= #{_pagesize}

]]>    

</select>

<!-- CX Insert -->

</mapper>

sqlmapping 을 위해서 TestInfo.java 를 만듭니다.

package com.inho.springbatch.domain;

public class TestInfo {

private static final long serialVersionUID = -8417913833700006116L;

private String optyNo;

private String name;

private String accntName;

private String bizNo;

private String eaiDate;

private int eaiSeq;

private String eaiErrMsg;

private String eaiDmlFlag;

private String eaiSendFlag;

public String getOptyNo() {

return optyNo;

}

public void setOptyNo(String optyNo) {

this.optyNo = optyNo;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getAccntName() {

return accntName;

}

public void setAccntName(String accntName) {

this.accntName = accntName;

}

public String getBizNo() {

return bizNo;

}

public void setBizNo(String bizNo) {

this.bizNo = bizNo;

}

/* public String getRowId() {

return rowId;

}

public void setRowId(String rowId) {

this.rowId = rowId;

}*/

public int getEaiSeq() {

return eaiSeq;

}

public String getEaiDate() {

return eaiDate;

}

public void setEaiDate(String eaiDate) {

this.eaiDate = eaiDate;

}

public String getEaiErrMsg() {

return eaiErrMsg;

}

public void setEaiErrMsg(String eaiErrMsg) {

this.eaiErrMsg = eaiErrMsg;

}

public String getEaiSendFlag() {

return eaiSendFlag;

}

public void setEaiSendFlag(String eaiSendFlag) {

this.eaiSendFlag = eaiSendFlag;

}

public void setEaiSeq(int eaiSeq) {

this.eaiSeq = eaiSeq;

}

public String getEaiDmlFlag() {

return eaiDmlFlag;

}

public void setEaiDmlFlag(String eaiDmlFlag) {

this.eaiDmlFlag = eaiDmlFlag;

}

public static long getSerialversionuid() {

return serialVersionUID;

}

}