Spring batch pagination with JdbcPagingItemReader

Shradha Yewale
Spring boot framework by shradha yewale
3 min readNov 14, 2020

JdbcPagingItemReader retrieves database records in a paging fashion.

What is pagination and how it helps

Pagination refers to the technique to fetch/read a huge amount of records from the database in a paging fashion.

While executing a query for a large dataset, the database needs many internal resources to finish the query. The database needs two steps to execute a query that involves more than one table. The database creates a Cartesian product with the data of the involved tables. The database selects only the rows, which satisfies the relation. The consequence is that the amount of database operations increases proportionally to the growth of data.

It is, therefore, necessary to split the results of a query into subsets to achieve constant response time. During the splitting process, it is very important to keep paging transitions fluently. That means rows can’t appear twice or miss completely in the result. For this purpose, pagination plays an important role.

In this post, we will understand how to read the input data of the batch job by using JdbcPagingItemReader. It significantly reduces the result set fetching time.

Let’s have a look at the following example to read student records from the ‘STUDENTS’ table using pagination.

  • This is the model class Student.

Student.java


public class Student {
private String id;
private String name;
}
  • The mapper class will map each row of data in the ResultSet.

StudentMapper.java

public class StudentMapper implements RowMapper<Student>  {  @Override
public Student mapRow(final ResultSet rs, final int rowNum) {
Student student = new Student();
student.setId(rs.getString(“id”));
student.setName(rs.getString(“name”));
return student;
}
}
  • The main reader class

StudentDataReader.java

public class StudentDataReader {   @Autowired
private DataSource dataSource;
private static final String GET_STUDENT_INFO = “SELECT * from STUDENTS where id = :id and name = :name “;

public JdbcPagingItemReader<Student> getPaginationReader(Student student) {
final JdbcPagingItemReader<Student> reader = new JdbcPagingItemReader<>();
final StudentMapper studentMapper = new StudentMapper();
reader.setDataSource(dataSource);
reader.setFetchSize(100);
reader.setPageSize(100);
reader.setRowMapper(studentMapper);
reader.setQueryProvider(createQuery());
Map<String, Object> parameters = new HashMap<>();
parameters.put(“id”, student.getId());
parameters.put(“name”, student.getName());
reader.setParameterValues(parameters);
return reader;
}
private PostgresPagingQueryProvider createQuery() {
final Map<String, Order> sortKeys = new HashMap<>();
sortKeys.put(“id”, Order.ASCENDING);
final PostgresPagingQueryProvider queryProvider = new PostgresPagingQueryProvider();
queryProvider.setSelectClause(“*”);
queryProvider.setFromClause(getFromClause());
queryProvider.setSortKeys(sortKeys);
return queryProvider;
}
private String getFromClause() {
return “( “ + GET_STUDENT_INFO + “)” + “ AS RESULT_TABLE “;
}
}
  • PagingQueryProvider - It executes the SQL built by the PagingQueryProvider to retrieve requested data.
  • setPageSize(int) - The query is executed using paged requests of a size specified in setPageSize(int). The number of rows to retrieve at a time. pageSize is the number of rows to fetch per page.

In the above example, it will fetch 100 rows at a time.

  • setFetchSize(int) - Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for the ResultSet object. If the fetch size specified is zero, the JDBC driver ignores the value. It takes the number of rows to fetch.
  • setSortKeys(Map<String, Order> sortKeys) - sortkey to use to sort and limit page content. It takes a map of sort columns as the key and boolean for ascending/descending. On the restart, it uses the last sort key value to locate the first page to read (so it doesn’t matter if the successfully processed items have been removed or modified). It is important to have a unique key constraint on the sort key to guarantee that no data is lost between executions.

Additional pages are requested when needed as read () method is called, returning an object corresponding to the current position.

  • setSelectClause(String selectClause) - SELECT clause part of SQL query string.
  • setFromClause(String fromClause) - FROM clause part of SQL query string. In this example, our query will look like
SELECT * (SELECT * from STUDENTS where id = :id and name = :name) AS RESULT_TABLE

References :

  1. https://docs.spring.io/spring-batch/docs/current/api/org/springframework/batch/item/database/JdbcPagingItemReader.html

--

--

Shradha Yewale
Spring boot framework by shradha yewale

Software Development Engineer | API Development | Web Development| Java | Spring Boot | React JS