CODEWITHSUNDEEP
javajpaspring-data-jpajpa-2.0
Peter Penzov
Peter Penzov

Reputation: 1680

How to send empty list to IN clause

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?

Upvotes: 5

Views: 4207

Answers (4)

andre
andre

Reputation: 1212

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.

Upvotes: 0

Joop Eggen
Joop Eggen

Reputation: 109593

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() + ...

Upvotes: 5

Satz
Satz

Reputation: 319

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.

  1. Implement EntityManger and create your SQL queries in runtime. So you can populate your where cause and everything. Like this: entityManager.createNativeQuery(sql.toString())

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

Upvotes: 1

Rambler
Rambler

Reputation: 5482

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.

Upvotes: 0

Related Questions