Hibernate compare with empty List?

Question

I have the following Hibernate HQL query:

 def query = 
 """
select i from Item
where i not in :someItemList
 """

 def items = Item.executeQuery(query, [someItemList: someItemList])

In my query someItemList is a list of Item objects. This query works fine if someItemList is not empty. Hibernate raises an exception in case the list is empty.

Is there a way to modify this query that it also works with the empty someItemList?

Also, see this: http://stackoverflow.com/questions/8450400/hql-where-in-for-empty-list-crashes — Sotirios Delimanolis, Sep 27 '13 at 19:09
Change the query string depending if someItemList is empty or not. — Marcelo, Sep 27 '13 at 19:12
@ShadowCreeper can you please post an answer to your comment? — Michael, Sep 27 '13 at 19:29
@confile I never tested it, it was only a question about a theory. If it worked, awesome! However I think the clean answer is just to *not* run the query if `someItemList.isEmpty()`. — Shadow Man, Oct 01 '13 at 18:35
@ShadowCreeper the result is the same exception even when use count. — Ilias Stavrakis, Jan 14 '16 at 14:36

score 2 · Answer 1 · answered Sep 27 '13 at 19:18

2

Just verify if the list is empty before creating the query.

if (someItemList.isEmpty()) {
   select i from Item    //your query
} else {
   select i from Item
   where i not in :someItemList   //your query here
}

answered Sep 27 '13 at 19:18

John

576
1
6
14

Can it be done within the HQL query as well? – Michael Sep 27 '13 at 19:28
As far as I know if u try to use the query as you want, it will always complain when the list is empty. I had this problem before and I have solved it by checking for empty before creating the query... – John Sep 27 '13 at 19:33

dmahapatro · Accepted Answer · 2013-09-28T15:57:20.503

2

def someItemList = ['Example'] //empty in cases    

def query = 
 """
select i from Item ${someItemList ? ' where i not in (:someItemList)' : ''}
 """

def items = someItemList ? Item.executeQuery(query, [someItemList: someItemList])
                         : Item.executeQuery(query)

//or try
def items = Item.executeQuery(query, 
                            someItemList ? [someItemList:someItemList] : [:])

Using GString.

edited Sep 28 '13 at 15:57

answered Sep 27 '13 at 20:33

dmahapatro

49,365
7
88
117

I get the error: Parameter someItemList does not exist as a named parameter in query, in case the someItemList is empty. How to fix that? – Michael Sep 28 '13 at 13:48
1

Same would apply for the query, see update. Not that much DRY. With the `if` block as shown in the other answer it will simpler. @confile – dmahapatro Sep 28 '13 at 15:59

Hibernate compare with empty List?

2 Answers2