Pagination not working in Spring Boot Native Query

Question

I have my pagination query I am passing page and size ass ?page=0&size=5. But not getting any result. This is my query.

@Query(
    value = "SELECT *, 
            ( 6371 * acos( 
                cos( radians(:lat) ) 
                * cos( radians( latitude ) )
                * cos( radians( longitude ) 
                    - radians(:lng) ) 
                + sin( radians(:lat) ) 
                * sin( radians( latitude ) ) ) 
            ) AS distance 
        FROM stores 
            WHERE deleted = '0' 
            HAVING distance < 20000 
            ORDER BY distance ?#{#pageable}", 
    countQuery = "select count(*) from stores where deleted = 0",
    nativeQuery = true)
    public Page<Stores> getAllNearbyStores(
        @Param("lat") double lat, 
        @Param("lng") double lng, 
        Pageable pageable
    );

Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_binary'¬í\0sr\0+org.springframework.data.domain.PageRequestÀùPÅÀÇ&\0' at line 1

score 0 · Answer 1 · answered Jul 25 '18 at 05:25

there need to add \n-- #pageable\n instead of ?#{#pageable}

Query:

@Query(
    value = "SELECT *,
             ( 6371 * acos(
    cos( radians(:lat) )
    * cos( radians( latitude ) )
    * cos( radians( longitude )
    - radians(:lng) )
    + sin( radians(:lat) )
    * sin( radians( latitude ) ) )
    ) AS distance
    FROM stores
    WHERE deleted = '0'
    HAVING distance < 20000
    ORDER BY distance \n-- #pageable\n",
    countQuery = "select count(*) from stores where deleted = 0",
    nativeQuery = true)
    public Page<Stores> getAllNearbyStores(
    @Param("lat") double lat,
    @Param("lng") double lng,
    Pageable pageable
    );

you can get details here

Adding @PageableDefault(size = 5) To query workout for me! – Mohit Thakkar Jul 25 '18 at 06:09 — Mohit Thakkar, Jul 25 '18 at 06:09

score 0 · Answer 2 · edited Jul 25 '18 at 06:52

@Query(
value = "SELECT *,
         ( 6371 * acos(
cos( radians(:lat) )
* cos( radians( latitude ) )
* cos( radians( longitude )
- radians(:lng) )
+ sin( radians(:lat) )
* sin( radians( latitude ) ) )
) AS distance
FROM stores
WHERE deleted = '0'
HAVING distance < 20000
ORDER BY distance \n-- #pageable\n",
countQuery = "select count(*) from stores where deleted = 0",
nativeQuery = true)
public Page<Stores> getAllNearbyStores(
@Param("lat") double lat,
@Param("lng") double lng,
@PageableDefault(size = 5) Pageable pageable
);

There need to add default size to **@PageableDefault(size = 5). This solved the issue.

Pagination not working in Spring Boot Native Query

2 Answers2