Search code examples
javamysqlhibernatejpajpql

Checking for NULL on a Collection in JPQL queries?

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.

Solution

  • 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.