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

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.

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);