Hibernate + Oracle IN limitation, how to solve it?

Question

I know this question has been sent many times, but I want to ask about the details,

Using Oracle, you cannot pass more than 1000 parameters to the IN section, so using hibernate with oracle may have some solutions for this problem, for example:

1- use the OR clause between the IN clause for each list of 1000 Parameters, but this is not applicable due to another oracle restriction for all query parameters no more than 2000

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

3- put the parameter list in the physical temp table and join it or even use it according to additional criteria, and I can’t try this solution because I don’t know how to create a temporary table using sleep mode, and then use it.

therefore, if any other thoughts are available using CRITERIA api, please share it or even if there is an easy way to handle the solution number "3"

Yours faithfully,

Answer 1

Firstly, the fact that you need to transfer more than 2000 identifiers (judging by the 1st point) to the request is a warning call in itself. There may be a better way to solve the underlying problem.

№2 , mergesort . , , , ( , ) , № 3.

# 3 :

• Hibernate (. Table.sqlTemporaryTableCreateString, Dialect), .
• , SQL ( ). API , sqlRestriction .

Answer 2

Guava Lists Hibernate Disjunction:

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