JdbcCursorItemReader Spring Batch Example

By
Rakesh
-

In this tutorial, we will see JdbcCursorItemReader Spring Batch Example. We will read data from the database and store it in the excel file in some directory on the local computer. We are going to use the MySql database.

The JdbcCursorItemReader class provides configuration for batch processes. We can define the maximum number of rows to retrieve through the max rows property and we can also provide a fetch size property that allows retrieving data in fixed-sized groups.

Consider we have some records in the MySql database.

JdbcCursorItemReader Spring Batch Example

We are going to see the JdbcCursorItemReader Spring Batch Example that reads the data from the database and writes it to excel. We will use JdbcCursorItemReader to read the data and FlatFileItemWriter to write the data to excel.

JdbcCursorItemReader Spring Batch Example

Configuring JdbcCursorItemReader

Annotation-based configuration


    @Bean
    public JdbcCursorItemReader<Student> reader() {
        JdbcCursorItemReader<Student> reader = new JdbcCursorItemReader<>();
        reader.setDataSource(dataSource);
        reader.setSql("select id, roll_number, name from student");
        reader.setRowMapper(new StudentResultRowMapper());
        reader.setMaxRows(10);
        reader.setFetchSize(10);
        reader.setQueryTimeout(10000);
        return reader;
    }

XML based configuration

<bean id="studentItemReader"
class="org.springframework.batch.item.database.JdbcCursorItemReader">
<property name="dataSource" ref="datasource"/>
<property name="sql"
value="select id, roll_number, name, price from student
where name like ?"/>
<property name="rowMapper" ref="studentRowMapper"/>
//some more configuration field.
</bean>

Let’s see Spring Batch JdbcCursorItemReader Example from scratch.

Maven dependency

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.javatute.com</groupId>
    <artifactId>springbatchjdbccursoritemreader</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.1.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.batch</groupId>
            <artifactId>spring-batch-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-batch</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

</project>

Directory structure

spring batch jdbccursoritemreader example

Define Student.java 

package com.springbatchexample.entity;

public class Student {

    private Long id;
    private String name;
    private String rollNumber;

    public Student() {

    }
    public Student(Long id, String name, String rollNumber) {
        this.id = id;
        this.name = name;
        this.rollNumber = rollNumber;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getRollNumber() {
        return rollNumber;
    }

    public void setRollNumber(String rollNumber) {
        this.rollNumber = rollNumber;
    }

}

Define StudentResultRowMapper class

package com.springbatchexample.component;

import com.springbatchexample.entity.Student;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

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

@Component
public class StudentResultRowMapper implements RowMapper<Student> {
    @Override
    public Student mapRow(ResultSet rs, int i) throws SQLException {
        Student student = new Student();
        student.setId(rs.getLong("id"));
        student.setRollNumber(rs.getString("roll_number"));
        student.setName(rs.getString("name"));
        return student;
    }
}

The mapRow() method is a factory method that creates a Student object based on a given JDBC ResultSet and row number. The JdbcCursorItemReader class contains setRowMapper() method that accepts RowMapper.

reader.setRowMapper(new StudentResultRowMapper())

Define StudentItemProcessor

import com.springbatchexample.entity.Student;
import org.springframework.batch.item.ItemProcessor;

public class StudentItemProcessor implements ItemProcessor<Student, Student> {

    @Override
    public Student process(Student student) throws Exception {
        return student;
    }
}

The StudentItemProcessor can be used to perform any transformations or validation that we need on an item(i.e student) before Spring Batch sends it to the ItemWriter. we are not performing transformations or validation for item.

Define SpringBatchConfig file

package com.springbatchexample.config;

import com.springbatchexample.component.StudentItemProcessor;
import com.springbatchexample.component.StudentResultRowMapper;
import com.springbatchexample.entity.Student;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.job.builder.FlowJobBuilder;
import org.springframework.batch.core.job.builder.JobBuilder;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.core.step.builder.SimpleStepBuilder;
import org.springframework.batch.core.step.builder.StepBuilder;
import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.batch.item.file.FlatFileItemWriter;
import org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor;
import org.springframework.batch.item.file.transform.DelimitedLineAggregator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.FileSystemResource;

import javax.sql.DataSource;

@EnableBatchProcessing
@Configuration
public class SpringBatchConfig {

