Hibernate + Oracle IN clause limitation, how to solve it?

Question

I know this question is posted many times, but I want to ask about details,

Using Oracle, you can't pass to IN clause more than 1000 parameters, so using hibernate with oracle may have some solutions to this issue like:

1- use OR clause between IN clause for each 1000 Parameters list, but this isn't applicable due to the other limitation by oracle for the whole query parameters not to exceed 2000

2- use a for loop, to run the query in term of 1000 parameters list each time, and then append all the results, this is not very good if you need sorting, or using criteria api with different level modification

3- put the parameters list in a temp physical table, and join it, or even use it in a sub criteria, and this solution I can't try, because I don't know how to create a temp table using hibernate, and then use it,

so if any other thoughts available using CRITERIA api please share it, or even if there's a simple way to handle solution number '3'

Regards,

Answer 1

First of, the fact that you need to pass more than 2000 ids (judging by your 1st point) to a query is a warning bell in and of itself. Perhaps there's a better way of solving the underlying problem.

You can use approach #2 and sort each list, then perform mergesort within the application. That will require extra code, but will likely (assuming the actual query is relatively fast) perform better than approach #3.

For #3, there are 2 big downsides to dealing with temporary tables:

• while Hibernate does support them (look at Table.sqlTemporaryTableCreateString method, it uses a bunch of supporting methods in the Dialect class), they're used internally and will require additional coding on your part to be accessible from the app.
• more importantly, using a temporary table will force you to write your query as native SQL (since it's not going to be mapped). If you're using Criteria API you will have to use sqlRestriction with a subquery.

Answer 2

With a little help of Guava Lists and Hibernate Disjunction:

    Disjunction disjunction = Restrictions.disjunction();
    for (List<?> chunkList: Lists.partition(largeList, 1000)) {
        disjunction.add(Restrictions.in(propertyName, chunkList));
    }
    criteria.add(disjunction);