Java Spring JPA分页如果超过一页，则无法正常工作 [英] Java Spring JPA Pagination Not working if more than one Page

问题描述

我正在尝试通过自定义查询使用jpa分页.

I'm trying to use jpa Pagination with custom query.

如果匹配2个案例，则分页有效.

pagination is working if match with 2 cases.

案例1:

• 如果可用数据少于10个且页面编号为0的请求

如果请求不起作用

和

情况2:

• 如果可用数据超过10个，且页面编号为1的请求

如果不分页不起作用.

我无法找出问题所在.有帮助的人吗?

i cant figure out the problem. Any help guys?

谢谢.

@Repository
public interface SmsLogRepository extends JpaRepository<SmsLog, Long> {

@Query(value = "select * from sms_log s where s.phone_number = (:ph) \n#pageable\n", nativeQuery = true)
Page<SmsLog> findByPhoneNumber(@Param("ph") String phoneNumber, Pageable pageable);

@Query(value = "select * from sms_log s where s.phone_number = (:ph) AND s.message_timestamp between (:fromdate) and (:todate) \n#pageable\n", nativeQuery = true)
Page<SmsLog> findByPhoneNumberAndDate(@Param("ph") String phoneNumber, @Param("fromdate") String fromDate, @Param("todate") String todate, Pageable pageable);

@Query(value = "select * from sms_log s where s.message_timestamp between (:fromdate) and (:todate) \n#pageable\n", nativeQuery = true)
Page<SmsLog> findByDate(@Param("fromdate") String fromDate, @Param("todate") String todate, Pageable pageable);
}

请求:

PageRequest pageRequest = new PageRequest(page, 10, new Sort(new Sort.Order(Sort.Direction.DESC, "message_timestamp")));
            return logRepository.findByPhoneNumber(ph, pageRequest);

推荐答案

花了一些时间后我找到了答案

After spending some time i found the answer

我错过了计数查询.添加了计数查询后，分页的工作方式就像魅力:)

I missed the count query. After adding the count query pagination worked like charm :)

@Repository
public interface SmsLogRepository extends JpaRepository<SmsLog, Long> {

@Query(value = "select * from sms_log s where s.phone_number = (:ph) \n#pageable\n", countQuery = "select count(*) from sms_log s where s.phone_number = (:ph) \n#pageable\n", nativeQuery = true)
Page<SmsLog> findByPhoneNumber(@Param("ph") String phoneNumber, Pageable pageable);

@Query(value = "select * from sms_log s where s.phone_number = (:ph) AND s.message_timestamp between (:fromdate) and (:todate) \n#pageable\n",countQuery = "select count(*) from sms_log s where s.phone_number = (:ph) AND s.message_timestamp between (:fromdate) and (:todate) \n#pageable\n", nativeQuery = true)
Page<SmsLog> findByPhoneNumberAndDate(@Param("ph") String phoneNumber, @Param("fromdate") String fromDate, @Param("todate") String todate, Pageable pageable);

@Query(value = "select * from sms_log s where s.message_timestamp between (:fromdate) and (:todate) \n#pageable\n",countQuery = "select count(*) from sms_log s where s.message_timestamp between (:fromdate) and (:todate) \n#pageable\n", nativeQuery = true)
Page<SmsLog> findByDate(@Param("fromdate") String fromDate, @Param("todate") String todate, Pageable pageable);
}

这篇关于Java Spring JPA分页如果超过一页，则无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！