Oracle has a limit of 1000 elements in an 'in' clause collection. If you need to check more than 1000 elements you need to break it into two clauses.
For example: "select from Person where firstName IN (:largeCollection : )"
can be transformed into: "select from Person where (firstName IN :largeCollection1 or firstName IN :largeCollection2 or firstName IN : largeCollection3 ...)"
Can the task of breaking the collection into sub-collections of 1000 and modifying the string be added to the Oracle dialect automatically if a collection of above 1000 elements is used as a parameter?
Hibernate is a bit of a “leaky abstraction” here in the sense that perfectly intuitive Java behavior (“why can’t I have more than 1000 elements in a collection?”) leaks SQL requirements into the application layer, and it’s pretty ugly to have to fix it every single place there is a “in” clause in a query. |
|
