Mysql 에서 Spring Batch를 이용하여 Job Repository Table 생성 또는 삭제하는 예제 - 1

 

Spring Batch 의 가장 큰 이슈(절대 문제는 아님)는 하나의 Repository를 여러 Job 이 사용하다가 실패될 가능성이 많다. 따라서 가장 확실하게 하나의 Job은 하나의 Repository를 쓰는 것이 가장 안전하다!

 

http://knight76.tistory.com/1191  (관련내용)

 

그러나, 이런 이슈 때문에 매번 Table 이름에 Prefix를 줄 때 상당히 힘이 들 수 밖에 없다. 오타라도 나면. 끔찍하다. 지울 때 잘 못 치우면 지우지도 귀찮은 일이 벌어지기도 한다.

 

mysql 의 예제) Spring Batch lib 에 담겨 있음.

 

schema-mysql.sql -- Autogenerated: do not edit this file CREATE TABLE BATCH_JOB_INSTANCE  ( JOB_INSTANCE_ID BIGINT  NOT NULL PRIMARY KEY , VERSION BIGINT , JOB_NAME VARCHAR(100) NOT NULL, JOB_KEY VARCHAR(32) NOT NULL, constraint JOB_INST_UN unique (JOB_NAME, JOB_KEY) ) ENGINE=InnoDB; CREATE TABLE BATCH_JOB_EXECUTION  ( JOB_EXECUTION_ID BIGINT  NOT NULL PRIMARY KEY , VERSION BIGINT  , JOB_INSTANCE_ID BIGINT NOT NULL, CREATE_TIME DATETIME NOT NULL, START_TIME DATETIME DEFAULT NULL , END_TIME DATETIME DEFAULT NULL , STATUS VARCHAR(10) , EXIT_CODE VARCHAR(100) , EXIT_MESSAGE VARCHAR(2500) , LAST_UPDATED DATETIME, constraint JOB_INST_EXEC_FK foreign key (JOB_INSTANCE_ID) references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID) ) ENGINE=InnoDB; CREATE TABLE BATCH_JOB_PARAMS  ( JOB_INSTANCE_ID BIGINT NOT NULL , TYPE_CD VARCHAR(6) NOT NULL , KEY_NAME VARCHAR(100) NOT NULL , STRING_VAL VARCHAR(250) , DATE_VAL DATETIME DEFAULT NULL , LONG_VAL BIGINT , DOUBLE_VAL DOUBLE PRECISION , constraint JOB_INST_PARAMS_FK foreign key (JOB_INSTANCE_ID) references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID) ) ENGINE=InnoDB; CREATE TABLE BATCH_STEP_EXECUTION  ( STEP_EXECUTION_ID BIGINT  NOT NULL PRIMARY KEY , VERSION BIGINT NOT NULL, STEP_NAME VARCHAR(100) NOT NULL, JOB_EXECUTION_ID BIGINT NOT NULL, START_TIME DATETIME NOT NULL , END_TIME DATETIME DEFAULT NULL , STATUS VARCHAR(10) , COMMIT_COUNT BIGINT , READ_COUNT BIGINT , FILTER_COUNT BIGINT , WRITE_COUNT BIGINT , READ_SKIP_COUNT BIGINT , WRITE_SKIP_COUNT BIGINT , PROCESS_SKIP_COUNT BIGINT , ROLLBACK_COUNT BIGINT , EXIT_CODE VARCHAR(100) , EXIT_MESSAGE VARCHAR(2500) , LAST_UPDATED DATETIME, constraint JOB_EXEC_STEP_FK foreign key (JOB_EXECUTION_ID) references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID) ) ENGINE=InnoDB; CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT  ( STEP_EXECUTION_ID BIGINT NOT NULL PRIMARY KEY, SHORT_CONTEXT VARCHAR(2500) NOT NULL, SERIALIZED_CONTEXT TEXT , constraint STEP_EXEC_CTX_FK foreign key (STEP_EXECUTION_ID) references BATCH_STEP_EXECUTION(STEP_EXECUTION_ID) ) ENGINE=InnoDB; CREATE TABLE BATCH_JOB_EXECUTION_CONTEXT  ( JOB_EXECUTION_ID BIGINT NOT NULL PRIMARY KEY, SHORT_CONTEXT VARCHAR(2500) NOT NULL, SERIALIZED_CONTEXT TEXT , constraint JOB_EXEC_CTX_FK foreign key (JOB_EXECUTION_ID) references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID) ) ENGINE=InnoDB; CREATE TABLE BATCH_STEP_EXECUTION_SEQ (ID BIGINT NOT NULL) ENGINE=MYISAM; INSERT INTO BATCH_STEP_EXECUTION_SEQ values(0); CREATE TABLE BATCH_JOB_EXECUTION_SEQ (ID BIGINT NOT NULL) ENGINE=MYISAM; INSERT INTO BATCH_JOB_EXECUTION_SEQ values(0); CREATE TABLE BATCH_JOB_SEQ (ID BIGINT NOT NULL) ENGINE=MYISAM; INSERT INTO BATCH_JOB_SEQ values(0);

 

 

