Spring PagingAndSortingRepository分页返回结果不一致问题解决

问题现象 #

• 调用PagingAndSortingRepository分页方法时，搜索query为“B”的返回元素数量少于搜索“Be”的结果
• 将pageSize从10调整为100后，结果恢复正常

代码背景 #

控制器代码 #

@GetMapping("/api/conferences/{conferenceId}/submissions")
public Page<SubmissionDetailDto> getPagedSubmissionsByConferenceId(
    @PathVariable("conferenceId") Long conferenceId,
    @RequestParam(required = false) Optional<String> query,
    Pageable pageable
) {
    System.out.println("Get paged submissions by conferenceId, by query, was called, " + conferenceId + ", " + query);
    return submissionService.getPagedSubmissionsByConferenceIdSearchBySurname(conferenceId, query, pageable);
}

服务层代码 #

public Page<SubmissionDetailDto> getPagedSubmissionsByConferenceIdSearchBySurname(Long conferenceId, Optional<String> query, Pageable pageable) {
    String searchQuery = query.isPresent() ? query.get() : "";
    return (Strings.isBlank(searchQuery)) ?
        mapToPagedSubmissionDetail(submissionPageRepository.searchByConferenceIdOrderByOrderNumber(conferenceId, pageable)) :
        mapToPagedSubmissionDetail(submissionPageRepository.findByConferenceIdAndContactsSurnameStartsWithIgnoreCase(conferenceId, searchQuery, pageable));
}

原始Repository代码 #

@Repository
public interface SubmissionPageRepository extends PagingAndSortingRepository<SubmissionEntity, UUID> {
    Page<SubmissionEntity> searchByConferenceIdOrderByOrderNumber(Long conferenceId, Pageable pageable);
    Page<SubmissionEntity> findByConferenceIdAndContactsSurnameStartsWithIgnoreCase(Long conferenceId, String query, Pageable pageable);
}

原因分析 #

SubmissionEntity与Contact存在一对多关联关系，JPA自动生成的关联查询SQL未添加分组逻辑。此时分页操作是针对关联查询返回的所有行（包含重复的Submission记录，每个Submission对应多个Contact时会生成多行）进行截取，而非针对去重后的Submission实体。

当搜索“B”时，匹配的Contact数量更多，单页内重复的Submission行占比更高，最终转换为DTO后去重得到的Submission数量就更少；扩大pageSize到100时，能覆盖足够多的关联行，确保所有符合条件的Submission都被包含进来。

解决方案 #

在Repository的查询方法中手动添加GROUP BY子句，按SubmissionEntity的主键分组，确保分页基于去重后的Submission实体进行。

修改后的Repository代码 #

@Repository
public interface SubmissionPageRepository extends PagingAndSortingRepository<SubmissionEntity, UUID> {
    @Query("SELECT s FROM SubmissionEntity s WHERE s.conferenceId = :conferenceId ORDER BY s.orderNumber")
    Page<SubmissionEntity> searchByConferenceIdOrderByOrderNumber(@Param("conferenceId") Long conferenceId, Pageable pageable);

    @Query("SELECT s FROM SubmissionEntity s JOIN s.contacts c WHERE s.conferenceId = :conferenceId AND LOWER(c.surname) LIKE LOWER(CONCAT(:query, '%')) GROUP BY s.id")
    Page<SubmissionEntity> findByConferenceIdAndContactsSurnameStartsWithIgnoreCase(@Param("conferenceId") Long conferenceId, @Param("query") String query, Pageable pageable);
}

通过添加GROUP BY s.id，让查询结果先按Submission主键去重，再执行分页操作，即可保证分页结果的准确性。

内容的提问来源于stack exchange，提问作者VOJAKIS