Dynamically Switch SQL statements in Spring Batch

Dynamically Switch SQL statements in Spring Batch

here's a quick example of how to switch out sql statements in Spring Batch using step scope.

the job

the batch resource

and the database resource

the supporting itemwriter

package de.incompleteco.spring.batch.item;

public class SystemOutItemWriter {

 public void write(Object object) {
  System.out.println(object);
 }
 
}

the supporting rowmapper

package de.incompleteco.spring.batch.data;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class ColumnRowMapper implements RowMapper {

 public String mapRow(ResultSet rs, int rowNum) throws SQLException {
  return rs.getString(1);
 }

}

the sql statements (junk data)

create table table_one (
 column_a varchar(50)
);

create table table_two (
 column_a varchar(50)
);

--table one
insert into table_one (column_a) values ('hello');
insert into table_one (column_a) values ('world');

--table two
insert into table_two (column_a) values ('hey');

and the unit test

package de.incompleteco.spring;

import static org.junit.Assert.assertFalse;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.JobParametersBuilder;
import org.springframework.batch.core.explore.JobExplorer;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"classpath:/META-INF/spring/*-context.xml"})
public class SwitchSQLIntegrationTest {

 @Autowired
 private Job job;
 
 @Autowired
 private JobLauncher jobLauncher;
 
 @Autowired
 private JobExplorer jobExplorer;
 
 @Test
 public void test() throws Exception {
  //setup the parameters
  JobParameters parameters = new JobParametersBuilder().addLong("runtime",System.currentTimeMillis())
    .addString("sqlKey", "sql1").toJobParameters();
  //run
  JobExecution execution = jobLauncher.run(job,parameters);
  //test
  while (jobExplorer.getJobExecution(execution.getId()).isRunning()) {
   Thread.sleep(100);
  }//end while
  //load
  execution = jobExplorer.getJobExecution(execution.getId());
  //test
  assertFalse(execution.getStatus().isUnsuccessful());
  //run it again
  parameters = new JobParametersBuilder().addLong("runtime",System.currentTimeMillis())
    .addString("sqlKey", "sql2").toJobParameters();
  //run
  execution = jobLauncher.run(job,parameters);
  //test
  while (jobExplorer.getJobExecution(execution.getId()).isRunning()) {
   Thread.sleep(100);
  }//end while
  //load
  execution = jobExplorer.getJobExecution(execution.getId());
  //test
  assertFalse(execution.getStatus().isUnsuccessful());  
 }
 
}