Spring Batch Paging with sortKeys and parameter values

Question

I have a Spring Batch project running in Spring Boot that is working perfectly fine. For my reader I'm using JdbcPagingItemReader with a MySqlPagingQueryProvider.

@Bean
public ItemReader<Person> reader(DataSource dataSource) {
    MySqlPagingQueryProvider provider = new MySqlPagingQueryProvider()
    provider.setSelectClause(ScoringConstants.SCORING_SELECT_STATEMENT)
    provider.setFromClause(ScoringConstants.SCORING_FROM_CLAUSE)
    provider.setSortKeys("p.id": Order.ASCENDING)

    JdbcPagingItemReader<Person> reader = new JdbcPagingItemReader<Person>()
    reader.setRowMapper(new PersonRowMapper())
    reader.setDataSource(dataSource)
    reader.setQueryProvider(provider)
    //Setting these caused the exception
    reader.setParameterValues(
        startDate: new Date() - 31,
        endDate: new Date()
    ) 
    reader.afterPropertiesSet()
    return reader
}

However, when I modified my query with some named parameters to replace previously hard coded date values and set these parameter values on the reader as shown above, I get the following exception on the second page read (the first page works fine because the _id parameter hasn't been made use of by the paging query provider):

org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter '_id': No value registered for key '_id'
at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(NamedParameterUtils.java:336)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:374)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:199)
at org.springframework.batch.item.database.JdbcPagingItemReader.doReadPage(JdbcPagingItemReader.java:218)
at org.springframework.batch.item.database.AbstractPagingItemReader.doRead(AbstractPagingItemReader.java:108)

Here is an example of the SQL, which has no WHERE clause by default. One does get created automatically when the second page is read:

select *, (select id from family f where date_created between :startDate and :endDate and f.creator_id = p.id)  from person p

On the second page, the sql is modified to the following, however it seems that the named parameter for _id didn't get supplied:

select *, (select id from family f where date_created between :startDate and :endDate and f.creator_id = p.id) from person p WHERE id > :_id

I'm wondering if I simply can't use the MySqlPagingQueryProvider sort keys together with additional named parameters set in JdbcPagingItemReader. If not, what is the best alternative to solving this problem? I need to be able to supply parameters to the query and also page it (vs. using the cursor). Thank you!

You don't seem to be setting the where clause for your new values. Is that just missing from your configuration example? — Michael Minella
@MichaelMinella Thank you for your help. I provided some SQL above. There is no WHERE clause in my initial query, however the paging provider automatically adds one after the first page. — th3morg
I meant that when you configure your provider, you aren't setting the where clause for the startDate and endDate, or am I missing something? — Michael Minella
@MichaelMinella, note that those are not used in the where clause, but rather in a subselect. Do you think for some reason that that would cause the issue I am having with the _id field? I realize I could change that to a join instead of a subselect and use a where clause, but I have many additional sub selects that can't be made into joins that I've removed for the sake of trying to reduce confusion. The query is tested and works. Like I said, page 1 functions fine and it isn't until page 2 where the WHERE clause automatically gets added, that the named _id param isn't bound. Thank you again! — th3morg
Looking at the source and how the getParameterMap function works it certainly seems to cover it. Any chance you can turn on the logging and see what is output for "Using parameterMap"? — David Coutu

th3morg th3morg · Accepted Answer · 2014-11-21T19:29:12

I solved this problem with some intense debugging. It turns out that MySqlPagingQueryProvider utilizes a method getSortKeysWithoutAliases() when it builds up the SQL query to run for the first page and for subsequent pages. It therefore appends and (p.id > :_id) instead of and (p.id > :_p.id). Later on, when the second page sort values are created and stored in JdbcPagingItemReader's startAfterValues field it will use the original "p.id" String specified and eventually put into the named parameter map the pair ("_p.id",10). However, when the reader tries to fill in _id in the query, it doesn't exist because the reader used the non-alias removed key.

Long story short, I had to remove the alias reference when defining my sort keys.

provider.setSortKeys("p.id": Order.ASCENDING)

had to change to in order for everything to work nicely together

provider.setSortKeys("id": Order.ASCENDING)

Spring Batch Paging with sortKeys and parameter values

2 Answers