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

Question

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

我需要获取一组带有某些参数的课程列表。

通常，我会使用以下语法param IS NULL or (/*对param进行操作*/) 来忽略参数如果它是空值的情况。

对于简单数据类型，我没有问题，但当涉及到对象列表时，我不知道如何检查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);

错误信息是：

无法提取ResultSet; SQL [n/a]; 嵌套异常为org.hibernate.exception.DataException: 无法提取ResultSet[SQL: 1241, 21000]

在堆栈跟踪中，我看到：

Caused by: java.sql.SQLException: 操作数应该包含1列

如果我的列表包含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等方法，但仍然没有成功。

- Yann39

---

你需要多少细粒度的查询控制？如果你正在使用Spring Boot，你考虑过使用基于方法名的自动生成CRUD方法吗？这样你就可以在参数上使用@Nullable注解，它可能会直接工作。请参见https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.nullability - Matt

如果不行的话，我相信在MySQL中使用"where X in (NULL)"是有效的语法，所以我认为你上面的查询应该可以工作。你的问题似乎在于提取ResultSet，而不是运行查询。你能否添加Course类的完整代码以及实体映射和你遇到的错误的完整堆栈跟踪？ - Matt

虽然这可能不是你想要的答案，但最简单的解决方案是拥有两个独立的查询方法，一个检查类别，另一个不检查，并且在Java代码中检查你的类别列表并调用适当的查询的委托方法，基于它是否为null，即如果为null，则根本不包含它在查询中。 - Matt

谢谢Matt，我已经在其他需要的地方使用了基于方法名称的查询，但实际上我在这里谈论的查询已经为示例简化，不能以这种方式完成。顺便说一句，在where X in (NULL)中起作用，而是(?3) IS NULL语句导致了问题。我的Course类和实体映射非常基本，没有什么特别之处。我确实从管理器类调用存储库方法，我可以像你建议的那样做一些技巧，但我相信有更干净的方法来做到这一点 :) - Yann39

哈哈，我在寻找解决方案的时候在spring-data-mongo中发现了这个问题。我找到了一个简洁的方法，使用SpEl使其正常工作。这里是详细的解答。 - undefined

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);

解决方案很简单，只需在IS NULL之外使用COALESCE，这样它就可以处理多个值。 这样，如果列表中至少包含一个非空值，则第二个表达式（(c.category IN ?3)）将执行过滤操作。

下次提问之前，我会等待至少整整一晚上 :)

- Yann39

---

这似乎是一个hack。Hibernate应该提供官方解决方案。 - CDT

@CDT：看起来至少Spring团队正在调查这个问题：请参见DATAJPA-209。 - lu_ko

对我来说，唯一有效的解决方案也是这个。尝试过使用“is empty”、“is null”，但都没有成功。 - lingar

此解决方案适用于MySql，但不能在SQL Server中使用。 - Irfan Nasim

我不得不在Oracle的COALESCE函数中添加第二个值： AND (COALESCE(?3, NULL) IS NULL - Bob Dudan

Answer 2

这并不是严格回答您的问题，但一个简单的解决方案是在您的存储库中有另一种方法，可以在调用查询之前检查您的列表，并将它们默认为带有虚拟值的列表。类似于：

@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 ('DUMMYVALUE' IN ?3 OR (c.category IN ?3)) ")
// make this private to keep it safe
private List<Course> getCoursesFiltered(Long courseId, String filter, List<Category> categories);

// public helper method to put a dummy value in the list that you can check for
public List<Course> getNullableCoursesFiltered(Long courseId, String filter, List<Category> categories) {
    if(categories == null) {
        categories = Arrays.asList("DUMMYVALUE");
    }
    return getCoursesFiltered(courseId, filter, categories);
}

- Matt

---

你如何在存储库中定义方法？难道不是在处理接口吗？ - gene b.

@geneb。Java 8引入了在接口中放置具体方法的能力。 - Matt

这是一个巨大的代码异味。 - Rafael Lima

Answer 3

Spring使我们能够使用对象模型作为查询参数，使用@Param注释。在动态查询中检查列表类型参数是否为空的另一个解决方法是在我们的搜索模型上放置一个getter方法。

假设您有一个名为PersonSearchFilter的搜索过滤器模型，您希望通过它来搜索Person模型；

@Query(value = "SELECT a FROM Person a WHERE 1=1 "
            + "and (:#{#searchFilter.name} is null or a.name like %:#{#searchFilter.name}%) "
            + "and (:#{#searchFilter.statusListSize} = 0 or a.status in (:#{#searchFilter.statusList})) ")
Optional<List<Person>> findByFilter(@Param("searchFilter") PersonSearchFilter searchFilter);


public class PersonSearchFilter {

    private String name;
    private List<String> statusList;
    
    public String getName() {
        return name;
    }

    public List<String> getStatusList() {
        return statusList;
    }

    public int getStatusListSize() {
        return CollectionUtils.isEmpty(statusList) ? 0:statusList.size();
    }

}

- Selman Gun

---

你尝试过使用：＃{#searchFilter.statusList.size}而非：＃{#searchFilter.statusListSize}吗？这应该可以在SpEL中使用。 - Ondřej Stašek

Answer 4

@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, null) IS NULL OR (c.category IN ?3)) ")
List<Course> getCoursesFiltered(Long courseId, String filter, List<Category> categories);

- prashanth pochampally