使用sortKeys和参数值进行Spring Batch Paging [英] Spring Batch Paging with sortKeys and parameter values

问题描述

我有一个在Spring Boot中运行的Spring Batch项目，它运行得非常好。对于我的读者，我正在使用JdbcPagingItemReader和MySqlPagingQueryProvider。

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
}

然而，当我使用一些命名参数修改我的查询时如上所示，替换以前的硬编码日期值并在阅读器上设置这些参数值，我在第二个页面读取时得到以下异常（第一页工作正常，因为_id参数尚未生成分页查询提供程序的使用）：

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)

以下是SQL的示例，默认情况下没有WHERE子句。在读取第二页时会自动创建一个：

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

在第二页上， sql被修改为以下，但似乎没有提供_id的命名参数：

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

我想知道我是否根本不能将MySqlPagingQueryProvider排序键与其他命名一起使用在JdbcPagingItemReader中设置的参数。如果没有，解决这个问题的最佳方法是什么？我需要能够为查询提供参数并对其进行分页（与使用光标相比）。谢谢！

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!

推荐答案

我通过一些激烈的调试解决了这个问题。事实证明，当MySqlPagingQueryProvider构建SQL查询以运行第一页和后续页时，它会使用方法 getSortKeysWithoutAliases（）。因此，它会附加和（p.id>：_id）而不是和（p.id>：_p.id）。稍后，当第二页排序值创建并存储在JdbcPagingItemReader的 startAfterValues 字段中时，它将使用原始p.id指定字符串并最终将命令参数映射到（_ p.id，10）对。但是，当读者尝试在查询中填写_id时，它不存在，因为读者使用了非别名删除键。

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）

这篇关于使用sortKeys和参数值进行Spring Batch Paging的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！