JPA原生查询传入多值vendor列表触发SQL语法错误如何解决？

问题根因 #

你遇到的报错是3个问题共同导致的：

• 参数绑定不匹配：DAO方法的形参名为vendorNbr，但SQL语句中使用的占位符是:vendor，没有显式指定参数绑定关系的情况下，JPA无法正确映射参数值。
• 多值参数的空判断语法不兼容：原生SQL中(:参数 IS NULL OR 字段 IN (:参数))的写法仅支持单值参数，当传入多值列表时，占位符会被展开为逗号分隔的多个值，此时(:参数 IS NULL)会变为('294','302' IS NULL)，属于非法的布尔表达式，直接触发你看到的「非布尔类型表达式」报错。单值场景下占位符仅展开为单个字符串，判断语法合法因此可以正常运行。
• SQL语法错误：原SQL的WHERE子句末尾多了多余的AND关键字，GROUP BY子句末尾多了多余的逗号，本身就存在语法问题。

修复方案 #

按以下步骤修改代码即可同时兼容空值、单值、多值参数场景：

1. 给所有方法参数添加@Param注解，显式指定和SQL占位符的绑定关系
2. 修正SQL中的多余语法符号，调整空判断逻辑兼容多值参数
3. 业务层调用方法前做判断，若传入的vendorNbr是空列表，直接赋值为null，避免IN空列表的语法报错

修正后的代码示例 #

@Query(
      value =
          "select sum(ORDER_PALLET_QTY) as PALLETS, " +
          "sum(ORDER_QTY) as UNITS, " +
          "PO_TYPE as POTYPE " +
          "from [ORDER] " +
          "where CAL_DT BETWEEN :fromDate AND :endDate " +
          "AND (:vendorNbr IS NULL OR VENDOR_NBR IN (:vendorNbr)) " +
          "group by PO_TYPE",
      nativeQuery = true)
Optional<List<OrderCubeModelHelper>> getOrders(
      @Param("vendorNbr") List<String> vendorNbr,
      @Param("fromDate") LocalDate fromDate,
      @Param("endDate") LocalDate endDate);

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