JdbcPagingItemReader: PagingQueryProvider add a parameter value after the second iteration

Question

i created a class that extends JdbcPagingItemReader. Inside i have this configuration.

    @PostConstruct
    public void init() throws Exception {
        setDataSource(dsSrv.getUserDataSource());
        setPageSize(Integer.parseInt(pageSize));
        setFetchSize(Integer.parseInt(pageSize));
        setQueryProvider(queryProvider2(dsSrv.getUserDataSource()));
        setRowMapper(rowMapper());

    }

private PagingQueryProvider queryProvider2(DataSource ds) throws Exception {
    SqlPagingQueryProviderFactoryBean queryProvider=new SqlPagingQueryProviderFactoryBean();
    final Map<String, Order> sortKeys = new HashMap<>();
    sortKeys.put("ID", Order.ASCENDING);

    queryProvider.setDataSource(ds);
    queryProvider.setSelectClause("*");
    queryProvider.setFromClause("D_TABLE");
    queryProvider.setWhereClause("ID = '5d7031e6-10f2-4247-b581-17b2fe96449a'");
    queryProvider.setSortKeys(sortKeys);
    return queryProvider.getObject();
  }

The issue is that with the first iteration the query is assembled as expected:SELECT TOP 10 * FROM D_TABLE WHERE (ID= '5d7031e6-10f2-4247-b581-17b2fe96449a')

after the second iteration i have this query: SELECT TOP 10 * FROM D_TABLE WHERE (ID = '5d7031e6-10f2-4247-b581-17b2fe96449a') AND ((ID > ?)) ORDER BY ID ASC

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT TOP 10 * FROM D_TABLE WHERE (ID = '5d7031e6-10f2-4247-b581-17b2fe96449a') AND ((ID > ?)) ORDER BY ID ASC]; SQL state [null]; error code [0]; The value is not set for the parameter number 1.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1.

How can i avoid this?

Thank you

Answer 1

@PostConstruct
public void init() throws Exception {
    setDataSource(dsSrv.getUserDataSource());
    setPageSize(Integer.parseInt(pageSize));
    setFetchSize(Integer.parseInt(pageSize));
    setQueryProvider(queryProvider(dsSrv.getUserDataSource()));
    setRowMapper(rowMapper());
try {
  afterPropertiesSet();
} catch (Exception e) {
  e.printStackTrace();
}
setSaveState(false);
}

For async steps is mandatory to set setSaveState(false) otherwise you can face issues like that