JPA查询中Coalesce函数无法过滤summa零值的问题咨询

你遇到的是JPA查询关联集合的常见误区，你写的过滤条件不管放在JOIN ON还是WHERE子句，作用都只是筛选满足条件的Kart主实体：只要某个Kart存在至少一条符合过滤规则的Acharge记录，这个Kart就会被返回。而你投影中getAcharge()获取的是该Kart关联的全量Acharge数据，不会继承查询时的过滤条件，所以会出现summa=0.0的记录。

另外还有个小概率的类型兼容问题：Acharge实体中summa是Double类型，而ChargeUsl投影中定义的是BigDecimal，如果数据库中存储的是极小值（比如0.0000000001），转换为BigDecimal时可能被截断显示为0，可以先将Hibernate生成的原生SQL直接在Oracle中执行，确认返回结果里的summa是否真的存在0值，排除类型转换问题。

方案1：查询过滤后的关联记录，手动聚合（最稳定） #

直接查询符合条件的Acharge记录，再在业务层按Kart分组聚合，避免关联集合默认加载全量数据的问题：

1. 定义普通DTO类接收结果

// 外层DTO
@Data
@AllArgsConstructor
public class LskChargeDto {
    private String lsk;
    private String kul;
    private String nd;
    private List<ChargeUslDto> acharge;
}

// 内层费用DTO
@Data
@AllArgsConstructor
public class ChargeUslDto {
    private String mgFrom;
    private String mgTo;
    private String uslId;
    private Double summa;
}

2. 调整JPA查询语句

@Query("select t from Acharge t " +
        "join t.kart k join k.akart a " +
        "where a.mg between t.mgFrom and t.mgTo " +
        "and t.type=1 " +
        "and a.mg in (:periods) " +
        "and coalesce(t.summa,0) <> 0")
List<Acharge> getFilteredAcharges(@Param("periods") List<String> periods);

3. 业务层分组转换

List<Acharge> filteredAcharges = kartDAO.getFilteredAcharges(Arrays.asList("201402", "201403", "201404"));
// 按Kart分组
Map<Kart, List<Acharge>> groupMap = filteredAcharges.stream()
        .collect(Collectors.groupingBy(Acharge::getKart));
// 转换为DTO
List<LskChargeDto> result = groupMap.entrySet().stream().map(entry -> {
    Kart kart = entry.getKey();
    List<ChargeUslDto> uslList = entry.getValue().stream().map(acharge -> 
        new ChargeUslDto(
            acharge.getMgFrom(),
            acharge.getMgTo(),
            acharge.getUsl().getId(),
            acharge.getSumma()
        )
    ).collect(Collectors.toList());
    return new LskChargeDto(kart.getLsk(), kart.getKul(), kart.getNd(), uslList);
}).collect(Collectors.toList());

方案2：使用JOIN FETCH过滤关联集合 #

如果要保留接口投影的写法，可以用JOIN FETCH让关联集合加载时应用过滤条件，加distinct避免主实体重复：

@Query("select distinct k " +
        "from Kart k join k.akart a join fetch k.acharge t " +
        "where a.mg between t.mgFrom and t.mgTo " +
        "and t.type=1 " +
        "and a.mg in (:periods) " +
        "and coalesce(t.summa,0) <> 0")
List<LskCharge> getChargesByLsk(@Param("periods") List<String> periods);

方案3：用Hibernate过滤器实现全局关联过滤 #

如果需要在多个查询场景下都过滤summa=0的Acharge，可以用Hibernate自带的过滤器：

1. 在Acharge类上定义过滤器

@FilterDef(name = "filterZeroSumma", defaultCondition = "nvl(summa,0) <> 0")
@Entity
@Table(name = "A_CHARGE2", schema="SCOTT")
@Getter @Setter
public class Acharge implements java.io.Serializable, Compress {
    // 原有代码
}

2. 在Kart的关联集合上绑定过滤器

@OneToMany(mappedBy = "kart", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
@Filter(name = "filterZeroSumma")
private List<Acharge> acharge = new ArrayList<>(0);

3. 查询前开启过滤器

@Autowired
private EntityManager entityManager;

// 执行查询前开启过滤器
entityManager.unwrap(Session.class).enableFilter("filterZeroSumma");
List<LskCharge> lst = kartDAO.getChargesByLsk(Arrays.asList("201402", "201403", "201404"));
// 后续处理逻辑不变

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