@Query support Slice and Page

[guodage]

I know PartTreeJdbcQuery support Slice/Page since https://github.com/spring-projects/spring-data-relational/pull/952, but I need use @Query for customized sql and Pageable in parameter when my scenes of query is complicated.

like this:

@Query("a long sql")
Page<Long> findPage(Pageable pageable);

I had forked from 3.0.12 and write some code to implement this in StringBasedJdbcQuery. see https://github.com/8btc-OnePiece/spring-data-jdbc/commit/32b231f40d0bf78dea63cf2cfa806a96d8ef36d4

I'm looking forward to any feedback, and confirm is there any guys need it or PR.

[mp911de]

Pagination support for string-based queries requires either a provided count-query or would require SQL parsing. Either one isn't great, especially for more complex queries.

We aren't exactly keen for having a parser to rewrite queries so most likely, we would require a user-provided count-query.

[guodage]

My 100% page sqls are working well by rewrite count queries. Are there scenarios that can't be solved by this?

[schauder]

From a quick look at your proposal I see problems with:

• Nested selects especially with WITH clause.
• Selects that contain String expressions that look like parts ofSQL statements
• Same for column names or aliases
• Selects that use GROUP BY
• Different dialects requiring different variants of LIMIT clauses
• Analytic functions utilizing ORDER BY
• Statements already containing a LIMIT clause.

Again that are just the problems I found with a quick glance. Painful experience with Spring Data JPA demonstrates that it is difficult to parse SQL sufficiently to do the required substitutions.

I wonder if we can come up with a way where the user provides the correct spots to inject the count and limit clauses.

[mp911de]

For the time being, would we be able to wrap the query with an outer query like SELECT * FROM (<the inner select>) LIMIT … OFFSET … and require a provided count query?