How to check if a collection parameter is null in hql?

Question

So How to check if a collection is null in HQL? Simple example:

select * from Book book where title in (:titles)

So if titles is a single variable, I can do

select * from Book book where (:titles is null or title in (:titles))

But what if titles is a list/collection?

select * from Book book where (:titles is null or title in (:titles))

this won't work if titles is a list. After intense search, I tried is empty, size, and exists function, I also tried (:titles) is null option.

None of the above works. I know there is a hard coded way which is writing different query depends on the status of the titles list, if it is null, one query, and if it is null, another query. But that will produce a lot similar HQL queries with minor changes. And my use cases has few more lists to consider so it is not desired.

My question is it even possible to do the checking directly in HQL?

what is the business case? if you want to get always data with title which equals null and also equals values from collection then you can do selct * from Book book where title in (:titles) or title is null — Mara, Jul 17 '18 at 10:05
Title is not nullable, but titles list could be null. The idea is that based on different situation, sometimes it will.need to return all Books, sometime it should only return certain book with specific title. — game wu, Jul 17 '18 at 13:14
so you mean that if titles list is null then you need to get all books? in this case you should create two queries: one fetch all books and another fetch by titles. And check in your server code which query to use when list is null and not null. — Mara, Jul 17 '18 at 15:51
I know this solution, but for our case, there will be at least 4 lists, this will have a lot of similar queries. If it is possible, I want to do it in one query. If not, then multiple queries will do. — game wu, Jul 17 '18 at 18:25
Possible duplicate of [Checking for NULL on a Collection in JPQL queries?](https://stackoverflow.com/questions/45164322/checking-for-null-on-a-collection-in-jpql-queries) — Dherik, Dec 21 '18 at 15:00

score -2 · Accepted Answer · answered May 02 '20 at 07:44

:titles is a list.
You want to search books, which have theses "titles".

The user,

Might have select one title
Might have selected multiple titles
Or might have selected no titles at all

So this list might be null, might have one or more elements in it.

In any case, you will use Query.setParameterList(), in order to pass the titles collection into the query, as described in this answer.

Now then, you wouldn't want to use a set method, if the parameter you are trying to pass could be null. After all it's Java that we are talking on here.

So, what you need, is to check if this list is null or not.
Also, you do not want to have hibernate check if the list of titles, that the user has selected, is null.
You also need to have one query only, there is no need for multiple queries.

The way to do this is by using a query builder.
There are many ways to implement this methodology. But in general the idea is that you

Either use a framework, specialized for these kind of jobs, like Querydsl, check here

Or you simple use a StringBuilder to build the select, from and where clauses of your query, for example:

Map<String,Object> params = new HashMap<String,Object>();
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.append(" from Book book ");
if(!titlesList.isEmpty()){
    queryBuilder.append(" where book.title in (:titles) ");
    params.put("titles", titlesList);
}
Query query = entityManager.createQuery(queryBuilder.toString());
for ( Map.Entry<String,Object>; param : params.entrySet()) {
    if(param instanceof Collection<?>){
        query.setParameterList(param.getKey(),param.getValue());
    }
    //if param is of type String then query.setString etc. 
    //else setParameter, you get the idea, use the docs
}
List<Book> results = (List<Book>) query.list();

How to check if a collection parameter is null in hql?

1 Answers1