Hibernate restriction in causes an error if the list is empty

Question

If my list is empty, I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')'

Below is my hibernate related method:

  @Override
    public List<SomeThing> findByIds(List<Integer> someIds) {
        return sessionFactory.getCurrentSession().createCriteria(SomeClass.class)
                .add(Restrictions.in("id", someIds))
                .list();
    }

What should I do to guard against this error?

I know I could short-circuit the call and return an empty list like:

if(someIds == null || someIds.size() == 0) {
  return new List<SomeThing>();
}

But is there a more elegant way to do this?

Answer 1

I would say Hibernate needs to fix this issue, and give meaningful message.

I think its responsibility of the provider/hibernate to check for the empty/null List.

One can imagine the cause, it tries to construct where clause, something like id in (), somewhere in org.hibernate.loader.criteria.CriteriaQueryTranslator or similar..But because here the List is empty, it would be throwing an exception. But they already created query with ( and could not complete because of exception/empty List.

Answer 2

NO. If you execute the query with empty parameters for in clause, it will fail (you may verify this by running plain SQL). Better not to execute the query if the input param is null/empty.

Only thing I can advice is to use isEmpty() function and != null in if statement and little restructuring as:

@Override
public List<SomeThing> findByIds(List<Integer> someIds) {
   List<Something> result = null; //you may initialize with empty list
   if(someIds != null || !someIds.isEmpty() {
       result = sessionFactory.getCurrentSession().createCriteria(SomeClass.class)
            .add(Restrictions.in("id", someIds))
            .list();
   } 
   return result;
}

Answer 3

(This is mostly base on @Yogendra Singh's reply, with a twist to make it more adoptable to commonly-seen situation of multiple optional argument)

Criteria API aims to let you compose your query programmatically. Such kind of dynamic feature is expected to be handled in your code.

Normally we make optional criteria by this:

@Override
public List<SomeThing> findBySearchParams(SearchParam searchParam) {
   // create criteria with mandatory search criteria
   Criteria criteria = sessionFactory.getCurrentSession()
                           .createCriteria(SomeClass.class);
                           .add(Restriction("someField", searchParam.getSomeField()));


   // add "id" only if "someId" contains value
   if(searchParam.getSomeIds() != null && !searchParam.getSomeIds().empty()) {
       criteria.add(Restrictions.in("id", searchParam.getSomeIds()));
   } 

   // add "anotherField" only if "anOptionalField" is not null
   if(searchParam.getAnOptionalField() != null) {
       criteria.add(Restrictions.in("anotherField", searchParam.getAnOptionalField()));
   } 

   return criteria.list();
}

---

Edit:

Although Hibernate does not (yet) provide a more elegant way for that, you can write something yourself to make it looks more elegant:

class SmartCriteriaBuilder {
  private Criteria criteria;
  SmartCriteriaBuilder (Criteria criteria) { this.criteria = criteria;}

  SmartCriteriaBuilder in(String field, Collection values) {
    if (!empty(values)) {
      this.criteria.add(Restrictions.in(field,values));
    }
  }
  // all other kind of restrictions ....

  Criteria toCriteria() {
    return this.criteria;
  }
}

Then you can do something looks smarter:

SmartCriteriaBuilder criteriaBuilder = 
    new SmartCriteriaBuilder(sessionFactory.getCurrentSession().createCriteria());

criteriaBuilder .in("someField", listPossiblyNullOrEmpty);


return criteriaBuilder .toCriteria().list();