Checking for NULL on a Collection in JPQL queries?
I was able to got it like this:
coalesce(:myIdCollection, null) is null or id in (:myIdCollection)
If anyone using PostgreSQL encounters this problem, it can easily be fixed by adding parentheses:
(cats in :categories or (:categories) IS NULL)
So I was stuck doing something quite similar. Basically you want to check to see if either
- the collection you are passing in is empty: then don't include it in the query
- 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.