Spring Batch: JdbcPagingItemReader pagination

Question

An issue came across when I was doing a simple batch that reads data from postgresql database, processes and writes in a csv file. The following is the reader:

 @Bean
@StepScope
public ItemReader<SearchResult> databaseReader(@Qualifier("reportingDataSource") HikariDataSource reportDataSource,
                                           @Value("#{stepExecution}") StepExecution stepExecution) {

    JdbcPagingItemReader<SearchResult> reader = new JdbcPagingItemReader<>();
    reader.setQueryProvider(createQueryProvider());
    reader.setRowMapper(new CustomRowMapper());
    reader.setDataSource(reportDataSource);
    reader.setPageSize(5);
    reader.open(stepExecution.getExecutionContext());
    return reader;
}

private PagingQueryProvider createQueryProvider(SearchTxnRequest searchTxnRequest) {
    SqlitePagingQueryProvider queryProvider = new SqlitePagingQueryProvider();
    queryProvider.setSelectClause("SELECT *");
    queryProvider.setFromClause("from dummy_table");
    queryProvider.setSortKeys(sortBy());
    return queryProvider;
}

private Map<String, Order> sortBy() {
    Map<String, Order> sortConfiguration = new HashMap<>();
    sortConfiguration.put("date", Order.ASCENDING);
    return sortConfiguration;
}

I inserted dummy data to the 'dummy_table' with different ID but same data in the other fields, including the date field. As you can see in the code, the sortBy function define the way is sorting the information in the table, I picked only the date field. The issue appear here, if all the rows in the table have the same date, the batch only return the first 5 rows (If the page size is 5).

Why? well, JdbcPagingItemReader paginates with the sort keys, in this case only the date. The debugging shows the following: So the first page is read

2020-03-30 15:01:40 DEBUG [nio-7035-exec-1] o.s.b.i.database.JdbcPagingItemReader    : Reading page 0
2020-03-30 15:01:40 DEBUG [nio-7035-exec-1] o.s.b.i.database.JdbcPagingItemReader    : SQL used for reading first page: [SELECT * FROM from dummy_table ORDER BY date ASC LIMIT 5]

The second page:

2020-03-30 15:01:40 [nio-7035-exec-1] o.s.b.i.database.JdbcPagingItemReader    : Reading page 1
2020-03-30 15:01:40 [nio-7035-exec-1] o.s.b.i.database.JdbcPagingItemReader    : SQL used for reading remaining pages: [SELECT * FROM from dummy_table WHERE ((date > ?)) ORDER BY date ASC LIMIT 5]

As you can see the pagination is made by the date sort key. My problem was that all the data in the table had the same date. It's important to choose wisely the sort key in order to avoid issues in the pagination. I added the ID, and that solve the issue.

Mahmoud Ben Hassine Mahmoud Ben Hassine · Accepted Answer · 2020-03-30T19:51:26

You don't need to call reader.open in your databaseReader method, Spring Batch will do it if your reader is declared as an ItemStreamReader. I recommend returning the most specific type in reader/writer bean definitions so that Spring Batch can correctly create proxies. In your case, it should be something like:

@Bean
@StepScope
public JdbcPagingItemReader<SearchResult> databaseReader(@Qualifier("reportingDataSource") HikariDataSource reportDataSource) {

   JdbcPagingItemReader<SearchResult> reader = new JdbcPagingItemReader<>();
   reader.setQueryProvider(createQueryProvider());
   reader.setRowMapper(new CustomRowMapper());
   reader.setDataSource(reportDataSource);
   reader.setPageSize(5);
   return reader;
}

It's important to choose wisely the sort key in order to avoid issues in the pagination. I added the ID, and that solve the issue.

Yes, as documented, here is an excerpt from the Javadocs of JdbcPagingItemReader and AbstractSqlPagingQueryProvider:

It is important to have a unique key constraint on the sort key

The columns that make up the sort key must be a true key and not just a column to order by

I believe your date column is not a key, that's why when you add the ID, it works.

Spring Batch: JdbcPagingItemReader pagination

1 Answers