-2
`@Query(value = "select * from jhi_user order by ?#{#pageable}",
    countQuery = "select count(*) from jhi_user",nativeQuery = true)
Page<User> findAllUsers(Pageable pageable);`

This is my SQL. I could get the right answer, but in MySQL's log, there's messy code.

enter image description here

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
yo shen
  • 1
  • 2

2 Answers2

0

Are you saying this works? That would be really surprising to me. What you see in the logs is the result of the toString of the Pageable you are providing.

Assuming I misunderstood you and this actually doesn't work:

Just remove the order by ?#{#pageable} from your query. Parameter binding doesn't work in that position and pagination is done behind the scenes by Spring Data JPA.

There was a bug concerning this which is fixed for quite some time now and there existed workarounds for it which looked similar to what you have, but they are no longer valid.

See https://jira.spring.io/browse/DATAJPA-928 for details.

Also, Spring Data and Native Query with pagination which basically is about the same issue.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • In spring boot 2.0+,removing 'order by ?#{#pageable}' really works.But my project is using spring boot 1.4.1, so I have to add it .This causes some strange words in mysql's log as the picture shows. It's really annoying me although the pagination has already succeeded. Anyway, thank you for your answer! – yo shen Jan 23 '19 at 01:19
0

This issue is handled.I remove the order by ?#{#pageable} and add \n#pageable\n .This really works and the messy codes disappear.

yo shen
  • 1
  • 2