JPQL查询报错：条件上下文指定非布尔类型表达式，求解决方案

报错原因 #

当传入多值列表（如genderList包含Male、Female）时，JPQL会将:genderList替换为('Male','Female')，原条件((:genderList) IS NULL OR ...)会变成(('Male','Female') IS NULL OR ...)。这种写法在SQL中非法——括号内是多值集合，无法直接用IS NULL判断，因此数据库抛出"非布尔类型表达式用于条件上下文"的错误。

修复方案 #

修改列表参数的判断逻辑，用COALESCE结合SIZE()函数处理列表为null或空集合的场景，避免非法语法：

@Query("""
            SELECT customer_receiver
            FROM SubsidyCustomerUserReceiverEntity customer_receiver
            WHERE customer_receiver.deleted = false
                AND (:isPovertyLineCheck IS NULL OR :isPovertyLineCheck = false OR customer_receiver.isEligible = :isPovertyLineCheck)
                AND (:incomeLowerLimit IS NULL OR :incomeUpperLimit IS NULL OR
                            customer_receiver.receiverGrossHouseholdIncomeAmount BETWEEN :incomeLowerLimit AND :incomeUpperLimit)
                AND (:ageLowerLimit IS NULL OR :ageUpperLimit IS NULL OR
                            customer_receiver.ageYear BETWEEN :ageLowerLimit AND :ageUpperLimit)
                AND (COALESCE(SIZE(:locationList), 0) = 0 OR customer_receiver.area IN (:locationList))
                AND (COALESCE(SIZE(:employmentTypeList), 0) = 0 OR customer_receiver.employmentType IN (:employmentTypeList))
                AND (COALESCE(SIZE(:occupationList), 0) = 0 OR customer_receiver.incomeSourceOrOccupationName IN (:occupationList))
                AND (COALESCE(SIZE(:genderList), 0) = 0 OR customer_receiver.gender IN (:genderList))
            """)
    List<SubsidyCustomerUserReceiverEntity> getEligibleCustomerReceiver(BigDecimal incomeLowerLimit,
            BigDecimal incomeUpperLimit, Integer ageLowerLimit, Integer ageUpperLimit, Boolean isPovertyLineCheck,
            List<String> locationList, List<EmploymentType> employmentTypeList, List<String> occupationList,
            List<String> genderList);

逻辑说明 #

• SIZE(:listParam)：获取列表参数的元素数量，若列表为null则返回null
• COALESCE(SIZE(:listParam), 0)：将null转为0，统一处理列表为null或空集合的情况
• 当列表元素数量为0时，跳过该条件；否则执行IN匹配

替代方案（可选） #

若使用Hibernate等支持IS EMPTY的JPA实现，也可以直接写：

AND ((:locationList IS NULL OR :locationList IS EMPTY) OR customer_receiver.area IN (:locationList))

但COALESCE+SIZE的写法兼容性更强，适配更多JPA版本。

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