分页在spring boot本机查询中不起作用

knpiaxh1 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(579)

我有我的分页查询我传递页和大小ass？页=0和大小=5。但没有任何结果。这是我的问题。

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

错误：您的sql语法有错误；请查看与您的mysql服务器版本对应的手册，以获取在“\u binary”附近使用的正确语法¬í\0sr\0+org.springframework.data.domain.pagerequestàùpåàç&\第1行0'

sql mysql spring-boot spring-data-jpa

来源：https://stackoverflow.com/questions/51510981/pagination-not-working-in-spring-boot-native-query

2条答案

按热度按时间

vktxenjb1#

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

需要将默认大小添加到**@pageabledefault（size=5）。这就解决了问题。

赞(0）回复(0）举报 2021-06-21

vlju58qv2#

有必要补充一下 \n-- #pageable\n 而不是 ?#{#pageable} 查询：

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

你可以在这里了解详情

赞(0）回复(0）举报 2021-06-20

我来回答

分页在spring boot本机查询中不起作用

2条答案

相关问题

热门标签

最新问答