@Query support Slice and Page

[guodage]

I know PartTreeJdbcQuery support Slice/Page since #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 8btc-OnePiece@32b231f

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?