Spring batch pagination with JdbcPagingItemReader

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