sql - 在Spring数据JPA查询中检查List参数是否为空

Question

我有一个 Spring Boot 应用程序，并使用 Spring Data JPA 来查询 MySQL 数据库。

我需要获取使用某些参数过滤的类(class)列表。

我通常使用语法param IS NULL or (/*do something with param*/)，以便它为null时会忽略该参数。

使用简单的数据类型，我没有问题，但是当涉及对象列表时，我不知道如何检查NULL值。在以下查询中，如何检查?3参数是否为NULL？

@Query("SELECT DISTINCT c FROM Course c\n" +
       "WHERE c.courseDate < CURRENT_TIME\n" +
       "AND (?1 IS NULL OR (c.id NOT IN (3, 4, 5)))\n" +
       "AND (?2 IS NULL OR (c.title LIKE ?2 OR c.description LIKE ?2))\n" +
       "AND ((?3) IS NULL OR (c.category IN ?3)) ")
List<Course> getCoursesFiltered(Long courseId, String filter, List<Category> categories);

错误是:

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet[SQL: 1241, 21000]

在堆栈跟踪中，我可以看到:

Caused by: java.sql.SQLException: Operand should contain 1 column(s)

如果我的列表包含3个元素，则确实生成的查询将是 ... AND ((?3, ?4, ?5) IS NULL OR (c.category IN (?3, ?4, ?5)))。但是 IS NULL不能应用于多个元素(如果我的列表仅包含一个元素，则查询工作正常)。

我已经尝试过 size(?3) < 1， length(?3) < 1， (?3) IS EMPTY等，但是还是没有运气。

Answer 1

好吧，尽管我在半夜醒来之后还是想起一件事:

@Query("SELECT DISTINCT c FROM Course c\n" +
       "WHERE c.courseDate < CURRENT_TIME\n" +
       "AND (?1 IS NULL OR (c.id NOT IN (3, 4, 5)))\n" +
       "AND (?2 IS NULL OR (c.title LIKE ?2 OR c.description LIKE ?2))\n" +
       "AND (COALESCE(?3) IS NULL OR (c.category IN ?3)) ")
List<Course> getCoursesFiltered(Long courseId, String filter, List<Category> categories);

解决方案是除了使用 COALESCE之外，还简单地使用 IS NULL，以便它可以使用多个值。这样，如果列表包含至少一个非null值，则第二个表达式( (c.category IN ?3))将完成过滤工作。

下次我至少要等一整夜才问一个问题:)