JPQL WHERE子句CASE语句失效，引发Bean创建失败问题排查

问题场景 #

编写了如下JPQL查询方法，在WHERE子句中添加CASE语句后出现Bean创建失败错误，查询无法正常运行：

@Query("SELECT m from BankAccount m " +
        "inner join BankBranch br on br.id = m.bankBranch.id " +
        "inner join Bank bn on bn.id = m.bank.id " +
        "WHERE m.status = true " +
        "and (lower(m.accountNo) LIKE %:search% " +
        "or lower(br.branchName) LIKE %:search% " +
        "or lower(bn.name) LIKE %:search%) " +
        "AND case "+
        " when :featureName = 'Staff Advance Register' then m.isStaffAdvanceRegister = true "+
        " when :featureName = 'Staff Bill Payment' then m.isStaffBillPayment = true "+
        " when :featureName = 'Final Settlement' then is_final_settlement = true "+
        " when :featureName = 'Vendor Payment' then is_vendor_payment = true "+
        " when :featureName = 'BKash' then is_bkash = true "+
        " when :featureName = 'Brac Security Money Deposit' then is_brac_security_money_deposit = true "+
        " when :featureName = 'Fund Receive' then is_fund_receive = true "+
        " when :featureName = 'Foreign Currency Register' then is_fc_register = true "+
        " END "
)
Page<BankAccount> getBankAccountListByFeatureName(@Param("featureName") String featureName,@Param("search") String search, Pageable pageable);

问题原因 #

1. CASE表达式语法不符合JPQL规范
   JPQL的CASE表达式在WHERE子句中需要返回一个可比较的值，不能直接在THEN后写条件判断语句（如m.isStaffAdvanceRegister = true）。正确逻辑应该是让CASE返回对应字段的布尔值，再与true做比较。

2. 部分字段缺少实体别名
   代码中is_final_settlement、is_vendor_payment等字段没有指定所属实体的别名m.，JPQL解析时无法识别这些字段属于BankAccount实体，导致语法解析失败。

修复方案 #

方案一：使用OR连接条件分支（更直观） #

将CASE逻辑替换为多个OR连接的条件判断，每个分支对应featureName匹配后的字段校验：

@Query("SELECT m from BankAccount m " +
        "inner join BankBranch br on br.id = m.bankBranch.id " +
        "inner join Bank bn on bn.id = m.bank.id " +
        "WHERE m.status = true " +
        "and (lower(m.accountNo) LIKE %:search% " +
        "or lower(br.branchName) LIKE %:search% " +
        "or lower(bn.name) LIKE %:search%) " +
        "AND (" +
        "   (:featureName = 'Staff Advance Register' AND m.isStaffAdvanceRegister = true) " +
        "   OR (:featureName = 'Staff Bill Payment' AND m.isStaffBillPayment = true) " +
        "   OR (:featureName = 'Final Settlement' AND m.is_final_settlement = true) " +
        "   OR (:featureName = 'Vendor Payment' AND m.is_vendor_payment = true) " +
        "   OR (:featureName = 'BKash' AND m.is_bkash = true) " +
        "   OR (:featureName = 'Brac Security Money Deposit' AND m.is_brac_security_money_deposit = true) " +
        "   OR (:featureName = 'Fund Receive' AND m.is_fund_receive = true) " +
        "   OR (:featureName = 'Foreign Currency Register' AND m.is_fc_register = true) " +
        ") "
)
Page<BankAccount> getBankAccountListByFeatureName(@Param("featureName") String featureName,@Param("search") String search, Pageable pageable);

方案二：修正CASE表达式写法 #

调整CASE语句，让其返回对应字段的布尔值，再与true比较，同时补全所有字段的实体别名：

@Query("SELECT m from BankAccount m " +
        "inner join BankBranch br on br.id = m.bankBranch.id " +
        "inner join Bank bn on bn.id = m.bank.id " +
        "WHERE m.status = true " +
        "and (lower(m.accountNo) LIKE %:search% " +
        "or lower(br.branchName) LIKE %:search% " +
        "or lower(bn.name) LIKE %:search%) " +
        "AND CASE :featureName " +
        "   WHEN 'Staff Advance Register' THEN m.isStaffAdvanceRegister " +
        "   WHEN 'Staff Bill Payment' THEN m.isStaffBillPayment " +
        "   WHEN 'Final Settlement' THEN m.is_final_settlement " +
        "   WHEN 'Vendor Payment' THEN m.is_vendor_payment " +
        "   WHEN 'BKash' THEN m.is_bkash " +
        "   WHEN 'Brac Security Money Deposit' THEN m.is_brac_security_money_deposit " +
        "   WHEN 'Fund Receive' THEN m.is_fund_receive " +
        "   WHEN 'Foreign Currency Register' THEN m.is_fc_register " +
        "END = true "
)
Page<BankAccount> getBankAccountListByFeatureName(@Param("featureName") String featureName,@Param("search") String search, Pageable pageable);

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