Spring Data Native query pagination not working

Question

I am using Spring Boot 2.0.2.RELEASE with Spring Data JPA. I am trying to do implement pagination with native query in MySql, my code is :

@Query(nativeQuery=true, value = "SELECT DISTINCT ud.latitude,ud.longitude,u.user_id userId FROM users u \n" +
            "INNER JOIN user_devices ud ON u.id = ud.user_id\n" +
            "WHERE ud.access_token IS NOT NULL AND ud.user_id <> 1\n" +
            "ORDER BY calculateDistanceByLatLong(:userLat, :userLong, ud.latitude, ud.longitude) ASC \n#pageable\n",
            countQuery = "SELECT COUNT(DISTINCT u.id) FROM users u \n" +
                    "INNER JOIN user_devices ud ON u.id = ud.user_id\n" +
            "WHERE ud.access_token IS NOT NULL AND ud.user_id <> 1 \n#pageable\n")
    public Page<LocationProjection> listNearByUsers(@Param("userLat")String userLatitude,@Param("userLong") String userLongitude, Pageable pageable) throws Exception;

I got the reference from this link.

And also reviewed this link.

But it's not adding any pagination code. When I try to use :

userDao.listNearByUsers(userDeviceEntity.getLatitude(),userDeviceEntity.getLongitude(), PageRequest.of(pageNo, maxResults))

And for example set pageNo=0 and maxResults =1,all the results are displayed. So no pagination is being implemented. I printed the fired query, it is :

SELECT
DISTINCT ud.latitude,
ud.longitude,
u.user_id userId 
FROM
users u  
INNER JOIN
user_devices ud 
ON u.id = ud.user_id 
WHERE
ud.access_token IS NOT NULL 
AND ud.user_id <> 1 
ORDER BY
calculateDistanceByLatLong(?,
?,
ud.latitude,
ud.longitude) ASC  #pageable

And the count query as :

SELECT
COUNT(DISTINCT u.id) 
FROM
users u  
INNER JOIN
user_devices ud 
ON u.id = ud.user_id 
WHERE
ud.access_token IS NOT NULL 
AND ud.user_id <> 1  #pageable

I thought Spring Data would add "LIMIT 0,1" in the main query but it's not working. The DAO interface is :

@Repository
public interface UserDao extends JpaRepository<UserEntity, Integer>{}

Please suggest some solution.

you are using native query but there is no rownum in your query which will apply pagination. Use JPQL and countQuery for this — Lemmy, Jun 15 '18 at 08:22
@MountainKing I followed the reference links that I have mentioned. There was no such thing. Can you elaborate more ? — SudeepShakya, Jun 15 '18 at 08:24

score 10 · Answer 1 · edited Aug 19 '19 at 17:19

After going over several Q/A here, and none working properly (for my setup, at least). After reading this bit at Spring official documentation, I've managed to get it working by simply removing the "pageable" bit from my query.

I'm working with spring-boot version 2.0.1.RELEASE.

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

Spring Data Native query pagination not working

1 Answers1