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<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> 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 && !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

No, I haven't found a solution yet. I created a ticket in Spring JIRA but there's no response: jira.spring.io/browse/DATAJPA-928. In the end, I didn't need pagination so I haven't tried to investigate any further or push harder with that ticket. — Lasneyx
– Lasneyx, Commented Sep 19, 2016 at 11:25
Okay, thanks. As a workaround you could have added "\n#pageable\n" instead of "#pageable", but I hope it will be fixed in the future. — Janar
– Janar, Commented Sep 19, 2016 at 11:35
that current fix made things worse for me. now my PageAble parameter is completely ignored and the query is executed unlimited. so if you inserted that \n#pageable\n workaround, make sure to remove it or you'll get into trouble. — Rüdiger Schulz
– Rüdiger Schulz, Commented Apr 10, 2018 at 9:41
For me, I was using @Param("lastname") annotation and the problem was WHERE LASTNAME = \":lastname\", where these double quotes were unnecessary. So it was because I simply forgot to remove double quotes from these param of SQL. — lechat
– lechat, Commented Jan 14, 2023 at 18:54
As others have noted, adding #pageable is no longer needed on newer versions of JPA, but be sure your Pageable param has a Sort specified otherwise it will be ignored. — hallman76
– hallman76, Commented Sep 26, 2023 at 14:25

Community · Accepted Answer · 2017-05-23 10:31:37Z

64

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>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>p.update_time) \n#pageable\n",*/
        nativeQuery=true)
public List<Author> findAuthorsUpdatedAndNew(Pageable pageable);

The countQuery (that is commented out in the code block) is needed to use Page<Author> 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...

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Sep 21, 2016 at 13:02

Emanuele Fusco

8296 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dmitry Stolbov Over a year ago

In my case ?#{#pageable} is working instead \n#pageable\n.

Abhishek Dujari Over a year ago

This works. For arguments you can still use named parameters. example :authorId . I changed your \n#pageable\n to --#pageable\n for postgresql. The countQuery is needed as you suggested since many examples use List<> instead of Page<> your answer was spot on for this. I went through the same docs and if it wasn't for your answer I would have just given up.

Nalaka Over a year ago

If you don't write the countQuery and letting the framework handles that, sometime doesn't write the count by query correctly for you and you might see an error as invalid field list. You can always analyze the SQL statements by adding below entries to the property file logging.level.org.hibernate.SQL=DEBUG logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE Adding the count by query separately will fix the issue. This might depend of the framework versions and the database you are using.

marioosh Over a year ago

In my case only /*:pageable*/ works (with SQL Server, Hibernate 5.4.1.Final and Spring Boot 2.2.1)

wmrodrigues Over a year ago

Is it just me or anyone else notice that -- for Postgres and /**/ for most of database engines means 'please, comment this bloc of code'? Am I misunderstanding the answers? Sorry if I'm reading all on the wrong side.

Dmitry Stolbov · Accepted Answer · 2020-03-12 04:57:12Z

54

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<User, Long> {

@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 ORDER BY ?#{#pageable}",
       countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
       nativeQuery = true)
   Page<User> findByLastname(String lastname, Pageable pageable);   
}

ORDER BY ?#{#pageable} is for Pageable. countQuery is for Page<User>.

edited Mar 12, 2020 at 4:57

answered Dec 22, 2016 at 12:48

Dmitry Stolbov

2,8272 gold badges27 silver badges24 bronze badges

2 Comments

Dmitry Stolbov Over a year ago

I don't now. Try 1. enable logging of SQL statements - set spring.jpa.show-sql=false in application.properties; 2. set ?#{#pageable} in your nativeQuery and 3. analyse result SQL from log. @Lasneyx created issue DATAJPA-928 for this peculiarity of Spring Data JPA.

Rajanboy Over a year ago

Hello @Dmitry , what should i do next, after i got Pageable pageable to make it paginated thank you?

Tim Biegeleisen · Accepted Answer · 2021-06-23 10:35:28Z

29

I am adding this answer just as a placeholder for those users who are using more recent versions of Spring Boot. On Spring Boot 2.4.3, I observed that none of the workaround were necessary, and the following code worked straight out of the box for me:

public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value="SELECT * FROM USERS WHERE LASTNAME = ?1", nativeQuery=true)
    Page<User> findByLastname(String lastname, Pageable pageable);
}

The countQuery definition was not necessary, and a call to Page#getTotalElements() in fact already was returning the correct count, as returned by JPA's own internal count query.

The above code is extremely powerful, offering pagination made via a native query, yet which return results into actual Java entities (rather than the ugly and bulky List<Object[]>, which sometimes is necessary).

answered Jun 23, 2021 at 10:35

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

6 Comments

Nisarg Patil Over a year ago

This worked for me, avoid using table alias in select query, it won't work!

Rajanboy Over a year ago

This also worked for me in Spring boot 3. This should be answer .

