I’ve been caught several times on the same bug recently : some method takes a collection as argument, and must execute a SQL (actually, a Hibernate query) to find all foos having their ID in the given collection:
public List<Foo> findByIds(Set<Long> ids) { String hql = "select foo from Foo foo where foo.id in (:ids) order by foo.bar"; Query query = session.createQuery(hql); query.setParameterList("ids", ids); return query.list(); }
This generates a SQL query looking like
select f.ID, f.BAR from FOO f where f.ID in (?, ?, ?) order by f.BAR
Everything goes well, until the method is executed with an empty set as argument. The SQL query becomes invalid and a runtime exception is thrown. Could anyone tell me why the SQL standard doesn’t allow an IN
without any parameter in the list? It’s not like the SQL engine couldn’t optimize the query and return nothing instantly.
By the way, Hibernate could also play a role here, and translate an no-parameter IN
clause by WHERE 0 = 1
. But no, you have to take care of this yourself, and rewrite the method as this:
public List<Foo> findByIds(Set<Long> ids) { if (ids.isEmpty()) { return Collections.emptyList(); } String hql = "select foo from Foo foo where foo.id in (:ids) order by foo.bar"; Query query = session.createQuery(hql); query.setParameterList("ids", ids); return query.list(); }
This is easy when the method is as simple as this one. But when the IN clause is embedded inside a subquery, things aren’t always as straightforward. Why an empty set is considered invalid in SQL is an mystery. But at least, SQL doesn’t force me to write
select f.ID, f.BAR from FOO f where f.ID = ? order by f.BAR
each time the set contains only one element.
Criteria API might take care of empty-collection well, I suppose.
Yeah, that’s weird.. Another thing that annoys me is that you cannot pass null value in predicate. You have to use this ugly clause: IS NOT NULL.
hello iam a university student i have some problems in java
would you plz help me?
i donot know how to create a priority queue with linked lists
and
i donot know how to use linked lists for sparse polynomials
this is my email:sedmoradi92@yahoo.com
pleaze help me i do need your help
thanks so much
See http://www.stackoverflow.com for technical questions.