JpaPagingItemReader Example

In this tutorial, we will see Spring Batch JpaPagingItemReader Example. We will read data from the database and store it in the excel file in some directory.

Using JpaPagingItemReader we can write JPQL to reads records from Database. The JpaPagingItemReader uses JpaNativeQueryProvider to define the query. The JpaNativeQueryProvider different methods to create SQL statements. We are going to use the MySql database

Consider that we have some records in the database.

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

Configuring JpaPagingItemReader

Annotation-based configuration

    @Bean
    public JpaPagingItemReader<Student> getJpaPagingItemReader()  {
        String sql = "select * from student where id >= :limit";
        JpaNativeQueryProvider<Student> queryProvider = new JpaNativeQueryProvider<Student>();
        JpaPagingItemReader<Student> reader = new JpaPagingItemReader<>();
        queryProvider.setSqlQuery(sql);
        reader.setQueryProvider(queryProvider);
        queryProvider.setEntityClass(Student.class);
        reader.setParameterValues(Collections.singletonMap("limit", 10));
        reader.setEntityManagerFactory(entityManagerFactory);
        reader.setPageSize(3);
        reader.setSaveState(true);
        return reader;
    }

Note –  JPA does not have a concept similar to the Hibernate StatelessSession. Also, The JpaPagingItemReader must be configured with an EntityManagerFactory. To configure EntityMangerFactory JpaPagingIteReader uses setEntitytManagerFactory().

We can get EntityMangerFactory reference using @Autowired annotation.

    @Autowired
    private EntityManagerFactory entityManagerFactory;

Configure JpaPagingItemReader using JpaPagingItemReaderBuilder.

    @Bean
    public JpaPagingItemReader getJpaPagingItemReader() {
        return new JpaPagingItemReaderBuilder<Student>()
                .name("Student")
                .entityManagerFactory(entityManagerFactory)
                .queryString("select s from Student s")
                .pageSize(1000)
                .build();
    }

Let’s see Spring Batch JpaPagingItemReader Example from scratch

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

Insert some records for testing purposes.

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>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</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;

import javax.persistence.*;
import java.io.Serializable;

@Entity
public class Student implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "roll_number")
    private String 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.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.JpaPagingItemReader;
import org.springframework.batch.item.database.builder.JpaPagingItemReaderBuilder;
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.persistence.EntityManagerFactory;
import javax.sql.DataSource;

@EnableBatchProcessing
@Configuration
public class SpringBatchConfig {


    @Autowired
    private DataSource dataSource;

    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Autowired
    private EntityManagerFactory entityManagerFactory;

/*    @Bean
    public JpaPagingItemReader<Student> getJpaPagingItemReader()  {
        String sql = "select * from student where id >= :limit";
        JpaNativeQueryProvider<Student> queryProvider = new JpaNativeQueryProvider<Student>();
        JpaPagingItemReader<Student> reader = new JpaPagingItemReader<>();
        queryProvider.setSqlQuery(sql);
        reader.setQueryProvider(queryProvider);
        queryProvider.setEntityClass(Student.class);
        reader.setParameterValues(Collections.singletonMap("limit", 10));
        reader.setEntityManagerFactory(entityManagerFactory);
        reader.setPageSize(3);
        reader.setSaveState(true);
        return reader;
    }*/

    @Bean
    public JpaPagingItemReader getJpaPagingItemReader() {
        return new JpaPagingItemReaderBuilder<Student>()
                .name("Student")
                .entityManagerFactory(entityManagerFactory)
                .queryString("select s from Student s")
                .pageSize(1000)
                .build();
    }

    @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(getJpaPagingItemReader()).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=update
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.*")
@EntityScan("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 excel.

That’s all about the spring batch JpaPagingItemReader 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
• JdbcPagingItemReader spring batch 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.