JPA查询分页返回每页元素数量不符问题排查

问题根源 #

你的多表JOIN查询产生了重复结果行（比如一个用户对应多条KYC/钱包记录），setFirstResult和setMaxResults是基于数据库返回的原始行数分页，而非去重后的DTO数量。当你设置页大小10时，数据库返回的前10行里包含重复数据，最终转换为DTO后只剩8个唯一条目，导致显示数量不符。

解决方案 #

方案1：先分页查询主表ID，再关联查详情 #

先获取分页后的用户ID列表，再根据ID查询完整DTO，避免关联导致的分页混乱：

// 第一步：分页查询customerId列表
Query idQuery = entityManager.createQuery("SELECT cp.customerId FROM CustomerProfile cp " +
        "JOIN CustomerAuthModel ca ON cp.customerId=ca.customerId " +
        "JOIN CustomerWalletModel cw ON cp.customerId = cw.customerId " +
        "JOIN CustomerKycModel ck ON ck.customerId = cw.customerId " +
        "ORDER BY ck.creationDateTime DESC");
idQuery.setFirstResult((pageNumber-1)*pageSize);
idQuery.setMaxResults(pageSize);
List<Long> customerIds = idQuery.getResultList();

// 第二步：根据ID查询完整DTO
List<CustomerReportResponseDTO> customerReportLst = Collections.emptyList();
if(!customerIds.isEmpty()){
    Query dtoQuery = entityManager.createQuery("SELECT new com.one97.one97pay.web.dto.CustomerReportResponseDTO(" +
            "cw.walletId, cw.balance, cw.isEnabled,ca.isEnabled,ca.accountLockType," +
            "ck.isEnabled AS kycStatus,cp.mobile,cp.residentId,cp.ridExpiryDate, ck.kycStatus,ck.kycType,ca.createdOn," +
            "TRIM(UPPER(CONCAT (cp.firstName,CASE WHEN cp.middelName is NULL THEN '' ELSE CONCAT(' ', cp.middelName) END," +
            "CASE WHEN cp.lastName is NULL THEN '' ELSE CONCAT(' ', cp.lastName) END ))), ck.creationDateTime)" +
            " FROM CustomerProfile cp JOIN CustomerAuthModel ca ON cp.customerId=ca.customerId " +
            "JOIN CustomerWalletModel cw ON cp.customerId = cw.customerId " +
            "JOIN CustomerKycModel ck ON ck.customerId = cw.customerId " +
            "WHERE cp.customerId IN :customerIds " +
            "ORDER BY ck.creationDateTime DESC");
    dtoQuery.setParameter("customerIds", customerIds);
    customerReportLst = dtoQuery.getResultList();
}

方案2：使用DISTINCT去重后分页 #

在SELECT语句中添加DISTINCT关键字，确保数据库返回唯一行，再进行分页。注意需要保证DTO的equals()和hashCode()方法正确实现，或者数据库层面能识别重复行：

Query query = entityManager.createQuery("SELECT DISTINCT new com.one97.one97pay.web.dto.CustomerReportResponseDTO(" +
        "cw.walletId, cw.balance, cw.isEnabled,ca.isEnabled,ca.accountLockType," +
        "ck.isEnabled AS kycStatus,cp.mobile,cp.residentId,cp.ridExpiryDate, ck.kycStatus,ck.kycType,ca.createdOn," +
        "TRIM(UPPER(CONCAT (cp.firstName,CASE WHEN cp.middelName is NULL THEN '' ELSE CONCAT(' ', cp.middelName) END," +
        "CASE WHEN cp.lastName is NULL THEN '' ELSE CONCAT(' ', cp.lastName) END ))), ck.creationDateTime)" +
        " FROM CustomerProfile cp JOIN CustomerAuthModel ca ON cp.customerId=ca.customerId " +
        "JOIN CustomerWalletModel cw ON cp.customerId = cw.customerId " +
        "JOIN CustomerKycModel ck ON ck.customerId = cw.customerId" +
        " ORDER BY ck.creationDateTime DESC");
int pageSize = requestDto.getPageSize();
int pageNumber = requestDto.getPageNum();
query.setFirstResult((pageNumber-1) * pageSize); 
query.setMaxResults(pageSize);
List<CustomerReportResponseDTO> customerReportLst = query.getResultList();

方案3：使用Spring Data JPA的Pageable接口 #

如果项目使用Spring Data JPA，直接用Pageable和Page接口，框架会自动处理分页逻辑：

1. 定义Repository方法：

@Repository
public interface CustomerProfileRepository extends JpaRepository<CustomerProfile, Long> {
    @Query("SELECT DISTINCT new com.one97.one97pay.web.dto.CustomerReportResponseDTO(" +
            "cw.walletId, cw.balance, cw.isEnabled,ca.isEnabled,ca.accountLockType," +
            "ck.isEnabled AS kycStatus,cp.mobile,cp.residentId,cp.ridExpiryDate, ck.kycStatus,ck.kycType,ca.createdOn," +
            "TRIM(UPPER(CONCAT (cp.firstName,CASE WHEN cp.middelName is NULL THEN '' ELSE CONCAT(' ', cp.middelName) END," +
            "CASE WHEN cp.lastName is NULL THEN '' ELSE CONCAT(' ', cp.lastName) END ))), ck.creationDateTime)" +
            " FROM CustomerProfile cp JOIN CustomerAuthModel ca ON cp.customerId=ca.customerId " +
            "JOIN CustomerWalletModel cw ON cp.customerId = cw.customerId " +
            "JOIN CustomerKycModel ck ON ck.customerId = cw.customerId" +
            " ORDER BY ck.creationDateTime DESC")
    Page<CustomerReportResponseDTO> findCustomerReports(Pageable pageable);
}

2. 调用方法：

int pageSize = requestDto.getPageSize();
int pageNumber = requestDto.getPageNum();
Pageable pageable = PageRequest.of(pageNumber-1, pageSize, Sort.by(Sort.Direction.DESC, "ck.creationDateTime"));
Page<CustomerReportResponseDTO> page = customerProfileRepository.findCustomerReports(pageable);
List<CustomerReportResponseDTO> customerReportLst = page.getContent();

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