HQL Where IN for empty lists

Question

I have a HQL statement:

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 decision to accept an empty list?

Answer 1

One solution I used would be to put some dummy value in the list along with your input, to ensure that it will never be empty. Of course, you can only do this if you can select a dummy value.

If your input list is typeNamesOrig :

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

Answer 2

If typeNames is empty / null, I probably would not have executed the request:

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

Answer 3

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

 if(typeNames.isEmpty()) typeNames = null // Call Query 

Answer 4

A dummy value is by far the easiest solution, except that you don’t know for sure that the value does not exist. The best thing you do is add another variable to indicate that the list is empty.

 select * from books where id in (:ids) and :listHasItems = 1 

If there are no items in your list, just add a random identifier to it and set listHasItems to 0.