构建Spring Data JPA Specification时HAVING子句空值校验优化方法

原有参考JPQL实现 #

@Query("SELECT DISTINCT h, SUM(m.annualIncome) " +
        "FROM Household h LEFT JOIN h.familyMemberList m " +
        "GROUP BY h.id " +
        "HAVING SUM(m.annualIncome) < 100000 " +
        "AND (:householdSize IS NULL OR COUNT(m) = :householdSize) " +
        "AND (:householdIncome IS NULL OR SUM(m.annualIncome) = :householdIncome)")
List<Household> findGrantEligibleHouseholds(@Param("householdSize") long householdSize, @Param("householdIncome") long householdIncome);

待优化的冗余Specification实现 #

public static Specification<Household> grantEligibleHouseholdsSpecification(HouseholdCriteria criteria) {
    return Specification.where(
        (root, query, builder) -> {
            List<Predicate> searchCriteria = new ArrayList<>();
            final Join<Household, FamilyMember> householdFamilyMemberJoin = root.join(Household_.familyMemberList, JoinType.LEFT);
            if(criteria.getHousingType() != null) {
                searchCriteria.add(builder.equal(root.get(Household_.housingType), criteria.getHousingType()));
            }
            query.groupBy(root.get(Household_.id));
            if(criteria.getHouseholdIncome() != null && criteria.getHouseholdSize() != null) {
                query.having(builder.lt(builder.sum(householdFamilyMemberJoin.get(FamilyMember_.annualIncome)),100000)
                        ,builder.equal(builder.count(householdFamilyMemberJoin),criteria.getHouseholdSize())
                        ,builder.equal(builder.sum(householdFamilyMemberJoin.get(FamilyMember_.annualIncome)),criteria.getHouseholdIncome()));
            }
            else if(criteria.getHouseholdIncome() != null) {
                query.having(builder.lt(builder.sum(householdFamilyMemberJoin.get(FamilyMember_.annualIncome)),100000)
                        ,builder.equal(builder.sum(householdFamilyMemberJoin.get(FamilyMember_.annualIncome)),criteria.getHouseholdIncome() ));
            }
            else if(criteria.getHouseholdSize() != null) {
                query.having(builder.lt(builder.sum(householdFamilyMemberJoin.get(FamilyMember_.annualIncome)),100000)
                        ,builder.equal(builder.count(householdFamilyMemberJoin),criteria.getHouseholdSize()));
            }
            else {
                query.having(builder.lt(builder.sum(householdFamilyMemberJoin.get(FamilyMember_.annualIncome)),100000));
            }
            query.multiselect();
            return builder.and(searchCriteria.toArray(new Predicate[searchCriteria.size()]));
        }
    );
}

优化思路 #

核心逻辑是把HAVING子句的动态条件和WHERE子句的动态条件用统一的动态列表收集模式处理，彻底消除多分支判断的冗余代码：

• 提前抽取重复使用的关联关系、聚合函数表达式，减少重复编码
• 固定必填的HAVING条件直接加入谓词列表，可选条件仅需做一次非空判断，满足条件就加入列表
• 最后统一将谓词列表转为数组传给查询对象即可，无需针对条件组合编写多分支逻辑

优化后代码 #

public static Specification<Household> grantEligibleHouseholdsSpecification(HouseholdCriteria criteria) {
    return Specification.where((root, query, builder) -> {
        List<Predicate> wherePredicates = new ArrayList<>();
        List<Predicate> havingPredicates = new ArrayList<>();
        // 提前抽取关联和常用聚合表达式，避免重复编写
        final Join<Household, FamilyMember> memberJoin = root.join(Household_.familyMemberList, JoinType.LEFT);
        Expression<Long> incomeSum = builder.sum(memberJoin.get(FamilyMember_.annualIncome));
        Expression<Long> memberCount = builder.count(memberJoin);

        // WHERE条件动态拼接
        if (criteria.getHousingType() != null) {
            wherePredicates.add(builder.equal(root.get(Household_.housingType), criteria.getHousingType()));
        }

        // HAVING条件动态拼接
        // 固定必填条件：家庭总收入低于10万
        havingPredicates.add(builder.lt(incomeSum, 100000));
        // 可选条件：家庭人数匹配
        if (criteria.getHouseholdSize() != null) {
            havingPredicates.add(builder.equal(memberCount, criteria.getHouseholdSize()));
        }
        // 可选条件：家庭总收入匹配
        if (criteria.getHouseholdIncome() != null) {
            havingPredicates.add(builder.equal(incomeSum, criteria.getHouseholdIncome()));
        }

        // 配置查询参数
        query.groupBy(root.get(Household_.id));
        query.having(havingPredicates.toArray(new Predicate[0]));
        query.distinct(true);

        return builder.and(wherePredicates.toArray(new Predicate[0]));
    });
}

后续扩展方式 #

后续新增查询条件无需修改原有逻辑，仅需对应添加判断即可：

• 新增WHERE条件：在WHERE条件拼接块增加非空判断，将生成的谓词加入wherePredicates列表
• 新增HAVING条件：在HAVING条件拼接块增加非空判断，将生成的谓词加入havingPredicates列表

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