CODEWITHSUNDEEP
javaoracle-databasehibernate
Amr Faisal
Amr Faisal

Reputation: 2034

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

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,

Upvotes: 6

Views: 12749

Answers (3)

M.Garcia
M.Garcia

Reputation: 26

If you want to do it in Java, the best option to do it is making sublists of the main list and then use it in the query:

int DBLIMIT = 2000;
int MAXRESULTS = 1999;
int valueResults = 0;
if (list.size() > DBLIMIT) {
    while (valueResults >= 0) {
    List<String> listMAXRESULTSReg= new ArrayList<>();
    if ((list.size() - (listMAXRESULTSReg.size() + MAXRESULTS * (valueResults + 1))) > 0) {
        listMAXRESULTSReg = list.subList(MAXRESULTS * valueResults, MAXRESULTS * (valueResults + 1));
        valueResults++;
    } else if ((list.size() - (listMAXRESULTSReg.size() + MAXRESULTS * (valueResults + 1))) <= 0) {
        listMAXRESULTSReg = list.subList(MAXRESULTS * valueResults, list.size() - 1);
        valueResults = -1;
    }
    String queryStr = "FROM table" + "WHERE field in (:field) ";
    Query query = session.createQuery(queryStr).setParameter("field", listMAXRESULTSReg);
    resultList.addAll(query.getResultList());
}
return resultList;

Hope it help you.

Upvotes: 0

iTake
iTake

Reputation: 4190

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

Upvotes: 3

ChssPly76
ChssPly76

Reputation: 100686

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.

Upvotes: 6

Related Questions