Add page support for Custom query @Query with different SQL future like join, etc...

[Aki325]

Hi,

I am working on a project with Spring DATA JDBC, I have different custom queries in my Repository such as:

@Query("SELECT test1, test2 FROM table where test1 = :test1 AND (:test2 IS NULL OR test2 =:test2)) " List<Test> findTest(String test1, String test2, Peagabe peagable)

@Query ("SELECT a.test1,a.test2 FROM tabe a inner join table2 b on a.test1 = b.test1 WHERE test1 = (SELECT test1 from table3 c where c.test5= :test)) List<Test> findOtherTest(String test, Pageable peagable)

or more complicted queries. P.S I wrote these queries without testing, I want just give the idea

There are two issues :

1. if I return a List from the method (List<Test> findTest(String test1, String test2, Peagabe peagable)) linked to the Query, even if I am passing an instance of Pageable to that method, it will return me all the records that it found. regardless of which size number I passed. (PageRequest pageable = PageRequest.of(0,1))

2. If I return a Page from the method (Page<Test> findTest(String test1, String test2, Peagabe peagable)) linked to the Query, It gives me org.springframework.dao.IncorrectResultSizeDataAccessEception : Incorrect result size: expected 1, actual 10

Would you please exlain me about these issues and if Spring is considering to add these futures in Spring Data JDBC? Thanks

[schauder]

Pagination is currently not supported and probably won't become available for quite some time.

I expect we might offer something based on SpEL eventually, so it might be worth following #619.

[Aki325]

Good morning Thank you for your concerns An idea about pagination for custom query :

I don't know the reason for having a SQL parser to make pagination but my idea is, if we wrappe the quey string by SELECT * FROM ( query string provided by @Query ) and then we apply mechansime of pagination on it it should work because we don't really need to parse the sql string query provided by @Query just we need to have the number of records and then check the page number and size is passed by Pageable object