HQL Where IN for empty list crashes

Question

I have an HQL statement like so:

Select cast(ed.employee.employeeID as int) AS emp_id FROM Education AS ed WHERE ed.type.name IN (:typeNames)

Sometimes however, typeNames is empty. This causes the following:

org.hibernate.hql.ast.QuerySyntaxException: unexpected end of subtree [Select cast(ed.employee.employeeID as int) AS emp_id FROM Education AS ed WHERE ed.type.name IN ()]

What is the solution to make that accept an empty list?

Answer 1

You can set :typeNames list as null if array is empty.

if(typeNames.isEmpty()) typeNames = null

// Call Query

Answer 2

If typeNames is empty/null, I probably wouldn't execute the query:

if (typeNames) result = Foo.executeQuery("select ... where e.type.name in :typeNames", [typeNames: typeNames)

Answer 3

One solution that I used, would be to place some dummy value in the list together with your input to ensure that it's never empty. Of course, you can only do it if dummy value can be chosen.

If your input list is typeNamesOrig:

List<String> typeNames = new ArrayList<String>(typeNamesOrig);
typeNames.add("valueThatDoesNotExistForSure");
query.setParameterList("typeNames",typeNames);