Spring Data and Native Query with pagination

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

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

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

Try this:

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);
}

("/* */" for Oracle notation)

I have exact same symptom like @Lasneyx. My workaround for Postgres native query

@Query(value = "select * from users where user_type in (:userTypes) and user_context='abc'--#pageable\n", nativeQuery = true)
List<User> getUsersByTypes(@Param("userTypes") List<String> userTypes, Pageable pageable);

Spring Data and Native Query with pagination

Related

Recent Posts