    @Autowired
    private DataSource dataSource;

    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Bean
    public JdbcCursorItemReader<Student> reader() {
        JdbcCursorItemReader<Student> reader = new JdbcCursorItemReader<>();
        reader.setDataSource(dataSource);
        reader.setSql("select id, roll_number, name from student");
        reader.setRowMapper(new StudentResultRowMapper());
        return reader;
    }

    @Bean
    public FlatFileItemWriter<Student> writer() {
        FlatFileItemWriter<Student> writer = new FlatFileItemWriter<>();
        writer.setResource(new FileSystemResource("C://data/batch/data.csv"));
        writer.setLineAggregator(getDelimitedLineAggregator());
        return writer;
    }

    private DelimitedLineAggregator<Student> getDelimitedLineAggregator() {
        BeanWrapperFieldExtractor<Student> beanWrapperFieldExtractor = new BeanWrapperFieldExtractor<Student>();
        beanWrapperFieldExtractor.setNames(new String[]{"id", "rollNumber", "name"});

        DelimitedLineAggregator<Student> aggregator = new DelimitedLineAggregator<Student>();
        aggregator.setDelimiter(",");
        aggregator.setFieldExtractor(beanWrapperFieldExtractor);
        return aggregator;

    }

    @Bean
    public Step getDbToCsvStep() {
        StepBuilder stepBuilder = stepBuilderFactory.get("getDbToCsvStep");
        SimpleStepBuilder<Student, Student> simpleStepBuilder = stepBuilder.chunk(1);
        return simpleStepBuilder.reader(reader()).processor(processor()).writer(writer()).build();
    }

    @Bean
    public Job dbToCsvJob() {
        JobBuilder jobBuilder = jobBuilderFactory.get("dbToCsvJob");
        jobBuilder.incrementer(new RunIdIncrementer());
        FlowJobBuilder flowJobBuilder = jobBuilder.flow(getDbToCsvStep()).end();
        Job job = flowJobBuilder.build();
        return job;
    }

    @Bean
    public StudentItemProcessor processor() {
        return new StudentItemProcessor();
    }
}

This is the main configuration file. Let’s see how to define a configuration file

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/springbootcrudexample
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
server.port = 9091
spring.batch.initialize-schema=always

Define SpringMain class

package com.springbatchexample.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages = "com.springbatchexample.*")
public class SpringMain {
    public static void main(String[] args) {
        SpringApplication.run(SpringMain.class, args);
    }
}

Once we run the application we should be able to see data in excel.

Let’s see some important properties of JdbcCursorItemReader its uses.

The JdbcCursorItemReader class has the below properties that can be used to read data more efficiently.

  • datasource – The datasource used to access database.
reader.setDataSource(dataSource);
  • fetchSize – The number of rows to fetch to transparently retrieve data by group. The default value is -1.
reader.setFetchSize(10);
  • maxRows – The maximum number of rows that can be retrieved from SQL SELECT statements. The default value is -1.
reader.setMaxRows(10);
  • queryTimeout – The maximum amount of time to wait for a response.
reader.setQueryTimeout(10000);
  • preparedStatementSetter – The PreparedStatementSetter to set parameters for SQL statements.
reader.setPreparedStatementSetter();
  • rowMapper – This property we already used. The RowMapper instance to build objects from ResultSet.
reader.setRowMapper(new StudentResultRowMapper());
  • sql – The SQL SELECT to execute to get data.
reader.setSql("select id, roll_number, name from student");

That’s all about JdbcCursorItemReader Spring Batch Example.

See docs for more details.

Download code from GitHub.

See other Spring Batch tutorials.

Spring Data JPA examples

Hibernate example using spring boot.

RELATED ARTICLESMORE FROM AUTHOR