hakima maarouf Over a year ago

but when I use alias I had to add countQuery otherwise I got oracle.jdbc.OracleDatabaseException: ORA-00904: "myAlias" invalid identifier

saran3h Over a year ago

Does it work if the query has complex joins? What’s the actual query that’s generated?

Amir Keshavarz Over a year ago

I get this error while using MSSQL: The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer

|

maricn · Accepted Answer · 2017-04-13 10:45:37Z

14

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 < 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<T> findByUserIdInGroupByObjectId(@Param("user_ids") Set<Integer> 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).

answered Apr 13, 2017 at 10:45

maricn

6331 gold badge7 silver badges23 bronze badges

1 Comment

Kulbhushan Singh Over a year ago

what if we have to pass order by asc or desc dynamically to the query?

Thomas Schmidt · Accepted Answer · 2017-06-28 10:54:57Z

9

Try this:

public interface UserRepository extends JpaRepository<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 ORDER BY /*#pageable*/",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

("/* */" for Oracle notation)

edited Jun 28, 2017 at 10:54

Thomas Schmidt

1,3883 gold badges12 silver badges39 bronze badges

answered Feb 10, 2017 at 3:16

XmasPiano

911 silver badge1 bronze badge

2 Comments

d-man Over a year ago

some reason genreated query having comma after pageable some thing like this "ORDER BY /*#pageable*/," causing syntax error

hicham abdedaime Over a year ago

i remove order by but generated exception unexpected char: '#'

Pang · Accepted Answer · 2024-08-02 09:02:56Z

8

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<User> getUsersByTypes(@Param("userTypes") List<String> userTypes, Pageable pageable);

edited Aug 2, 2024 at 9:02

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Dec 8, 2016 at 15:08

689

5395 silver badges6 bronze badges

2 Comments

Anders Metnik Over a year ago

Worked for me with DB2 as well.

Developer Over a year ago

@Query(nativeQuery = true, value= "select a.* from rqst a LEFT OUTER JOIN table_b b ON a.id= b.id where a.code='abc' ORDER BY --#pageable\n") is not working : Couldn't determine the data type of parameter $2

Level_Up · Accepted Answer · 2018-07-03 09:45:13Z

6

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<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 /*#pageable*/ ORDER BY LASTNAME",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
 }

answered Jul 3, 2018 at 9:45

Level_Up

8246 silver badges19 bronze badges

Comments

Udara Seneviratne · Accepted Answer · 2020-08-24 11:57:21Z

5

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<User> findAllUsersWithPagination(Pageable pageable);

answered Aug 24, 2020 at 11:57

Udara Seneviratne

2,5431 gold badge39 silver badges54 bronze badges

1 Comment

java dev Over a year ago

Where pageable parameter in the native query?

wmao · Accepted Answer · 2017-01-10 04:18:54Z

2

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

edited Jan 10, 2017 at 4:18

answered Jan 10, 2017 at 4:08

wmao

292 bronze badges

Comments

The Code Guy · Accepted Answer · 2020-01-14 20:58:19Z

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<Entity> 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=&name=&state=published&page=0&size=3&sort=name,desc

Watch out for this issue: Spring Pageable does not translate @Column name

Muhamed Saudi · Accepted Answer · 2018-01-03 06:40:56Z

1

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

answered Jan 3, 2018 at 6:40

Muhamed Saudi

192 bronze badges

Comments

nobjta_9x_tq · Accepted Answer · 2022-03-25 16:40:10Z

1

Create your custom repository:

public interface ProductsCustomRepository extends JpaRepository<ProductResultEntity, Long> {
    @Query(
        value = "select tableA.id, tableB.bank_name from tableA join tableB on tableA.id = tableB.a_id where tableA.id = :id
        and (:fieldX is null or tableA.fieldX LIKE :fieldX)",
        countQuery = "select count(*) from tableA join tableB on tableA.id = tableB.a_id where tableA.id = :id
        and (:fieldX is null or tableA.fieldX LIKE :fieldX)",
        nativeQuery = true
    )
    Page<ProductResultEntity> search(@Param("id") Long aId,
        @Param("fieldX") String keyword, Pageable pageable
    );
}

Create View as query of:

create view zzz as select * from tableA join tableB on tableA.id = tableB.a_id

Generate ProductResultEntity from that zzz view (and delete view zzz when have done)
Test call function and enjoy it:

productsRepository.search("123", "%BANK%", PageRequest.of(0, 5, Sort.by(Sort.Direction.ASC, "id")));

Entity:

@Entity
public class ProductResultEntity {
    private Long id;
    private String bank;

    @Id
    @Column(name = "id", nullable = false)
    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    @Column(name = "bank_name", nullable = false)
    public String getBank() {
        return bank;
    }

    public void setBank(String bank) {
        this.bank = bank;
    }
}

edited Mar 25, 2022 at 16:40

answered Mar 25, 2022 at 16:33

