How to send empty list to IN clause

Question

I want to use this SQL query:

String hql = "select e from " + Terminals.class.getName() + " e WHERE e.merchantId IN :merchant_ids";
        TypedQuery<Terminals> query = entityManager.createQuery(hql, Terminals.class).setParameter("merchant_ids", merchant_ids);
        List<Terminals> merchants = query.getResultList();

But I get error: the right syntax to use near ') So IN clause list into IN (....) can't be empty. Is there some solution to this problem?

Possible duplicate of [Adding IN clause List to a JPA Query](https://stackoverflow.com/questions/4378824/adding-in-clause-list-to-a-jpa-query) — Nir Levy, Sep 15 '19 at 22:40
I can't find a solution into the link. As you can see i don't have `(...)` into my code. — Peter Penzov, Sep 15 '19 at 22:43
Do you really expect to get a result when you have a list that is empty and you're looking for something that's in that list? Such a query must fail. In fact that your code has you looking for IDs, if there's none in the search list then there should be none in the result. What are you doing my guy? — Richard Barker, Sep 15 '19 at 23:13
In my case I need NOT to get a result from table Terminals when the list merchant_ids is empty. The general idea is that I'm looking for Terminals that match assigned merchant ids — Peter Penzov, Sep 15 '19 at 23:15
Think about it, if you're searching for something that's in an empty list you will either get an empty result or all results and therefore the behavior is undefined and thus an error. How you handle the empty list is an implementation detail you must figure out and decide for yourself. — Richard Barker, Sep 15 '19 at 23:17

score 4 · Answer 1 · answered Sep 16 '19 at 14:51

It is allowable and even very fine not executing the query:

if (merchant_ids.isEmpty()) {
    return new ArrayList<>();
} else {
    String hql = "select e from " + Terminals.class.getName()
            + " e WHERE e.merchantId IN :merchant_ids";
   return entityManager.createQuery(hql, Terminals.class)
        .setParameter("merchant_ids", merchant_ids)
        .getResultList();
}

I do not know what would happen if one would pass null instead of an empty list; SQL ... IN NULL could do. On the other hand it might do a full table scan in order to return 0 results.

If x IN() would not result in 0 records (when there is an OR ...) then:

if (merchant_ids.isEmpty()) {
    merchant_ids.add(-1);
    String hql = "select e from " + Terminals.class.getName() + ...

hm.... What should I use for empty param? merchant_ids.add(0); or merchant_ids.add(-1);? — Peter Penzov, Sep 16 '19 at 16:52
A value not occurring. As ID numbers are by convention non-zero positives 0 would be possible too,Because of java's use of -1 in indexOf and zero-based indices I personally tend to -1, but the database column definition could use unsigned numbers or whatever, — Joop Eggen, Sep 17 '19 at 07:48

score 1 · Answer 2 · answered Sep 16 '19 at 14:27

Very often, I used to stuck this kind of case. I couldn't find out a proper solution. Since you are using Spring JPA But I have some workaround to suggest to you.

Implement EntityManger and create your SQL queries in runtime. So you can populate your where cause and everything. Like this: entityManager.createNativeQuery(sql.toString())
Implement if-else block. Check if the list is empty or not, if false call actual query (with IN block) or else write another query without IN block.

Again I am telling, this may not be a proper solution. But I see this is proper workaround.

score 0 · Answer 3 · answered Sep 15 '19 at 23:29

I am not familiar with hibernate but since it is an SQL error, the following should work :

TypedQuery<Terminals> query = entityManager
.createQuery(hql, Terminals.class)
.setParameter("merchant_ids",merchant_ids.size()==0?null:merchant_ids);

But as @Richard Barker mentioned , best solution is to not even execute the query when the list is empty. You will even save on the unnecessary database call , when you already know that the query is not going to return anything.

score 0 · Answer 4 · answered Nov 26 '20 at 21:21

I followed @Rambler's suggestion and created a method to return a null:

    public static <T> Collection<T> nullIfEmpty(Collection<T> collection) {
        return (collection == null || collection.isEmpty()) ? null : collection;
    }

This was easier to add in place, but I agree that it is better to not make the call to the database.

How to send empty list to IN clause

4 Answers4