Spring Batch — Pass Arguments to Query

In this example, will see how to pass the arguments to SQL query. JdbcCursorItemReader internally uses the PreparedStatementSetter to set the values, hence passing the list is not directly supported, you need to put custom implementation.

In this example, we’ll read data from mysql db and write it into CSV file.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>xml-spring-batch-csv-to-xml</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spring-batch-composite-item-writer</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-batch</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-oxm</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.github.javafaker</groupId>
            <artifactId>javafaker</artifactId>
            <version>1.0.2</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.batch</groupId>
            <artifactId>spring-batch-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Model —

@AllArgsConstructor(staticName = "create")
@NoArgsConstructor
@Data
@Builder
public class Employee {
    private Long empId;
    private String firstName;
    private String lastName;
    private Integer age;
    private String email;

    @Override
    public String toString() {
        return empId + "," + firstName + "," + lastName + "," + age + "," + email;
    }
}

EmployeeMapper.java — An interface used by JdbcTemplate for mapping rows of a java.sql.ResultSet on a per-row basis. Implementations of this interface perform the actual work of mapping each row to a result object, but don't need to worry about exception handling.SQLExceptions will be caught and handled by the calling JdbcTemplate.

package com.example.mapper;

import com.example.model.Employee;
import org.springframework.jdbc.core.RowMapper;

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

public class EmployeeMapper implements RowMapper<Employee> {

    @Override
    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
        Employee result = new Employee();
        result.setEmpId(rs.getLong("empId"));
        result.setFirstName(rs.getString("firstName"));
        result.setLastName(rs.getString("lastName"));
        result.setAge(rs.getInt("age"));
        result.setEmail(rs.getString("email"));
        return result;
    }
}

MainApp.java

package com.example;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.JobExecutionException;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class MainApp implements CommandLineRunner {

    public static void main(String[] args) {
        SpringApplication.run(MainApp.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        ApplicationContext context = new ClassPathXmlApplicationContext("spring-batch-context.xml");

        JobLauncher jobLauncher = (JobLauncher) context.getBean("jobLauncher");
        Job job = (Job) context.getBean("employeeJob");

        try {
            JobExecution execution = jobLauncher.run(job, new JobParameters());
            System.out.println("Job Exit Status : "+ execution.getStatus());
        } catch (JobExecutionException e) {
            System.out.println("Job ExamResult failed");
            e.printStackTrace();
        }
    }
}

application.properties

sql.age=22
sql.firstName=John

logging.level.org.springframework.jdbc.core.JdbcTemplate=debug 

db.sql

CREATE TABLE `emp`.`employee` (
  `empId` INT NOT NULL,
  `firstName` VARCHAR(45) NULL,
  `lastName` VARCHAR(45) NULL,
  `age` INT NULL,
  `email` VARCHAR(45) NULL,
  PRIMARY KEY (`empId`));

  INSERT INTO `emp`.`employee` (`empId`, `firstName`, `lastName`, `age`, `email`) VALUES ('1', 'John', 'Doe', '22', 'john.doe@gmail.com');
  INSERT INTO `emp`.`employee` (`empId`, `firstName`, `lastName`, `age`, `email`) VALUES ('2', 'Jane', 'Doe', '33', 'jane.doe@gmail.com');
  INSERT INTO `emp`.`employee` (`empId`, `firstName`, `lastName`, `age`, `email`) VALUES ('3', 'Mike', 'Doe', '44', 'mike.doe@gmail.com');
  INSERT INTO `emp`.`employee` (`empId`, `firstName`, `lastName`, `age`, `email`) VALUES ('4', 'Harshita', 'Dekate', '55', 'harshita.dekate@gmail.com');

xml

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:batch="http://www.springframework.org/schema/batch"
       xmlns:task="http://www.springframework.org/schema/task"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/batch
                         http://www.springframework.org/schema/batch/spring-batch-2.2.xsd
                         http://www.springframework.org/schema/beans
                         http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
                         http://www.springframework.org/schema/util
                         http://www.springframework.org/schema/util/spring-util-3.2.xsd">

    <!-- https://stackoverflow.com/questions/5598217/how-do-i-read-jvm-arguments-in-the-spring-applicationcontext-xml -->
    <bean id="applicationProperties" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="ignoreResourceNotFound" value="false"/>
        <property name="ignoreUnresolvablePlaceholders" value="true"/>
        <property name="searchSystemEnvironment" value="false"/>
        <property name="locations">
            <list>
                <value>classpath:application.properties</value>
            </list>
        </property>
    </bean>

    <!-- JobRepository and JobLauncher are configuration/setup classes -->
    <bean id="jobRepository" class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean"/>

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

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

    <batch:job id="employeeJob">
        <batch:step id="step1">
            <batch:tasklet>
                <batch:chunk reader="employeeItemReader"
                             writer="cvsFileItemWriter"
                             commit-interval="1">
                </batch:chunk>
            </batch:tasklet>
        </batch:step>
    </batch:job>

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test"/>
        <property name="username" value="root"/>
        <property name="password" value="Password"/>
    </bean>

    <bean id="employeeSetter" class="org.springframework.jdbc.core.ArgumentPreparedStatementSetter">
        <constructor-arg>
            <array>
                <value>${sql.age}</value>
                <value>${sql.firstName}</value>
            </array>
        </constructor-arg>
    </bean>

    <bean id="employeeItemReader" class="org.springframework.batch.item.database.JdbcCursorItemReader">
        <property name="dataSource" ref="dataSource"/>
        <property name="sql" value="SELECT * FROM emp.employee where age = ? and firstName = ?"/>
        <property name="rowMapper">
            <bean class="com.example.mapper.EmployeeMapper"/>
        </property>
        <property name="preparedStatementSetter" ref="employeeSetter"/>
    </bean>

    <bean id="cvsFileItemWriter" class="org.springframework.batch.item.file.FlatFileItemWriter">
        <property name="resource" value="file:cvs/employee.csv"/>
        <property name="shouldDeleteIfExists" value="true"/>

        <property name="lineAggregator">
            <bean class="org.springframework.batch.item.file.transform.DelimitedLineAggregator">
                <property name="delimiter" value=","/>
                <property name="fieldExtractor">
                    <bean class="org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor">
                        <property name="names" value="empId, firstName, lastName, age, email"/>
                    </bean>
                </property>
            </bean>
        </property>
    </bean>
</beans>

Reference —

how to replace the code which has deprecated classes