Spring Batch Table을 생성 하고 삭제하는 아주 간단한 예제를 공유한다. 버그가 많겠지만.. 대충 쓰기에는 좋을 듯 하다. (돌아갈 정도로만 만들었음..)

 

예제는 Web call로 mysql에 Spring Batch repository table 생성하는 코드이다.  코드는 완전하지 않을 수 있음.

 

 

 

1. 설정파트

 

접속해야 할 DB 정보는 db.properties 에 저장한다.

repository.jdbc.url=… repository.jdbc.username=… repository.jdbc.password=… repository.jdbc.maxActive=2 repository.jdbc.maxIdle=1

 

/beans-batchdb.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"     xmlns:context="http://www.springframework.org/schema/context"     xmlns:tx="http://www.springframework.org/schema/tx"     xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd                            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd                            http://www.springframework.org/schema/batch http://www.springframework.org/schema/batch/spring-batch-2.1.xsd                            http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">                                <bean id="repositoryDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">         <property name="url" value="${repository.jdbc.url}" />         <property name="driverClassName" value="net.sf.log4jdbc.DriverSpy"/>         <property name="username" value="${repository.jdbc.username}" />         <property name="password" value="${repository.jdbc.password}" />         <property name="maxActive"             value="${repository.jdbc.maxActive}"/>         <property name="maxIdle"             value="${repository.jdbc.maxIdle}"/>         <property name="validationQuery"     value="select 1"/>         <property name="defaultAutoCommit" value="false"/>         <property name="testOnBorrow" value="false"/>         <property name="testWhileIdle" value="true"/>         <property name="timeBetweenEvictionRunsMillis" value="60000"/>         <property name="numTestsPerEvictionRun" value="3"/>         <property name="minEvictableIdleTimeMillis" value="-1"/>         <property name="maxWait" value="8000"/>      </bean>         <bean id="repositoryDataSourceSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">         <property name="dataSource" ref="repositoryDataSource" />        <!—mybatis 설정 추가 -->         <property name="configLocation" value="classpath:/configfile.xml" />     </bean>         <bean id="repositorySqlSession" class="org.mybatis.spring.SqlSessionTemplate">           <constructor-arg index="0" ref="repositoryDataSourceSqlSessionFactory" />     </bean>     </beans>

 

 

