Spring原生查询绑定Null值遇ORA-00932错误的解决咨询

问题场景 #

在Spring Boot应用中使用Spring Data JPA执行原生Oracle查询时抛出java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY错误，但相同查询在IntelliJ数据库控制台能正常运行。

相关代码 #

@Query(value = "SELECT TRUNC(p.CREATION_DATE), SUM(p.AMOUNT)" +
            " FROM PAYMENT  p" +
            " INNER JOIN FACTOR f" +
            " ON p.PAYMENT_ID = f.FACTOR_ID" +
            " JOIN VEHICLE_GATEWAY v" +
            " ON v.FACTOR_ID = f.FACTOR_ID" +
            " WHERE :debitTypes IS null OR f.FACTOR_TYPE IN (:debitTypes)" +
            " AND p.CREATION_DATE >= :fromPaymentDate" +
            " AND p.CREATION_DATE <= :toPaymentDate" +
            " GROUP BY TRUNC(p.CREATION_DATE)" +
            " ORDER BY TRUNC(p.CREATION_DATE)", nativeQuery = true)
    Object[] getPaidDebtSummary(@Param("debitTypes") List<Integer> debitTypes,
                                @Param("fromPaymentDate") Date fromPaymentDate,
                                @Param("toPaymentDate") Date toPaymentDate);

解决方案 #

1. 修正WHERE子句的括号优先级 #

原查询中OR与AND的逻辑组合未加括号，导致Oracle解析顺序异常，间接引发类型匹配问题。修改后的WHERE子句需将OR条件分组：

WHERE (:debitTypes IS NULL OR f.FACTOR_TYPE IN (:debitTypes))
AND p.CREATION_DATE >= :fromPaymentDate
AND p.CREATION_DATE <= :toPaymentDate

2. 适配Oracle对List参数的处理 #

Oracle JDBC驱动处理IN子句的List参数时，可能会将其转换为二进制类型，导致类型不兼容，可通过两种方式解决：

• 方式一：使用Oracle原生数值列表类型
  将List参数转换为Oracle支持的SYS.ODCINUMBERLIST类型，修改查询条件：

  WHERE (:debitTypes IS NULL OR f.FACTOR_TYPE IN (SELECT COLUMN_VALUE FROM TABLE(:debitTypes)))
  

  需确保Spring Data JPA能将List<Integer>正确转换为该类型，可通过注册自定义类型转换器实现。

• 方式二：使用SpEL表达式处理空List
  针对Spring Data JPA 2.x及以上版本，用:debitTypes IS EMPTY替代IS NULL，框架会自动处理空List场景，避免无效SQL：

  @Query(value = "SELECT TRUNC(p.CREATION_DATE), SUM(p.AMOUNT)" +
            " FROM PAYMENT  p" +
            " INNER JOIN FACTOR f" +
            " ON p.PAYMENT_ID = f.FACTOR_ID" +
            " JOIN VEHICLE_GATEWAY v" +
            " ON v.FACTOR_ID = f.FACTOR_ID" +
            " WHERE (:debitTypes IS EMPTY OR f.FACTOR_TYPE IN (:debitTypes))" +
            " AND p.CREATION_DATE >= :fromPaymentDate" +
            " AND p.CREATION_DATE <= :toPaymentDate" +
            " GROUP BY TRUNC(p.CREATION_DATE)" +
            " ORDER BY TRUNC(p.CREATION_DATE)", nativeQuery = true)
  Object[] getPaidDebtSummary(@Param("debitTypes") List<Integer> debitTypes,
                              @Param("fromPaymentDate") Date fromPaymentDate,
                              @Param("toPaymentDate") Date toPaymentDate);
  

3. 校验字段与参数类型匹配 #

确认数据库中f.FACTOR_TYPE的字段类型与List<Integer>一致，若字段为VARCHAR2类型，需将参数改为List<String>，或在查询中添加类型转换：

f.FACTOR_TYPE IN (TO_NUMBER(:debitTypes))

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