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

Question

@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 ........

What's your question here?

ControlAltDel
– ControlAltDel

2017-05-19 03:15:50 +00:00
Commented May 19, 2017 at 3:15 — ControlAltDel
– ControlAltDel, Commented May 19, 2017 at 3:15
take a look at this answer it may help.

Fady Saad
– Fady Saad

2017-05-19 03:21:17 +00:00
Commented May 19, 2017 at 3:21 — Fady Saad
– Fady Saad, Commented May 19, 2017 at 3:21
@ControlAltDel it's all there

SHALong
– SHALong

2017-05-19 03:29:34 +00:00
Commented May 19, 2017 at 3:29 — SHALong
– SHALong, Commented May 19, 2017 at 3:29

zhouji · Accepted Answer · 2017-08-08 06:42:46Z

36

解决没? 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.

edited Aug 8, 2017 at 6:42

answered Aug 8, 2017 at 6:35

zhouji

5,3561 gold badge29 silver badges26 bronze badges

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

1 Comment

Rob Over a year ago

Please flag the question as a duplicate instead of posting an answer, in the future

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related