spring-data-jpa: NativeQuery with Pagination validation error at startup [DATAJPA-928]

Miguel Martín-Forero opened DATAJPA-928 and commented

According to Example 50 at Using @Query docs, it’s possible to use a native query with pagination using Pageable but in my case it’s failing with a org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException.

NativeJpaQuery constructor is checking if the query has a Pageable parameter and if the queryString contains a #pageable or #sort sequence. The query has Pageable parameter but it does not contain a #pageable string:

@Query(value = "select m.* from message m left join user_prefers_category u on (u.category = m.category and u.user = ?1) join message_category c on c.id = m.category where u.categoryEnabled = 1 or m.category not in (select category from user_prefers_category us where us.user = ?1)",
            countQuery = "select count(m.*) from message m left join user_prefers_category u on (u.category = m.category and u.user = ?1) join message_category c on c.id = m.category where u.categoryEnabled = 1 or m.category not in (select category from user_prefers_category us where us.user = ?1)",
    nativeQuery = true)
    Page<Message> findByUserCategories(Integer userId, Pageable pageable);

If I provide a #pageable string at the end of the query, validation passes, but when the query executes, it fails saying that it’s expecting 3 parameters instead of 2.

Funny thing is that, when the server is starting, if I set a breakpoint inside NativeJpaQuery and change containsPageableOrSortInQueryExpression from false to true manually, validation passes just fine and the query executes well, paginating

---

Affects: 1.10.1 (Hopper SR1), 1.10.2 (Hopper SR2)

Reference URL: http://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination

Issue Links:

• DATAJPA-1255 Native query with SpEL creates invalid SQL

Referenced from: pull request https://github.com/spring-projects/spring-data-jpa/pull/246, and commits https://github.com/spring-projects/spring-data-jpa/commit/64c668d0ed8681e7551eeaa9a135c87c9dce99e6, https://github.com/spring-projects/spring-data-jpa/commit/b7ca8121266229f2558c5244d9dadbc6f7907e5b, https://github.com/spring-projects/spring-data-jpa/commit/68efc510dfcd1c5a7df76b7c8bac4a7198471158, https://github.com/spring-projects/spring-data-jpa/commit/8bf8e3acaf4bfb20086149ea1c79fbdc6de02264

Backported to: 2.0.4 (Kay SR4)

1 votes, 12 watchers