Blog

Just to keep others from searching for a “proper” way to handle this case: I just found out that Hibernate generates invalid SQL (at least in MySQL) when you pass an empty (not null) list to Restrictions.in(). The error occured in a simple query that tried to lookup a list of records by id. The code was something like this:

public List<Thing> findRecordsByIds(final List<Integer> ids) {
	return (List<Thing>) getHibernateTemplate().execute(new HibernateCallback() {
		public Object doInHibernate(Session session) throws HibernateException, SQLException {
			return session.createCriteria(Thing.class).add(Restrictions.in("thingId", ids));
		}
	});
}

And it failed with a SQLGrammarException (which should never occur when using the Hibernate Query API). The Hibernate project knows about this as seen in their JIRA, but a fix is not yet delivered. Of course an easy fix to my example is:

public List<Thing> findRecordsByIds(final List<Integer> ids) {
	if(ids.size() == 0) return Collections.emptyList();
	return (List<Thing>) getHibernateTemplate().execute(new HibernateCallback() {
		public Object doInHibernate(Session session) throws HibernateException, SQLException {
			return session.createCriteria(Thing.class).add(Restrictions.in("thingId", ids));
		}
	});
}

Which also uses another function everybody should know about: Collections.emptyList() which in contrary to Collections.EMPTY_LIST uses a simple form of type inference to create a List<Thing> instead of a raw List and avoids a compiler warning.