Open guodage opened 6 months ago
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.
My 100% page sqls are working well by rewrite count queries. Are there scenarios that can't be solved by this?
From a quick look at your proposal I see problems with:
WITH
clause. GROUP BY
LIMIT
clausesORDER BY
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.
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?
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:
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/32b231f40d0bf78dea63cf2cfa806a96d8ef36d4I'm looking forward to any feedback, and confirm is there any guys need it or PR.