SpringBoot JPA自定义查询含可空IN子句，多值时Oracle报错求助

问题复现 #

在Spring Boot应用中使用JPA编写自定义查询，包含可为null的列表过滤器，代码如下：

@Query( "SELECT new com.mypackage.model.CustomOutput( AVG(t.time) ) " +
        "FROM MyTable t WHERE " +
        "t.time IS NOT NULL AND t.updatedTime > :after AND t.updatedTime < :before " +
        "AND " +
        "( (:filterA) IS NULL OR t.columnA IN (:filterA) ) AND " +
        "( (:filterB) IS NULL OR t.columnB IN (:filterB) ) AND " +
        "( (:filterC) IS NULL OR t.columnC IN (:filterC) ) AND " +
        "( (:filterD) IS NULL OR t.columnD IN (:filterD) )"
)
CustomOutput findCustomOutput(
        @Param("filterA") List<String> filterA,
        @Param("filterC") List<String> filterC,
        @Param("filterB") List<String> filterB,
        @Param("filterD") List<String> filterD,
        @Param("after") Date after,
        @Param("before") Date before
);

当过滤器为null或单值列表时查询正常，但传入多值列表时抛出Oracle语法错误：

2024-06-28 12:32:32,543 [collectorsCluster_Worker-1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 920, SQLState: 42000
2024-06-28 12:32:32,544 [collectorsCluster_Worker-1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-00920: invalid relational operator

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    ...（堆栈信息省略）

错误原因 #

问题出在JPQL中对集合参数的IS NULL判断逻辑。当传入多值List时，Hibernate会将(:filterX) IS NULL转换为类似('val1','val2') IS NULL的SQL语句，而Oracle不支持对行值表达式直接使用IS NULL运算符，因此触发ORA-00920语法错误。

解决方案 #

方法1：修改JPQL判断逻辑，结合空集合检查 #

将原有的(:filterX) IS NULL判断替换为(:filterX) IS NULL OR :filterX IS EMPTY，同时保留IN子句逻辑，确保参数为null或空集合时都能忽略过滤条件：

@Query( "SELECT new com.mypackage.model.CustomOutput( AVG(t.time) ) " +
        "FROM MyTable t WHERE " +
        "t.time IS NOT NULL AND t.updatedTime > :after AND t.updatedTime < :before " +
        "AND " +
        "( (:filterA) IS NULL OR :filterA IS EMPTY OR t.columnA IN (:filterA) ) AND " +
        "( (:filterB) IS NULL OR :filterB IS EMPTY OR t.columnB IN (:filterB) ) AND " +
        "( (:filterC) IS NULL OR :filterC IS EMPTY OR t.columnC IN (:filterC) ) AND " +
        "( (:filterD) IS NULL OR :filterD IS EMPTY OR t.columnD IN (:filterD) )"
)
CustomOutput findCustomOutput(
        @Param("filterA") List<String> filterA,
        @Param("filterC") List<String> filterC,
        @Param("filterB") List<String> filterB,
        @Param("filterD") List<String> filterD,
        @Param("after") Date after,
        @Param("before") Date before
);

方法2：Java层处理null参数，统一传入空集合 #

在调用DAO方法前，将所有null的List参数替换为空集合，这样JPQL只需检查集合是否为空：

1. 调用处处理参数：

List<String> processedFilterA = Optional.ofNullable(filterA).orElse(Collections.emptyList());
// 同理处理filterB、filterC、filterD
customRepository.findCustomOutput(processedFilterA, processedFilterC, processedFilterB, processedFilterD, after, before);

2. 修改JPQL查询：

@Query( "SELECT new com.mypackage.model.CustomOutput( AVG(t.time) ) " +
        "FROM MyTable t WHERE " +
        "t.time IS NOT NULL AND t.updatedTime > :after AND t.updatedTime < :before " +
        "AND " +
        "( :filterA IS EMPTY OR t.columnA IN (:filterA) ) AND " +
        "( :filterB IS EMPTY OR t.columnB IN (:filterB) ) AND " +
        "( :filterC IS EMPTY OR t.columnC IN (:filterC) ) AND " +
        "( :filterD IS EMPTY OR t.columnD IN (:filterD) )"
)
CustomOutput findCustomOutput(
        @Param("filterA") List<String> filterA,
        @Param("filterC") List<String> filterC,
        @Param("filterB") List<String> filterB,
        @Param("filterD") List<String> filterD,
        @Param("after") Date after,
        @Param("before") Date before
);

两种方法都能避免Oracle对行值表达式的无效判断，解决ORA-00920错误。

内容的提问来源于stack exchange，提问作者SRI HARSHA S V S