检查 Spring 数据 JPA 查询中的 List 参数是否为空 [英] Check that a List parameter is null in a Spring data JPA query

问题描述

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

I have a Spring Boot application and use Spring Data JPA to query a MySQL database.

我需要得到一个用一些参数过滤的课程列表.

I need to get a list of courses filtered with some parameters.

我通常使用语法 param IS NULL 或 (/*do something with param*/) 这样如果参数为空，它就会忽略该参数.

I usually use the syntax param IS NULL or (/*do something with param*/) so that it ignores the parameter if it is null.

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

With simple datatypes I have no problems but when it comes to a List of objects I don't know how to check for NULL value. How can I check if the ?3 parameter is NULL in the following query ?

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

错误是:

无法提取结果集；SQL [不适用]；嵌套异常是 org.hibernate.exception.DataException: could not extract ResultSet[SQL: 1241, 21000]

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

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

And in the stack trace I can see :

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

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

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

Indeed generated query would be ... AND ((?3, ?4, ?5) IS NULL OR (c.category IN (?3, ?4, ?5))) if my list contains 3 elements. But IS NULL cannot be applied to multiple elements (query works fine if my list contain only one element).

我试过 size(?3) <1, 长度(?3) <1、(?3) IS EMPTY 等，但仍然没有运气.

I have tried size(?3) < 1, length(?3) < 1, (?3) IS EMPTY, etc. but still no luck.

推荐答案

好吧，我半夜醒来后想到一件事:

OK I though of a thing after waking up at the middle of the night :

@Query("SELECT DISTINCT c FROM Course c
" +
       "WHERE c.courseDate < CURRENT_TIME
" +
       "AND (?1 IS NULL OR (c.id NOT IN (3, 4, 5)))
" +
       "AND (?2 IS NULL OR (c.title LIKE ?2 OR c.description LIKE ?2))
" +
       "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)) 将完成过滤工作.

The solution was simply to use COALESCE in addition to IS NULL so it can work with multiple values. That way if the list contain at least one non-null value, the second expression ((c.category IN ?3)) will do the job of filtering.

下次我会至少等一整晚才问问题:)

I will wait at least a whole night next time before asking a question :)

这篇关于检查 Spring 数据 JPA 查询中的 List 参数是否为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！