Spring Data and Native Query with pagination

Question

In a web project  using latest spring-data  1 10 2  with a MySQL 5 6 database  I m trying to use a native query with pagination but I m experiencing an org springframework data jpa repository query InvalidJpaQueryMethodException at startup    UPDATE  20180306 This issue is now fixed in Spring 2 0 4 For those still interested or stuck with older versions check the related answers and comments for workarounds   According to Example 50 at Using  Query from spring-data documentation this is possible specifying the query itself and a countQuery  like this   public interface UserRepository extends JpaRepository lt User  Long gt       Query value    SELECT   FROM USERS WHERE LASTNAME    1       countQuery    SELECT count    FROM USERS WHERE LASTNAME    1       nativeQuery   true    Page lt User gt  findByLastname String lastname  Pageable pageable       Out of curiosity  In NativeJpaQuery class I can see that it contains the following code to check if it s a valid jpa query   public NativeJpaQuery JpaQueryMethod method  EntityManager em  String queryString  EvaluationContextProvider evaluationContextProvider  SpelExpressionParser parser       super method  em  queryString  evaluationContextProvider  parser      JpaParameters parameters   method getParameters       boolean hasPagingOrSortingParameter   parameters hasPageableParameter      parameters hasSortParameter       boolean containsPageableOrSortInQueryExpression   queryString contains   pageable      queryString contains   sort       if hasPagingOrSortingParameter  amp  amp   containsPageableOrSortInQueryExpression           throw new InvalidJpaQueryMethodException  Cannot use native queries with dynamic sorting and or pagination in method     method            My query contains a Pageable parameter  so hasPagingOrSortingParameter is true  but it s also looking for a  pageable or  sort sequence inside the queryString  which I do not provide   I ve tried adding  pageable  it s a comment  at the end of my query  which makes validation to pass but then  it fails at execution saying that the query expects one additional parameter  3 instead of 2   Funny thing is that  if I manually change containsPageableOrSortInQueryExpression from false to true while running  the query works fine so I don t know why it s checking for that string to be at my queryString and I don t know how to provide it   Any help would be much appreciated   Update 01 30 2018 It seems that developers at spring-data project are working on a fix for this issue with a PR by Jens Schauder

User · Answer

You can use below code for h2 and MySQl       Query value    SELECT req CREATED AT createdAt  req CREATED BY createdBy req APP ID appId req NOTE ID noteId req MODEL model FROM SUMBITED REQUESTS  req inner join NOTE note where req NOTE ID note ID and note CREATED BY   userId                      countQuery    SELECT count    FROM SUMBITED REQUESTS req inner join NOTE note WHERE req NOTE ID note ID and note CREATED BY  userId           nativeQuery   true  Page lt UserRequestsDataMapper gt  getAllRequestForCreator  Param  userId   String userId Pageable pageable

User · Answer

I use oracle database and I did not get the result but an error with generated comma which d-man speak about above   Then my solution was   Pageable pageable   new PageRequest current  rowCount     As you can see without order by when create Pagable   And the method in the DAO   public interface UserRepository extends JpaRepository lt User  Long gt       Query value    SELECT   FROM USERS WHERE LASTNAME    1    pageable   ORDER BY LASTNAME       countQuery    SELECT count    FROM USERS WHERE LASTNAME    1       nativeQuery   true    Page lt User gt  findByLastname String lastname  Pageable pageable

User · Answer

Removing  n pageable n from both query and count query worked for me  Springboot version   2 1 5 RELEASE DB   Mysql

User · Answer

Replacing   pageable  with     pageable  allow to do pagination  Adding PageableDefault allow you to set size of page Elements

User · Answer

My apologies in advance  this is pretty much summing up the original question and the comment from Janar  however     I run into the same problem  I found the Example 50 of Spring Data as the solution for my need of having a native query with pagination but Spring was complaining on startup that I could not use pagination with native queries   I just wanted to report that I managed to run  successfully the native query I needed  using pagination  with the following code        Query value  SELECT a                    FROM author a left outer join mappable natural person p on a id   p provenance id                  WHERE p update time is null OR  p provenance name  biblio db  and a update time gt p update time                  ORDER BY a id  n pageable n              countQuery  SELECT count a                     FROM author a left outer join mappable natural person p on a id   p provenance id                  WHERE p update time is null OR  p provenance name  biblio db  and a update time gt p update time   n pageable n             nativeQuery true  public List lt Author gt  findAuthorsUpdatedAndNew Pageable pageable     The countQuery  that is commented out in the code block  is needed to use Page lt Author gt  as the return type of the query  the newlines around the   pageable  comment are needed to avoid the runtime error on the number of expected parameters  workaround of the workaround   I hope this bug will be fixed soon

User · Answer

For me below worked in MS SQL    Query value  SELECT   FROM ABC r where r type in  type  ORDER BY RAND    n--  pageable n   nativeQuery   true  List lt ABC gt  findByBinUseFAndRgtnType  Param  type   List lt Byte gt type Pageable pageable

User · Answer

