检查Spring数据JPA查询中的列表参数是否为空

Question

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

我需要得到一份经过一些参数筛选的课程列表。

我通常使用语法param IS NULL或（/*对param*/）执行操作，以便在参数为NULL时忽略它。

对于简单的数据类型，我没有问题，但当涉及到对象列表时，我不知道如何检查NULLvalue。如何检查？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.DataExc0019：无法提取ResultSet[SQL：1241, 21000]

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

原因：java。sql。SQLException:操作数应包含1列

实际上，生成的查询将是。。。如果我的列表包含3个元素，则（？3、？4、？5）为空或（？3、？4、？5）中的c类）。但是为空不能应用于多个元素（如果我的列表只包含一个元素，则查询可以正常工作）。

我试过尺码（？3）




Answer 1

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

}

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

Answer 3

好吧，我在半夜醒来后想到了一件事：

@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，所以它可以使用多个值。这样，如果列表包含至少一个非空值，第二个表达式（（c.categoryIN？3））会做过滤的工作。

下次我至少要等一整晚再问问题：）