10

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?

Derek
  • 11,715
  • 32
  • 127
  • 228
  • what should the behaviour be if the list is empty - get all, or get none ? – aishwarya Dec 09 '11 at 19:08
  • It should not return any results if there is an empty list – Derek Dec 09 '11 at 19:16
  • Can't you just check the list before you pass it as an argument to the HQL query? I believe HQL just reflects the behaviour of SQL, which will also throw an error if you attempt to do a IN() query? – sbrattla Dec 09 '11 at 22:28
  • @Derek, I concur with Ted. if you are looking for an empty list in this condition, why execute the query at all :-) – aishwarya Dec 10 '11 at 06:17
  • 2
    There is a (currently) opened bug in Hibernate jira about empty IN() issue: https://hibernate.atlassian.net/browse/HHH-8091 – omnomnom Mar 29 '13 at 16:38

4 Answers4

15

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)
Ted Naleid
  • 26,511
  • 10
  • 70
  • 81
  • 1
    Can you explain why not? As any type name will be never included in an empty list, I would expect that nothing can be selected. Thus the result set is empty. – Lars Blumberg Jul 10 '13 at 12:15
  • 3
    Mostly because it saves a round trip to the database to determine something that we can know in code, also because it works around the issue in hibernate that forces things like the accepted answer to put a dummy value in the list. – Ted Naleid Jul 10 '13 at 14:30
  • 1
    @Ted Sometimes you cannot avoid execution. I face the same problem and the query has an OR in WHERE clause. All other bind parameters have valid values and the query returns results even when the list is empty. It's dangerous to use dummy values in my scenario so dead end. Is there any other solution? – Ilias Stavrakis Jan 14 '16 at 13:46
  • There are queries you still want to execute even if the collection is empty. Valid cares are where you use an optional `excludeIds` collection to exclude specific rows from update. – djmj Mar 22 '20 at 20:35
  • @TedNaleid if hibernate made this design decision it would be really bad. I would rather have a useless database roundtrip than being surprised by this error in production. It is easy to miss especially if you do not know it. I mean you can still handle it in your code. – Arwed Mett Jul 27 '22 at 08:24
5

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);
Alex Gitelman
  • 24,429
  • 7
  • 52
  • 49
  • 1
    This will not work when you use collection of enums. Since you can only use enum values and not just some non existing string, chances are quite high that every enum value would return one or more results. And creating an extra EMPTY enum value just for this purpose would be overkill and very ugly. – lugte098 Jul 03 '14 at 14:16
  • 1
    That workaround solves a problem and introduces a new one at the same time, I would highly recommend to consider other answers. – Pavel Mar 23 '17 at 10:32
  • @Pavel There are no other legitimate answers here, though... :( – Josh M. Jan 29 '19 at 21:46
2

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

if(typeNames.isEmpty()) typeNames = null

// Call Query
Dayoung
  • 29
  • 1
-1

The dummy value is definitely the easiest solution except that you never know for sure the value does not exists. The best thing you do is add another variable to indicate the list is empty.

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

if your list has no items just add a random id to it and set listHasItems to 0.

Nactive
  • 540
  • 1
  • 7
  • 17