Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: Maximum size for in clause in HQL
PostPosted: Fri May 16, 2008 4:55 am 
Beginner
Beginner

Joined: Thu Sep 01, 2005 10:09 am
Posts: 39
HI I have a query like
From Person where id in ( x,y,...)
where i have many (like 10000 or more ids in the in clause) the HQL parser laughs at that and throws a stackoverflowexception. I now build smaller queries which 1000 ids thats fine. does anyone know how much ids i can give there to minimize the number of queries ?

Regards Thomas


Top
 Profile  
 
 Post subject:
PostPosted: Sat May 17, 2008 9:41 pm 
Newbie

Joined: Tue Apr 25, 2006 1:17 pm
Posts: 19
instead of putting each id separately in your hql, you can actually use the 'List' type in your parameter as such:

Code:
'from Person where id in (:person_list)'


and then when you are setting up the 'Query' object, set the list collection as your parameter like:

Code:
myQuery.setParameterList("person_list", myList)


Try that -- it might help cut down on the clutter and possibly the 10000 ids in your clause..

Justin


Top
 Profile  
 
 Post subject: Its the same with that... Didnt help
PostPosted: Sun May 18, 2008 2:48 am 
Beginner
Beginner

Joined: Thu Sep 01, 2005 10:09 am
Posts: 39
Exception in thread "Thread-7" java.lang.StackOverflowError
at org.hibernate.hql.ast.QueryTranslatorImpl$JavaConstantConverter.visit(QueryTranslatorImpl.java:562)
at org.hibernate.hql.ast.util.NodeTraverser.visitDepthFirst(NodeTraverser.java:40)
at org.hibernate.hql.ast.util.NodeTraverser.visitDepthFirst(NodeTraverser.java:41)
at org.hibernate.hql.ast.util.NodeTraverser.visitDepthFirst(NodeTraverser.java:42)
at org.hibernate.hql.ast.util.NodeTraverser.visitDepthFirst(NodeTraverser.java:42)


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 18, 2008 7:20 am 
Regular
Regular

Joined: Wed Apr 25, 2007 11:44 pm
Posts: 59
buddy don't use "in" clause when you know that there are thousands of records might come in the in clause

see oracle's plan while using the in clause and see the plan while using the "where exists" or any other technique

hope that might answer your question


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 18, 2008 7:49 am 
Newbie

Joined: Fri May 16, 2008 3:40 pm
Posts: 13
Where do the 10000 ids come from? I don't believe this is user input, so those should either be persistent or derivable from another hql query. If they are not, make them persistent. Then join instead of using an in-clause like that.


Top
 Profile  
 
 Post subject: keep calm
PostPosted: Sun May 18, 2008 8:26 am 
Beginner
Beginner

Joined: Thu Sep 01, 2005 10:09 am
Posts: 39
they come from a lucene query (yes hibernate search is a great tool) and i want apparently to join fetch associations because for the heck i can't use lazy loading because i have to transmit the whole result. its no problem with a native query then, its the hql parser that throws me out of the race.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.