JdbcPagingItemReader spring batch example

In this tutorial, we will see Spring Batch JdbcPagingItemReader Example. We will read data from the database and store it in the excel file in some directory. The JdbcPagingItemReader is used to reads records in a paging fashion. The JdbcPagingItemReader uses PagingQueryProvider to define the query. The PagingQueryProvider different methods to create SQL statements. We are going to use the MySql database

Consider we have some records in the database.

In this Spring Bathc example, We will read data using JdbcPagingItemReader and also sort on basis of name and then write to excel.

Configuring JdbcPagingItemReader

Annotation-based configuration

    @Bean
    public JdbcPagingItemReader<Student> jdbcPagingItemReader() {
        JdbcPagingItemReader<Student> pagingItemReader = new JdbcPagingItemReader<>();

        pagingItemReader.setDataSource(dataSource);
        pagingItemReader.setFetchSize(20);
        pagingItemReader.setRowMapper(new StudentResultRowMapper());
        pagingItemReader.setPageSize(1);

        MySqlPagingQueryProvider mySqlPagingQueryProvider = new MySqlPagingQueryProvider();
        mySqlPagingQueryProvider.setSelectClause("id, roll_number, name");
        mySqlPagingQueryProvider.setFromClause("from student");

        Map<String, Order> orderByName = new HashMap<>();
        orderByName.put("name", Order.ASCENDING);

        mySqlPagingQueryProvider.setSortKeys(orderByName);
        pagingItemReader.setQueryProvider(mySqlPagingQueryProvider);

        return pagingItemReader;
    }

Note – The JdbcPagingItemReader uses different provider for different database. For example for MySql database we have MySqlPagingQueryProvider and for Oracle database JdbcPagingItemReader uses OraclePagingQueryProvider.

Similarly Spring Batch provides different types of PagingQueryProvider for different database. Let’s see some important PagingQueryProvider.

1. Db2PagingQueryProvider
2. DerbyPagingQueryProvider
3. H2PagingQueryProvider
4. HsqlPagingQueryProvider
5. PostgresPagingQueryProvider
6. SqlitePagingQueryProvider
7. SqlServerPagingQueryProvider
8. SqlWindowingPagingQueryProvider
9. SybasePagingQueryProvider

Let’s see Spring Batch JdbcPagingItemReader Example using Spring Boot and MySql.

Before going ahead create a student table.

CREATE TABLE student
(
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(260),
    roll_number VARCHAR(260)
);

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>springbatchxmlitemreader</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>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-oxm -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-oxm</artifactId>
        </dependency>

        <dependency>
            <groupId>com.thoughtworks.xstream</groupId>
            <artifactId>xstream</artifactId>
            <version>1.4.11.1</version>
        </dependency>

    </dependencies>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

</project>

package structure

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 StudentItemProcessor.java

package com.springbatchexample.config;

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 the 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.JdbcPagingItemReader;
import org.springframework.batch.item.database.Order;
import org.springframework.batch.item.database.support.MySqlPagingQueryProvider;
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;
import java.util.HashMap;
import java.util.Map;

@EnableBatchProcessing
@Configuration
public class SpringBatchConfig {


    @Autowired
    private DataSource dataSource;

    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Bean
    public JdbcPagingItemReader<Student> jdbcPagingItemReader() {
        JdbcPagingItemReader<Student> pagingItemReader = new JdbcPagingItemReader<>();

        pagingItemReader.setDataSource(dataSource);
        pagingItemReader.setFetchSize(20);
        pagingItemReader.setRowMapper(new StudentResultRowMapper());
        pagingItemReader.setPageSize(1);

        MySqlPagingQueryProvider mySqlPagingQueryProvider = new MySqlPagingQueryProvider();
        mySqlPagingQueryProvider.setSelectClause("id, roll_number, name");
        mySqlPagingQueryProvider.setFromClause("from student");

        Map<String, Order> orderByName = new HashMap<>();
        orderByName.put("name", Order.ASCENDING);

        mySqlPagingQueryProvider.setSortKeys(orderByName);
        pagingItemReader.setQueryProvider(mySqlPagingQueryProvider);

        return pagingItemReader;
    }

    @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(jdbcPagingItemReader()).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();
    }

}

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.boot.autoconfigure.domain.EntityScan;
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 you run the above example we should be able to see the below records in the excel.

The record has been in sorted order.

That’s all about the JdbcPagingItemReader spring batch example.

Download code from GitHub.

See docs

Other Spring Batch tutorial.

• Spring Batch FlatFileItemReader Example
• StoredProcedureItemReader example
• JdbcCursorItemReader Spring Batch Example
• JsonItemReader Spring Batch Example
• StaxEventItemReader Example

Spring Data JPA Example.

• Spring Data JPA greater than Example
• Spring Data JPA less than Example
• Spring Data JPA IsNull Example Using Spring Boot
• Spring Data findById() Vs getOne()
• Spring Data JPA CrudRepository findById()
• Spring Data JPA JpaRepository getOne()
• Spring Data CrudRepository saveAll() and findAll().
• Spring Data CrudRepository existsById()
• Spring Data JPA delete() vs deleteInBatch()
• Spring Data JPA deleteAll() Vs deleteAllInBatch()
• Spring Data JPA JpaRepository deleteAllInBatch()
• Spring Data JPA deleteInBatch() Example
• Spring Data JPA JpaRepository saveAndFlush() Example
• Spring Data JPA CrudRepository count() Example
• Spring Data JPA CrudRepository delete() and deleteAll()
• Spring Data JPA CrudRepository deleteById() Example
• CrudRepository findAllById() Example Using Spring Boot
• Spring Data CrudRepository save() Method.
• Sorting in Spring Data JPA using Spring Boot.
• Spring Data JPA example using spring boot.
• Spring Data JPA and its benefit.