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

Answer 1

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

Answer 2

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

Answer 3

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