nobjta_9x_tq

1,24115 silver badges17 bronze badges

1 Comment

nobjta_9x_tq Over a year ago

Entity generate by View which uses that tool in Inteliji: Persistence or you can write it by hand.

amit kumar · Accepted Answer · 2019-09-18 09:10:00Z

0

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<ABC> findByBinUseFAndRgtnType(@Param("type") List<Byte>type,Pageable pageable);

answered Sep 18, 2019 at 9:10

amit kumar

791 gold badge2 silver badges11 bronze badges

Comments

Kübra · Accepted Answer · 2019-10-08 08:40:39Z

0

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<AplUserEntity> searchUser(@Param("scrname") String scrname,@Param("uname") String  uname,@Param("roleIds") List<Long> roleIds,Pageable pageable);

answered Oct 8, 2019 at 8:40

Kübra

1973 silver badges10 bronze badges

1 Comment

Dragonthoughts Over a year ago

Please can you explain why this answer is better than any of the others that have been around for several years?

Shubham Paldewar · Accepted Answer · 2019-11-04 06:21:55Z

0

Removing \n#pageable\n from both query and count query worked for me. Springboot version : 2.1.5.RELEASE DB : Mysql

answered Nov 4, 2019 at 6:21

Shubham Paldewar

614 bronze badges

Comments

Salah Atwa · Accepted Answer · 2020-02-18 11:51:47Z

0

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<UserRequestsDataMapper> getAllRequestForCreator(@Param("userId") String userId,Pageable pageable);

answered Feb 18, 2020 at 11:51

Salah Atwa

1,7282 gold badges14 silver badges13 bronze badges

Comments

Fonda Wu · Accepted Answer · 2017-11-25 09:19:24Z

-1

It does work as below:

public interface UserRepository extends JpaRepository<User, Long> {
    @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<User> findByLastname(String lastname, Pageable pageable);
}

answered Nov 25, 2017 at 9:19

Fonda Wu

1

Comments

Rahul Dudhane · Accepted Answer · 2021-01-22 15:08:34Z

-1

You can achieve it by using following code,

@Query(value = "SELECT * FROM users u WHERE  ORDER BY ?#{#pageable}", nativeQuery = true)
List<User> getUsers(String name, Pageable pageable);

Simply use ORDER BY ?#{#pageable} and pass page request to your method.

Enjoy!

answered Jan 22, 2021 at 15:08

Rahul Dudhane

5546 silver badges6 bronze badges

Comments

James Winnchester · Accepted Answer · 2022-09-26 03:29:19Z

@Query(value = "select " +
//"row_number() over (order by ba.order_num asc) as id, " +
"row_number() over () as id, " +
"count(ba.order_num),sum(ba.order_qty) as sumqty, " +
"ba.order_num, " +
"md.dpp_code,md.dpp_name, " +
"from biz_arrangement ba " +
"left join mst_dpp md on ba.dpp_code = md.dpp_code " +
"where 1 = 1 " +
"AND (:#{#flilter.customerCodeListCheck}='' OR ba.customer_code IN (:#{#flilter.customerCodeList})) " +
"AND (:#{#flilter.customerNameListCheck}='' OR ba.customer_name IN (:#{#flilter.customerNameList})) " +
"group by " +
"ba.order_num, " +
"md.dpp_code,md.dpp_name ",
countQuery = "select " +
"count ( " +
"distinct ( " +
"ba.order_num, " +
"md.dpp_code,md.dpp_name) " +
")" +
"from biz_arrangement ba " +
"left join mst_dpp md on ba.dpp_code = md.dpp_code " +
"where 1 = 1 " +
"AND (:#{#flilter.customerCodeListCheck}='' OR ba.customer_code IN (:#{#flilter.customerCodeList})) " +
"AND (:#{#flilter.customerNameListCheck}='' OR ba.customer_name IN (:#{#flilter.customerNameList})) ",
nativeQuery = true)
Page<Map<String, Object>> nativeQueryDynamicPageAndSort(@Param("flilter") Flilter flilter, Pageable pageable);

no need to add ?#{#pageable}, the problem I got is when I use

row_number() over (order by ba.order_num asc) as id,

the input sort won't work when I change to

row_number() over () as id,

the dynamic input sort and pagination are both okay!

This is a group by query with a row id.

Mohit Thakkar · Accepted Answer · 2018-07-25 06:17:13Z

-2

Replacing /#pageable/ with ?#{#pageable} allow to do pagination. Adding PageableDefault allow you to set size of page Elements.

answered Jul 25, 2018 at 6:17

Mohit Thakkar

93 bronze badges

Collectives™ on Stack Overflow

Spring Data and Native Query with pagination

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.

20 Answers 20

5 Comments

2 Comments

6 Comments

1 Comment

2 Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

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.

20 Answers 20

5 Comments

2 Comments

6 Comments

1 Comment

2 Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Related