Oracle knows the limitation to 1000 elements in the where a in (..) clause. Does this limitation also exist when using Hibernate in conjuction with Oracle?
views:
536answers:
3
+2
A:
Yes as Hibernate will call Oracle at some stage so the limit is the lowest of the limits in Hibernate and Oracle
Hibernate does nothing special with data for an in - just passes it to the database
Mark
2009-11-04 12:38:14
+2
A:
This database limitation still exists with hibernate. If you really need to have more than 1000 items in your in clause you will have to split the list yourself in the code and run the query for each block of 1000 keys, then append the result sets together.
Note this hack breaks down if your query needs to sort or otherwise aggregate the results of the query because the full set of results will only be known in the code. In this case you are better of finding another way to write the query that does not require an IN clause.
Tendayi Mawushe
2009-11-04 16:00:31
An alternative way would be to stick the 1000+ values in a global temporary table in the database and join the two tables. Databases are very good at joining table :)
Gary
2009-11-04 21:53:01
Exactly let the database do the work! The spliting is a well know work around but one that should indicate that you need to rethink your approach.
Tendayi Mawushe
2009-11-04 21:58:04
A:
It seems to be a Bug in Hibernate:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-1123
Christian
2010-01-27 05:27:21