/job/create-repository/benas-create-repository.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"     xmlns:context="http://www.springframework.org/schema/context"     xmlns:tx="http://www.springframework.org/schema/tx"     xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd                            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd                            http://www.springframework.org/schema/batch http://www.springframework.org/schema/batch/spring-batch-2.1.xsd                            http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">                                <bean id="dataSourceInitializerForCreation" class="com.google.batch.repository.DataSourceInitializer">         <property name="dataSource" ref="repositoryDataSource" />         <property name="initScripts" value="classpath:/org/springframework/batch/core/schema-mysql.sql" />     </bean>         <bean id="dataSourceInitializerForDrop" class="com.google.batch.repository.DataSourceInitializer">         <property name="dataSource" ref="repositoryDataSource" />         <property name="initScripts" value="classpath:/org/springframework/batch/core/schema-drop-mysql.sql" />     </bean>          <job id="repositoryJobTableCreation" parent="repository_simpleJob" xmlns="http://www.springframework.org/schema/batch">         <step id="step1" parent="repository_simpleStep">             <tasklet ref="repositoryTasklet" />         </step>     </job>         <job id="repositoryJobTableDrop" parent="repository_simpleJob" xmlns="http://www.springframework.org/schema/batch">         <step id="step2" parent="repository_simpleStep">             <tasklet ref="repositoryDropTasklet" />         </step>     </job>           <bean id="repositoryJobExecutionListener" class="com.google.batch.repository.RepositoryJobExecutionListener"/>           <bean id="repository_simpleJob" class="org.springframework.batch.core.job.SimpleJob" abstract="true">         <property name="jobRepository" ref="jobRepository" />          <property name="jobExecutionListeners" ref="repositoryJobExecutionListener" />     </bean>     <bean id="repository_simpleStep" class="org.springframework.batch.core.step.item.SimpleStepFactoryBean" abstract="true">         <property name="jobRepository" ref="jobRepository" />         <property name="transactionManager" ref="resourcelessTransactionManager"></property>         <property name="commitInterval" value="1"/>     </bean>           <bean id="repositoryTasklet" class="com.google.batch.repository.RepositoryTasklet">          <property name="dataSourceInitializer" ref="dataSourceInitializerForCreation"></property>      </bean>            <bean id="repositoryDropTasklet" class="com.google.batch.repository.RepositoryTasklet">          <property name="dataSourceInitializer" ref="dataSourceInitializerForDrop"></property>      </bean>          <bean id="jobRepository" class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean">         <property name="transactionManager" ref="resourcelessTransactionManager"/>     </bean>         <bean id="resourcelessTransactionManager" class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" />         <bean id="repositoryJobLauncher" class="org.springframework.batch.core.launch.support.SimpleJobLauncher">         <property name="jobRepository" ref="jobRepository" />     </bean>         </beans>

 

 

beans.xml

<context:property-placeholder location="classpath:db.properties" /> <import resource="/beans-batchdb.xml"/>     <context:annotation-config />     <context:component-scan base-package="com.google"/>    <!--  job #1  : create repository --> <import resource="classpath:/job/create-repository/beans-create-repository.xml" />

 

 

2. 자바 코드

com.google.batch.repository.DataSourceInitailizer

 

