如何在JPA查询中处理空/Null列表参数，返回全部元素？

要实现当传入的List参数为null或空列表时，跳过对应维度的筛选，只返回符合日期条件的结果，有两种常用处理方式，直接看方案：

方案一：修改现有@Query的JPQL语句 #

直接在每个IN筛选条件前增加空值判断逻辑，当参数为null或空列表时，该条件自动生效（相当于跳过筛选），否则执行IN匹配。修改后的代码如下：

@Query("SELECT new ReasonCodeCounter(credittype, reasontext, reasoncode, decisionresult, SUM(total))\r\n"+
"FROM ReasonCodeCounter\r\n"+
"WHERE createdate >= :startDate AND createdate <= :endDate\r\n"+
"AND (:credittypeVAR IS NULL OR :credittypeVAR IS EMPTY OR credittype IN (:credittypeVAR))\r\n"+
"AND (:decisionresultVAR IS NULL OR :decisionresultVAR IS EMPTY OR decisionresult IN (:decisionresultVAR))\r\n"+
"AND (:valoracaopropostaVAR IS NULL OR :valoracaopropostaVAR IS EMPTY OR valoracaoproposta IN (:valoracaopropostaVAR))\r\n"+
"AND (:produtoVAR IS NULL OR :produtoVAR IS EMPTY OR produto IN (:produtoVAR))\r\n"+
"AND (:regiaobalcaoVAR IS NULL OR :regiaobalcaoVAR IS EMPTY OR regiaobalcao IN (:regiaobalcaoVAR))\r\n"+
"AND (:ctransorigidVAR IS NULL OR :ctransorigidVAR IS EMPTY OR ctransorigid IN (:ctransorigidVAR))\r\n"+
"GROUP BY credittype, reasontext, reasoncode, decisionresult")
List<ReasonCodeCounterParent> getOccurrencesOfReasonCodesFilters(LocalDate startDate, LocalDate endDate,
List<String> credittypeVAR, List<String> decisionresultVAR, List<String> valoracaopropostaVAR, List<String> produtoVAR,
List<String> regiaobalcaoVAR, List<String> ctransorigidVAR);

逻辑说明 #

• :参数名 IS NULL：处理参数传入null的场景
• :参数名 IS EMPTY：处理参数传入空列表（size=0）的场景
• 只要满足上述任一条件，该维度的筛选就会被跳过；只有当参数非空且不为null时，才会执行IN匹配

方案二：用Spring Data JPA动态查询（Specification） #

如果后续筛选条件可能变动，用动态查询更灵活，无需硬写冗长的JPQL：

1. 让你的Repository继承JpaSpecificationExecutor<ReasonCodeCounter>：

public interface ReasonCodeCounterRepository extends JpaRepository<ReasonCodeCounter, Long>, JpaSpecificationExecutor<ReasonCodeCounter> {
}

2. 编写动态查询逻辑：

public class ReasonCodeCounterSpecs {
    public static Specification<ReasonCodeCounter> filterByDatesAndParams(LocalDate startDate, LocalDate endDate,
                                                                           List<String> credittypeVAR, List<String> decisionresultVAR,
                                                                           List<String> valoracaopropostaVAR, List<String> produtoVAR,
                                                                           List<String> regiaobalcaoVAR, List<String> ctransorigidVAR) {
        return (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            // 固定日期条件
            predicates.add(cb.between(root.get("createdate"), startDate, endDate));
            
            // 逐个判断参数，非空则添加筛选条件
            if (credittypeVAR != null && !credittypeVAR.isEmpty()) {
                predicates.add(root.get("credittype").in(credittypeVAR));
            }
            if (decisionresultVAR != null && !decisionresultVAR.isEmpty()) {
                predicates.add(root.get("decisionresult").in(decisionresultVAR));
            }
            if (valoracaopropostaVAR != null && !valoracaopropostaVAR.isEmpty()) {
                predicates.add(root.get("valoracaoproposta").in(valoracaopropostaVAR));
            }
            if (produtoVAR != null && !produtoVAR.isEmpty()) {
                predicates.add(root.get("produto").in(produtoVAR));
            }
            if (regiaobalcaoVAR != null && !regiaobalcaoVAR.isEmpty()) {
                predicates.add(root.get("regiaobalcao").in(regiaobalcaoVAR));
            }
            if (ctransorigidVAR != null && !ctransorigidVAR.isEmpty()) {
                predicates.add(root.get("ctransorigid").in(ctransorigidVAR));
            }
            
            // 配置分组和聚合查询，和原JPQL逻辑一致
            query.groupBy(root.get("credittype"), root.get("reasontext"), root.get("reasoncode"), root.get("decisionresult"));
            query.select(cb.construct(ReasonCodeCounter.class, 
                    root.get("credittype"), root.get("reasontext"), root.get("reasoncode"), root.get("decisionresult"),
                    cb.sum(root.get("total"))));
            
            return cb.and(predicates.toArray(new Predicate[0]));
        };
    }
}

3. 在Service中调用：

List<ReasonCodeCounterParent> result = reasonCodeCounterRepository.findAll(
    ReasonCodeCounterSpecs.filterByDatesAndParams(startDate, endDate, credittypeVAR, decisionresultVAR, valoracaopropostaVAR, produtoVAR, regiaobalcaoVAR, ctransorigidVAR)
);

优势 #

• 条件判断更直观，避免JPQL中嵌套的复杂逻辑
• 后续新增/修改筛选条件时，只需调整Specification代码，无需修改注解内的JPQL

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