Description
In commit 220de61, the method generateJumpToItemQuery() was removed from PagingQueryProvider class, with the following message: "Remove un-used code for jump-to-item queries". Well, currently, Spring Cloud Data Flow invokes that method to generate the query for a specific page in the database, so they will be impacted when they upgrade to Spring Batch 5.
Anyway, what is now the expected usage to generate the query for remaining pages? I see no easy way to access to a random page (e.g. page 7). Should I call generateRemainingPagesQuery() repeteadly in streaming fashion? Note also that the Javadoc does not make that clear. In fact, the documentation for generateFirstPageQuery and generateRemainingPagesQuery is currently the same...
In Oracle, for example, the current implementation (OraclePagingQueryProvider) is based on legacy ROWNUM syntax, which is deprecated since Oracle 12c. More easily, OFFSET and FETCH clauses could be used (ANSI SQL 2016 compliant), and have a single method in PagingQueryProvider() to generate the query for the specific page. A similar solution could be applied in other databases too.
public class Oracle19PagingQueryProvider extends PagingQueryProvider {
public String generatePageQuery(int itemIndex, int pageSize) {
StringBuilder sql = new StringBuilder();
sql.append("SELECT ").append(this.getSelectClause());
sql.append(" FROM ").append(this.getFromClause());
sql.append(this.getWhereClause() == null ? "" : " WHERE " + this.getWhereClause());
buildGroupByClause(sql);
sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this));
sql.append(" OFFSET ").append(itemIndex);
sql.append(" ROWS FETCH NEXT ").append(pageSize).append(" ROWS ONLY");
return sql.toString();
}
private void buildGroupByClause(StringBuilder sql) {
if(StringUtils.hasText(getGroupClause())) {
sql.append(" GROUP BY ");
sql.append(getGroupClause());
}
}
}