I'm trying to use jpa Pagination with custom query.
pagination is working if match with 2 cases.
Case 1 :
if data available less than 10 and request with page number is 0
if not the request not working
and
Case 2:
if data available more than 10 and request with page number is 1
if not pagination is not working.
i cant figure out the problem. Any help guys?
Thanks in advance.
@Repository
public interface SmsLogRepository extends JpaRepository {
@Query(value = "select * from sms_log s where s.phone_number = (:ph) \n#pageable\n", nativeQuery = true)
Page 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 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 findByDate(@Param("fromdate") String fromDate, @Param("todate") String todate, Pageable pageable);
}
Request :
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 {
@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 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 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 findByDate(@Param("fromdate") String fromDate, @Param("todate") String todate, Pageable pageable);
}