title: Spring Batch Paging with sortKeys and parameter values
body: <p>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. </p>
  
  <pre><code>@Bean
  public ItemReader&lt;Person&gt; 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&lt;Person&gt; reader = new JdbcPagingItemReader&lt;Person&gt;()
      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
  }
  </code></pre>
  
  <p>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 <strong>second</strong> page read (the first page works fine because the _id parameter hasn't been made use of by the paging query provider):</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>select *, (select id from family f where date_created between :startDate and :endDate and f.creator_id = p.id)  from person p
  </code></pre>
  
  <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:</p>
  
  <pre><code>select *, (select id from family f where date_created between :startDate and :endDate and f.creator_id = p.id) from person p WHERE id &gt; :_id
  </code></pre>
  
  <p>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!</p>