升级spring-data-jpa 3.0.0后Pageable JPQL查询结果不符问题

问题现象 #

升级至spring-data-jpa 3.0.0版本后，使用Pageable的JPQL查询返回的结果数量低于预期：

• 直接执行控制台输出的查询SQL能得到正确数量的结果
• 框架未自动生成对应的count SQL查询，导致分页的总条数计算错误，进而影响分页逻辑

涉及的JPQL查询 #

@Query(value = "select fsd from FeeScheduleDrugEntity fsd "
    + "left join DrugNdcEntity ndc on fsd.drug.id = ndc.drug.id and fsd.drug.noc = true "
    + "left join FeeScheduleSourceEntity fsse on fsse.id = fsd.drugFeeScheduleSource.id "
    + "where fsd.feeSchedule.id = :feeScheduleId ")

框架生成的查询SQL #

select f1_0.fee_schedule_drug_id,
f1_0.allowable_per_billing_unit,
f1_0.fee_schedule_drug_source,
f1_0.created_at,
f1_0.created_by,
f1_0.drug_id,
f1_0.fee_schedule_item_source_id,
f1_0.fee_schedule_id,
f1_0.modified_at,
f1_0.modified_by
from core.fee_schedule_drug f1_0
join core.drug d2_0 on d2_0.drug_id = f1_0.drug_id
left join core.drug_ndc d1_0 on f1_0.drug_id = d1_0.drug_id and d2_0.is_noc = true
left join core.fee_schedule_item_source f2_0
on f2_0.fee_schedule_item_source_id = f1_0.fee_schedule_item_source_id
where f1_0.fee_schedule_id=?
order by d1_0.ndc asc
offset ? rows fetch first ? rows only

原因分析 #

Spring Data JPA 3.0.0对复杂JPQL（包含带关联属性条件的left join）的count查询生成逻辑做了调整，当join条件中涉及主实体的关联属性（如fsd.drug.noc = true）时，框架无法正确解析并生成count SQL，转而可能使用当前页的结果数量作为总计数，导致分页信息错误。

解决方案 #

方案1：手动指定countQuery #

在@Query注解中显式添加countQuery参数，明确写出统计总条数的JPQL，避免框架自动生成出错：

@Query(value = "select fsd from FeeScheduleDrugEntity fsd "
    + "left join DrugNdcEntity ndc on fsd.drug.id = ndc.drug.id and fsd.drug.noc = true "
    + "left join FeeScheduleSourceEntity fsse on fsse.id = fsd.drugFeeScheduleSource.id "
    + "where fsd.feeSchedule.id = :feeScheduleId ",
    countQuery = "select count(fsd) from FeeScheduleDrugEntity fsd "
    + "left join DrugNdcEntity ndc on fsd.drug.id = ndc.drug.id and fsd.drug.noc = true "
    + "left join FeeScheduleSourceEntity fsse on fsse.id = fsd.drugFeeScheduleSource.id "
    + "where fsd.feeSchedule.id = :feeScheduleId ")

注：若统计的是主实体FeeScheduleDrugEntity的数量，且left join不会导致主实体记录重复，可简化countQuery，去掉left join部分，提升性能：

countQuery = "select count(fsd) from FeeScheduleDrugEntity fsd "
    + "where fsd.feeSchedule.id = :feeScheduleId and fsd.drug.noc = true "

方案2：优化JPQL的join条件 #

如果业务逻辑允许，将join中的条件fsd.drug.noc = true移至where子句，简化框架解析难度，使其能自动生成正确的count SQL：

@Query(value = "select fsd from FeeScheduleDrugEntity fsd "
    + "left join DrugNdcEntity ndc on fsd.drug.id = ndc.drug.id "
    + "left join FeeScheduleSourceEntity fsse on fsse.id = fsd.drugFeeScheduleSource.id "
    + "where fsd.feeSchedule.id = :feeScheduleId and fsd.drug.noc = true ")

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