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

instead of putting each id separately in your hql, you can actually use the 'List' type in your parameter as such:



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



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

Justin

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)

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

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.

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.