Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cannot use native queries with dynamic sorting and/or pagination even if I have specified the count query [duplicate]

@Query( value = "select * from paper_entry where owner is null or owner = ?1", countQuery = "select count(*) from paper_entry where owner is not null or owner = ?1", nativeQuery = true)

Page findAll(Long userId, Pageable pageable);

I use mysql 5.7, spring-data-jpa 1.11.3.RELEASE. As you can see, I follow the document https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query. However I got this error.

Caused by: org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting and/or pagination in method public abstract org.springframework.data.domain.Page com.gbdata.entry.persistence.dao.PaperEntryRepository.findAll(java.lang.Long,org.springframework.data.domain.Pageable) at org.springframework.data.jpa.repository.query.NativeJpaQuery.(NativeJpaQuery.java:55) ~[spring-data-jpa-1.11.3.RELEASE.jar:na] at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:72) ~[spring-data-jpa-1.11.3.RELEASE.jar:na] at ........

like image 605
CuteDoge Avatar asked May 19 '17 03:05

CuteDoge


People also ask

Does native query support pagination?

@Query(value = "SELECT u FROM User u ORDER BY id") Page<User> findAllUsersWithPagination(Pageable pageable); We can pass a PageRequest parameter to get a page of data. Pagination is also supported for native queries but requires a little bit of additional work.

Which is faster JPQL or native query?

Also, JPA criteria queries are as fast as JPQL queries, and JPA native queries have the same efficiency as JPQL queries. This test run has 11008 records in the Order table, 22008 records in the LineItem table, and 44000 records in the Customer table.

What is native query in JPA?

Native query refers to actual sql queries (referring to actual database objects). These queries are the sql statements which can be directly executed in database using a database client. JPA : entityManager.createNativeQuery() Hibernate (Non-JPA implementation): session.createSQLQuery()

How does JPA pagination work?

You can use the JPA pagination for both entity queries and native SQL. To limit the underlying query ResultSet size, the JPA Query interface provides the setMaxResults method. Navigating the following page requires positioning the result set where the last page ended.


1 Answers

解决没? SQL里面加 ORDER BY ?#{#pageable} 就可以了

@Query(
        value = "select * from paper_entry where owner is null or owner = ?1 ORDER BY ?#{#pageable}",
        countQuery = "select count(*) from paper_entry where owner is not null or owner = ?1 ORDER BY ?#{#pageable}",
        nativeQuery = true)
    Page findAll(Long userId, Pageable pageable);

It's duplicate of this question.

like image 66
zhouji Avatar answered Sep 21 '22 09:09

zhouji