Checking for NULL on a Collection in JPQL queries?

Question

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

The above works fine when categories is NULL. However, when categories is more than one value I get the error

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

And the hibernate relevant trace is

category6_.category_id in ( ? , ? ) or ( ? , ? ) is null )

How does one check for null on a collection in JPQL ? I think solving for that should resolve this error. Any help appreciated

Thanks.

Answer 1

Select v from Vendor v join v.categories cats WHERE vorg.email =:email AND (cats in :categories or cats IS EMPTY)

You can use the IS [NOT] EMPTY operator to test for empty collections. JPA doesn't necessarily bring back a NULL collection. Think about it, DB returns an empty collection.

Oh, I fixed your query (though I didn't test it).

Answer 2

I was able to got it like this:

coalesce(:myIdCollection, null) is null or id in (:myIdCollection)

Answer 3

So I was stuck doing something quite similar. Basically you want to check to see if either

1. the collection you are passing in is empty: then don't include it in the query
2. the collection you are passing in is populated: then do include it in the query

The problem with this is there is no good way to handle this part:

:categories = NULL

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

You cannot wrap it in parenthesis either, because this is not valid in SQL:

(@p0, @p1) = NULL

I tried to use coalesce to reduce the values down to NULL, but I couldn't get that to work either.

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

Then in the JPQL do this:

WHERE (:catsString = '' or cats IN (:categories)) "

Not the best solution in the world but it is functional.