/* * Copyright 2006-2007 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * *      http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.google.batch.repository;   import java.io.IOException; import java.util.List; import javax.sql.DataSource; import org.apache.commons.io.IOUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.beans.factory.BeanInitializationException; import org.springframework.beans.factory.DisposableBean; import org.springframework.beans.factory.InitializingBean; import org.springframework.core.io.Resource; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.transaction.TransactionStatus; import org.springframework.transaction.support.TransactionCallback; import org.springframework.transaction.support.TransactionTemplate; import org.springframework.util.Assert; import org.springframework.util.StringUtils; /** * Wrapper for a {@link DataSource} that can run scripts on start up and shut * down.  Us as a bean definition <br/><br/> * * Run this class to initialize a database in a running server process. * Make sure the server is running first by launching the "hsql-server" from the * <code>hsql.server</code> project. Then you can right click in Eclipse and * Run As -&gt; Java Application. Do the same any time you want to wipe the * database and start again. * * @author Dave Syer * @author Kim, Yong Hwan * */ public class DataSourceInitializer implements InitializingBean, DisposableBean {     private static final Log logger = LogFactory.getLog(DataSourceInitializer.class);     private Resource[] initScripts;     private DataSource dataSource;     private String replacePreFix;     public String getReplacePreFix() {         return replacePreFix;     }     public void setReplacePreFix(String replacePreFix) {         this.replacePreFix = replacePreFix;     }     protected void finalize() throws Throwable {         super.finalize();         logger.debug("finalize called");     }     public void destroy() {         // no use     }     public void afterPropertiesSet() throws Exception {         Assert.notNull(dataSource);     }     public void initialize() {             destroy();             if (initScripts != null) {                 for (int i = 0; i < initScripts.length; i++) {                     Resource initScript = initScripts[i];                     logger.debug("Initializing with script: " + initScript);                     doExecuteScript(initScript);                 }             }     }     @SuppressWarnings("unchecked")     private void doExecuteScript(final Resource scriptResource) {         if (scriptResource == null || !scriptResource.exists())             return;         TransactionTemplate transactionTemplate = new TransactionTemplate(new DataSourceTransactionManager(dataSource));         transactionTemplate.execute(new TransactionCallback() {             public Object doInTransaction(TransactionStatus status) {                 JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);                 String[] scripts;                 try {                     scripts = StringUtils.delimitedListToStringArray(stripComments(IOUtils.readLines(scriptResource.getInputStream())), ";");                 } catch (IOException e) {                     throw new BeanInitializationException("Cannot load script from [" + scriptResource + "]", e);                 }                 for (int i = 0; i < scripts.length; i++) {                     String script = scripts[i].trim();                     if (StringUtils.hasText(script)) {                         try {                            // replace                             script = script.replaceAll("BATCH_", replacePreFix);                             script = script.replaceAll("_FK", "_" + replacePreFix + "FK");                                                         logger.info(script);                             jdbcTemplate.execute(script);                         } catch (DataAccessException e) {                             if (script.toLowerCase().contains("drop")) {                                 logger.debug("DROP script failed (ignoring): " + script);                             } else {                                 throw e;                             }                         }                     }                 }                 return null;             }         });     }     private String stripComments(List<String> list) {         StringBuffer buffer = new StringBuffer();         for (String line : list) {             if (!line.startsWith("//") && !line.startsWith("--")) {                 buffer.append(line + "\n");             }         }         return buffer.toString();     }     public void setInitScripts(Resource[] initScripts) {         this.initScripts = initScripts;     }     public void setDataSource(DataSource dataSource) {         this.dataSource = dataSource;     } }

 

 

 

 

com.google.batch.repository.RepositoryJobExecutionListener

package com.google.batch.repository;   import java.util.Map; import org.springframework.batch.core.BatchStatus; import org.springframework.batch.core.JobExecution; import org.springframework.batch.core.JobExecutionListener; import org.springframework.batch.core.JobParameter; import org.springframework.batch.core.JobParameters; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.ConfigurableApplicationContext; public class RepositoryJobExecutionListener implements JobExecutionListener {     private ConfigurableApplicationContext context;         @Autowired     public void setContext(ConfigurableApplicationContext ctx){         this.context = ctx;     }         @Override     public void beforeJob(JobExecution paramJobExecution) {         JobParameters params = paramJobExecution.getJobInstance().getJobParameters();         Map<String, JobParameter> map = params.getParameters();         String replacePreFix = map.get("prefix").toString();         if (!replacePreFix.endsWith("_")) {             replacePreFix = replacePreFix + "_";         }         DataSourceInitializer dsinit =(DataSourceInitializer)context.getBean("dataSourceInitializerForCreation");         dsinit.setReplacePreFix(replacePreFix);                 DataSourceInitializer dsinit2 =(DataSourceInitializer)context.getBean("dataSourceInitializerForDrop");         dsinit2.setReplacePreFix(replacePreFix);     }     @Override     public void afterJob(JobExecution jobExecution) {         if( jobExecution.getStatus() == BatchStatus.COMPLETED ){             //job success             System.out.println("completed");         }         else if(jobExecution.getStatus() == BatchStatus.FAILED){             //job failure             System.out.println("failed");         }     } }

 

 

com.google.batch.repository.RepositoryTasklet

package com.google.batch.repository;   import org.springframework.batch.core.StepContribution; import org.springframework.batch.core.scope.context.ChunkContext; import org.springframework.batch.core.step.tasklet.Tasklet; import org.springframework.batch.repeat.RepeatStatus;   public class RepositoryTasklet implements Tasklet {     private DataSourceInitializer dataSourceInitializer;         public DataSourceInitializer getDataSourceInitializer() {         return dataSourceInitializer;     }     public void setDataSourceInitializer(DataSourceInitializer dataSourceInitializer) {         this.dataSourceInitializer = dataSourceInitializer;     }         @Override     public RepeatStatus execute(StepContribution contribution,             ChunkContext chunkContext) throws Exception {         // creation or deletion table         dataSourceInitializer.initialize();         return RepeatStatus.FINISHED;     } }

 

 

 

JobRepositoryController

 

package com.google.batch.controller;   import org.springframework.batch.core.Job; import org.springframework.batch.core.JobParameters; import org.springframework.batch.core.converter.DefaultJobParametersConverter; import org.springframework.batch.core.launch.JobLauncher; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.HttpStatus; import org.springframework.stereotype.Controller; import org.springframework.util.StringUtils; import org.springframework.web.bind.annotation.ExceptionHandler; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.bind.annotation.ResponseStatus;   @Controller public class JobRepositoryController {     @Autowired     private JobLauncher repositoryJobLauncher;     @Autowired     private Job repositoryJobTableCreation;     @Autowired     private Job repositoryJobTableDrop;         @RequestMapping(value = "/repository/{type}/{prefix}", method = {RequestMethod.POST, RequestMethod.GET})     @ResponseBody     public String repository(@PathVariable String type, @PathVariable String prefix) throws Exception {         if (type == null || prefix == null) {             throw new WrongParamException();         }                 if (prefix.length() < 2) {             throw new WrongParamException();         }         String[] parameters = new String[2];         parameters[0] = "prefix=" + prefix;          Properties props = StringUtils.splitArrayElementsIntoProperties(parameters, "="); JobParametersBuilder builder = new JobParametersBuilder();          builder.addLong("currTime", System.currentTimeMillis()); for(Object okey : props.keySet() ) {      String key = (String) okey;               builder.addString(key, (String) props.get(key));         } JobParameters jobParameters = builder.toJobParameters();         if (type.equalsIgnoreCase("create")) {             repositoryJobLauncher.run(repositoryJobTableCreation, jobParameters);         } else if (type.equalsIgnoreCase("drop")) {             repositoryJobLauncher.run(repositoryJobTableDrop, jobParameters);         } else {             throw new WrongParamException();         }         return "success";     }          }

 

 

3. 실행 테스트

 

(1) table 생성

$ curl -X GET http://localhost/batch/repository/create/itemid_ success

 

 

 

 

 

(2) table 삭제

$ curl -X GET http://localhost/batch/repository/drop/itemid_ success

* ResourcelessTransactionManager  클래스의 단점이 있다.

위의 예제는 하나의 JVM에서 동작할 때 하나의 Job만 동작하는 경우에 쓸모가 있다. 즉, 다른 Job과 동시에 작업하는 Batch Job으로 동작하는 경우, ResourcelessTransactionManager 는 동작하지 않는다. 

다른 Job의 TransactionManager로 바뀌는 부분이 존재한다. 따라서, 위의 예제는 하나의 JVM 인스턴스로 동작하는 경우에서만 사용할 수 있다. 다른 Job 과 동시에 사용해야 하는 경우 위의 예제를 사용할 수 없다.

다음 내용으로 변경해서 해결했다. 

http://knight76.tistory.com/entry/Mysql-에서-Spring-Batch를-이용하여-Job-Repository-Table-생성-또는-삭제하는-예제-2