Just for the record  using H2 as testing database  and MySQL at runtime  this approach works  example is newest object in group     Query value    SELECT t   FROM t LEFT JOIN t AS t newer              ON t object id   t newer object id AND t id  lt  t newer id AND o newer user id IN   user ids               WHERE t newer id IS NULL AND t user id IN   user ids               ORDER BY t id DESC  n--  pageable n           countQuery    SELECT COUNT 1  FROM t WHERE t user id IN   user ids  GROUP BY t object id  t user id           nativeQuery   true  Page lt T gt  findByUserIdInGroupByObjectId  Param  user ids   Set lt Integer gt  userIds  Pageable pageable     Spring Data JPA 1 10 5  H2 1 4 194  MySQL Community Server 5 7 11-log  innodb version 5 7 11

User · Answer

This is a hack for program using Spring Data JPA before Version 2 0 4   Code has worked with PostgreSQL and MySQL    public interface UserRepository extends JpaRepository lt User  Long gt      Query value    SELECT   FROM USERS WHERE LASTNAME    1 ORDER BY     pageable           countQuery    SELECT count    FROM USERS WHERE LASTNAME    1          nativeQuery   true     Page lt User gt  findByLastname String lastname  Pageable pageable          ORDER BY     pageable  is for Pageable  countQuery is for Page lt User gt

User · Answer

Both the following approaches work fine with MySQL for paginating native query  They doesn t work with H2 though  It will complain the sql syntax error    ORDER BY     pageable  ORDER BY a id  n pageable n

User · Answer

I could successfully integrate Pagination in  spring-data-jpa-2 1 6  as follows   Query   value      SELECT   FROM Users       countQuery      SELECT count    FROM Users       nativeQuery   true  Page lt User gt  findAllUsersWithPagination Pageable pageable

User · Answer

This worked for me  I am using Postgres  in Groovy      RestResource path  namespaceAndNameAndRawStateContainsMostRecentVersion   rel  namespaceAndNameAndRawStateContainsMostRecentVersion        Query nativeQuery true              countQuery                 SELECT COUNT 1               FROM                                SELECT                 ROW NUMBER   OVER                       PARTITION BY name  provider id  state                     ORDER BY version DESC  version partition                                    FROM mydb mytable                 WHERE                  name ILIKE          name         OR   name        AND                  namespace ILIKE          namespace         OR   namespace        AND                  state    state OR   state                      t             WHERE version partition   1                              value                 SELECT id  version  state  name  internal name  namespace  provider id  config  create date  update date              FROM                                SELECT                  ROW NUMBER   OVER                       PARTITION BY name  provider id  state                     ORDER BY version DESC  version partition                                    FROM mydb mytable                 WHERE                   name ILIKE          name         OR   name        AND                  namespace ILIKE          namespace         OR   namespace        AND                  state    state OR   state                             t                         WHERE version partition   1                               pageable                         public Page lt Entity gt  findByNamespaceContainsAndNameContainsAndRawStateContainsMostRecentVersion  Param  namespace  String namespace   Param  name  String name   Param  state  String state  Pageable pageable    The key here was to use       pageable     It allows me to do sorting and pagination  You can test it by using something like this  http   localhost 8080 api v1 entities search namespaceAndNameAndRawStateContainsMostRecentVersion namespace  amp name  amp state published amp page 0 amp size 3 amp sort name desc  Watch out for this issue  Spring Pageable does not translate  Column name

User · Answer

It does work as below   public interface UserRepository extends JpaRepository lt User  Long gt         Query value    select   from  select   rowid     rowid 1  as RN  u   from USERS u   SELECT  rowid    0  as init where  LASTNAME    1  as total            where RN between     pageable offset-1  and     pageable offset    pageable pageSize        countQuery    SELECT count    FROM USERS WHERE LASTNAME    1       nativeQuery   true      Page lt User gt  findByLastname String lastname  Pageable pageable

User · Answer

You can achieve it by using following code   Query value    quot SELECT   FROM users u WHERE  ORDER BY     pageable  quot   nativeQuery   true  List lt User gt  getUsers String name  Pageable pageable    Simply use ORDER BY     pageable  and pass page request to your method  Enjoy

User · Answer

I have exact same symptom like  Lasneyx  My workaround for Postgres native query   Query value    select   from users where user type in   userTypes  and user context  abc -- pageable n   nativeQuery   true  List lt User gt  getUsersByTypes  Param  userTypes   List lt String gt  userTypes  Pageable pageable

User · Answer

I m using the code below  working     Query value    select   from user usr       left join apl apl on usr user id   apl id       left join lang on lang role id   usr role id       where apl scr name like   scrname  and apl uname like   uname and usr role id in  roleIds ORDER BY     pageable      countQuery    select count    from user usr           left join apl apl on usr user id   apl id           left join lang on lang role id   usr role id           where apl scr name like   scrname  and apl uname like   uname and usr role id in  roleIds     nativeQuery   true  Page lt AplUserEntity gt  searchUser  Param  scrname   String scrname  Param  uname   String  uname  Param  roleIds   List lt Long gt  roleIds Pageable pageable

User · Answer

Using  ORDER BY id DESC  n--  pageable n    instead of  ORDER BY id  n pageable n  worked for me with MS SQL SERVER

User · Answer

Try this   public interface UserRepository extends JpaRepository lt User  Long gt       Query value    SELECT   FROM USERS WHERE LASTNAME    1 ORDER BY    pageable         countQuery    SELECT count    FROM USERS WHERE LASTNAME    1       nativeQuery   true    Page lt User gt  findByLastname String lastname  Pageable pageable                 for Oracle notation

[spring] Spring Data and Native Query with pagination

Examples related to spring

Examples related to spring-data

Examples related to spring-data-jpa