Is it possible to use native query + spring date for paging?

Asked

Viewed 923 times

4

I need to make a pagination using dynamic querys because I have to do many joins, I’m trying to make only one select * to test the use of native query + spring data, but the error message is as follows:

Cannot use native queries with dynamic sorting and/or pagination in method public abstract

Follows the code:

@Query(value = "select i.* " + "from VRS.TB_CLIE_INVS i ",nativeQuery = true)
Page<TbClieInv> testePaginacaoQuery(Pageable pageable);
query oracle paging

  • Oracle database, my application is Restfull using spring boot, on our back, and front is Angular, I’m using Jparepository, when I use findAll from Jparepository works, but when I try to use with @query it doesn’t go up the project at all, it is not possible that I can only paginate with the predefined methods of Jparepository, I have huge Joins among many tables that only doing with dynamic query even, for now I am searching for information on the internet but still not found a way out.

2 answers

1


Try the following:

@Query(value = "SELECT * FROM VRS.TB_CLIE_INVS /*#pageable*/",
       countQuery = "SELECT count(*) FROM VRS.TB_CLIE_INVS",
       nativeQuery = true)
Page<TbClieInv> testePaginacaoQuery(Pageable pageable);

Source: Soen - Spring Data and Native Query with pagination

  • correct code executed successfully, Thank you.

0

Worked !!!!!

worked exactly this way without order by, the intention was to simulate the Page findAll of Pagingandsorting and the test was successfully run, I had already tried to do with Count as you passed me but the key of my problems really was to put this command /*#pageable*/", I think now with this I will be able to convert the other dynamic querys I have to with paging, thank you very much for the help, follow the code that worked:

@Query(value = "SELECT * FROM tabela1 /*#pageable*/",  
       countQuery = "SELECT count(*) FROM tabela1",
       nativeQuery = true)
Page<ClassemodeloTabela> testePaginacaoQuery(Pageable pageable);

  • Hi Leandro, I formatted your code and updated my reply to reflect your comment. You can mark an answer as correct by accepting it and indicating that it is good with a positive vote (tour) . Did you really want to write an answer to your question or was your intention just to comment on my answer? In the second case you can do this once you have 50 reputation points, due to the format of the site responses and comments are separate things and we should not use responses to comment.

  • I could not paste as an answer because it gave exceeded length, despite reading years ago, but it is the first time I put here, I tried to answer your comment but has lines limitations, so I put below for general view, thank you very much.

  • Good thing you broke the barrier of the first post ;). I will signal to the moderators to see if they can convert the answer to comment (as the code was the same I think the rest fits).

  • I have big syntax doubts to make this select with paging and using Join, this is possible ?

Browser other questions tagged query oracle paging

You are not signed in. Login or sign up in order to post.