JPQL查询规避空值参数时触发SQL Server语法异常排查

问题重现 #

我写了一个Spring Data JPA的查询方法，想实现空参数自动忽略的逻辑，代码如下：

@Transactional
@Query(value = "select a from ItemAdditionalInfo a where (coalesce(:itemNbrs) is null or a.itemNbr in (:itemNbrs)) and (coalesce(:deptNbr) is null or a.deptNbr in (:deptNbr)) and a.tenantId=(:tenantId)")
List<ItemAdditionalInfo> paramTest(@Param("itemNbrs")List<Integer> itemNbrs,@Param("deptNbr")List<Integer> deptNbr,@Param("tenantId") String tenantId);

执行时直接抛出SQL语法错误：

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.

问题根源 #

coalesce(:itemNbrs)这种写法完全错误——coalesce是用来处理单个值空值替换的函数，而:itemNbrs是List类型参数，最终会被解析成(1,2,3)这种格式，导致SQL变成coalesce((1,2,3))，这在SQL Server里属于非法语法，自然报错。

可行解决方案 #

方案1：修改JPQL语句，用集合空判断关键字 #

JPQL自带is empty关键字用来判断集合是否为空，直接替换原来的coalesce逻辑就行，调整后的代码：

@Transactional
@Query(value = "select a from ItemAdditionalInfo a " +
        "where (:itemNbrs is empty or a.itemNbr in (:itemNbrs)) " +
        "and (:deptNbr is empty or a.deptNbr in (:deptNbr)) " +
        "and a.tenantId = :tenantId")
List<ItemAdditionalInfo> paramTest(@Param("itemNbrs") List<Integer> itemNbrs,
                                   @Param("deptNbr") List<Integer> deptNbr,
                                   @Param("tenantId") String tenantId);

这个写法是JPQL标准语法，能被正确解析成对应SQL，不会有语法问题。

方案2：用Specification动态构建查询 #

如果参数多、逻辑复杂，用动态查询更灵活，还能避免硬写JPQL的麻烦：
首先写一个Specification工具类：

public class ItemAdditionalInfoSpecs {
    public static Specification<ItemAdditionalInfo> withParams(List<Integer> itemNbrs, List<Integer> deptNbr, String tenantId) {
        return (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            // 租户ID是必传参数，直接加条件
            predicates.add(cb.equal(root.get("tenantId"), tenantId));
            // itemNbrs不为空才加过滤条件
            if (itemNbrs != null && !itemNbrs.isEmpty()) {
                predicates.add(root.get("itemNbr").in(itemNbrs));
            }
            // deptNbr不为空才加过滤条件
            if (deptNbr != null && !deptNbr.isEmpty()) {
                predicates.add(root.get("deptNbr").in(deptNbr));
            }
            return cb.and(predicates.toArray(new Predicate[0]));
        };
    }
}

然后让Repository继承JpaSpecificationExecutor：

public interface ItemAdditionalInfoRepository extends JpaRepository<ItemAdditionalInfo, Long>, JpaSpecificationExecutor<ItemAdditionalInfo> {
}

调用的时候直接传参数就行：

List<ItemAdditionalInfo> result = itemAdditionalInfoRepository.findAll(ItemAdditionalInfoSpecs.withParams(itemNbrs, deptNbr, tenantId));

方案3：原生SQL适配（不推荐，仅作参考） #

如果一定要用原生SQL，得调整空集合的判断逻辑，SQL Server里可以通过判断集合长度来处理：

@Transactional
@Query(value = "select * from ItemAdditionalInfo a " +
        "where ((:itemNbrs is null or (select count(*) from :itemNbrs) = 0) or a.itemNbr in (:itemNbrs)) " +
        "and ((:deptNbr is null or (select count(*) from :deptNbr) = 0) or a.deptNbr in (:deptNbr)) " +
        "and a.tenantId = :tenantId", nativeQuery = true)
List<ItemAdditionalInfo> paramTest(@Param("itemNbrs") List<Integer> itemNbrs,
                                   @Param("deptNbr") List<Integer> deptNbr,
                                   @Param("tenantId") String tenantId);

这个写法依赖SQL Server对集合参数的解析支持，通用性不如前两种方案。

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