在JPQL查询中的集合上检查NULL? [英] Checking for NULL on a Collection in JPQL queries?
问题描述
我正在编写一个JPQL查询，该查询基于 Categories 集合进行查询.我的类别可以为空，因此我正在使用:categories = NULL.
I am writing a JPQL query which queries based on a Categories collection. My categories can be null and hence I am checking using :categories=NULL.
@Query("Select v from Vendor v join v.org vorg join v.categories cats WHERE vorg.email =:email AND (cats in :categories or :categories = NULL)")
Set<Vendor> filterVendors(@Param("emailId") String emailId, @Param("categories") Set<Category> categories);
当category为NULL时，上面的方法可以正常工作.但是，当类别不止一个值时，我会得到错误
The above works fine when categories is NULL. However, when categories is more than one value I get the error
java.sql.SQLException:操作数应包含1列
java.sql.SQLException: Operand should contain 1 column(s)
与休眠有关的痕迹是
()中的category6_.category_id ? ，? ) 或者 ( ? ，? ) 一片空白 )
category6_.category_id in ( ? , ? ) or ( ? , ? ) is null )
在JPQL中如何检查集合中的null?我认为解决该问题应该可以解决此错误.任何帮助表示赞赏
How does one check for null on a collection in JPQL ? I think solving for that should resolve this error. Any help appreciated
谢谢.
推荐答案
所以我被困在做类似的事情.基本上，您要检查是否有一个
So I was stuck doing something quite similar. Basically you want to check to see if either
- 您要传递的集合为空:请不要在查询中包括它
- 您要传递的集合已填充:然后将其包括在查询中
问题是没有好的方法来处理这部分:
The problem with this is there is no good way to handle this part:
:categories = NULL
这是因为当转换为SQL时，它将看起来像这样(当集合中有2个项目时):
This is because when translated to SQL it is going to look something like this (when 2 items are in the collection):
@p0, @p1 = NULL
您也不能将其包装在括号中，因为这在SQL中无效:
You cannot wrap it in parenthesis either, because this is not valid in SQL:
(@p0, @p1) = NULL
我尝试使用合并将值减小为NULL，但是我也无法使它起作用.
I tried to use coalesce to reduce the values down to NULL, but I couldn't get that to work either.
从基本上我可以看出，JPQL中没有任何东西可以让您运行某种类型的集合功能(哈希集等)以查看其是否已填充.如果有人知道一个，请告诉我们.
Basically from what I can tell there isn't anything in JPQL that lets you run some kind of function of a collection (hashset, etc) to see if it is populated or not. If anyone knows of one please let us know.
最后，我诉诸黑客，在调用代码中检查集合以查看是否已填充集合.如果不是，那么我简单地再传入一个参数...一个空字符串.
Finally I resorted to a hack, in the calling code I check the collection to see if it is populated. If it is not I simple pass in one more parameter ... an empty string.
String catString = "";
if (categoryList != null && !categoryList.isEmpty()) catString = "HasCats";
然后在JPQL中执行以下操作:
Then in the JPQL do this:
WHERE (:catsString = '' or cats IN (:categories)) "
这不是世界上最好的解决方案，但它是实用的.
Not the best solution in the world but it is functional.
这篇关于在JPQL查询中的集合上检查NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
