One ItemReader, 2 SQL Query, jdbcTemplate?

Question

I have a requirement where I am reading from the database in two different Query. Each Query has its own SQL. The SQLs are similar and are going after the same set of tables for the most part, with minor differences. I wanted to check if I can have two SQLs in an ItemReader or maybe using a jdbctemplate is possible?

Any ideas, sample code?

Answer 1

in the event that you want to 're-use' an existing JdbcCursorItemReader (or one of the other Spring Batch Jdbc*ItemReaders), you can switch the SQL dynamically by leveraging the step scope. Below is an example of a configuration that switches the SQL based on the sqlKey property from the JobParameters. the source of the statements is a simple map.

<?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:batch="http://www.springframework.org/schema/batch"
    xmlns:util="http://www.springframework.org/schema/util"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd
        http://www.springframework.org/schema/batch http://www.springframework.org/schema/batch/spring-batch.xsd">

    <batch:job id="switchSQL">
        <batch:step id="switchSQL.step1">
            <batch:tasklet>
                <batch:chunk reader="sqlItemReader" writer="outItemWriter" commit-interval="10"/>
            </batch:tasklet>
        </batch:step>
    </batch:job>

    <bean id="sqlItemReader"
        class="org.springframework.batch.item.database.JdbcCursorItemReader" scope="step">
        <property name="dataSource" ref="dataSource"/>
        <property name="sql" value="#{@sqlStatements[jobParameters['sqlKey']]}"/>
        <property name="rowMapper">
            <bean class="de.incompleteco.spring.batch.data.ColumnRowMapper"/>
        </property>
    </bean>

    <util:map id="sqlStatements">
        <entry key="sql1" value="select * from table_one"/>
        <entry key="sql2" value="select * from table_two"/>
    </util:map>

    <bean id="outItemWriter"
        class="org.springframework.batch.item.adapter.ItemWriterAdapter">
        <property name="targetObject" ref="outWriter"/>
        <property name="targetMethod" value="write"/>
    </bean>

    <bean id="outWriter" class="de.incompleteco.spring.batch.item.SystemOutItemWriter"/>
</beans>

here are the supporting classes;

(a simple itemwriter)

package de.incompleteco.spring.batch.item;

public class SystemOutItemWriter {

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

}

(and a simple 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<String> {

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

}

and here's the remaining config

<?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">


    <bean id="jobRepository"
        class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean"/>

    <bean id="jobExplorer"
        class="org.springframework.batch.core.explore.support.MapJobExplorerFactoryBean">
        <property name="repositoryFactory" ref="&amp;jobRepository"/>
    </bean>

    <bean id="jobLauncher"
        class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
        <property name="jobRepository" ref="jobRepository"/>
    </bean>


    <bean id="transactionManager"
        class="org.springframework.batch.support.transaction.ResourcelessTransactionManager">
    </bean>

    <bean class="org.springframework.batch.core.scope.StepScope"/>
</beans>

and

<?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:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">


    <jdbc:embedded-database id="dataSource" type="H2">
        <jdbc:script location="classpath:/META-INF/sql/schema-h2.sql"/>
        <jdbc:script location="classpath:/META-INF/sql/insert-h2.sql"/>
    </jdbc:embedded-database>

    <bean id="dataSourceTransactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

and the sql stuff

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');

now finally a 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());        
    }

}