org.hibernate.criterion - I need to OR together an arbitrary number of criterion?

Question

I have a java application using (quite old) hibernate mapping to an oracle 11 database. I'm using the org.hibernate.criterion class to generate my queries (at least for this piece).

I recently hit a bug where one of the queries I'm generating using org.hibernate.Criteria produces a very large 'in' clause. Here's how the Java console displays the generated query:

select this_.foo as foo_1_2_3, this_.bar as bar_2_3_4, this_.id as id_5_6_7
from some_table inner join some_other_table inner join
where blah=bloo and this_.id in (?, ?, ?, ?, ?, ......................... ?, ?, ?, ?) order by this_.id asc

where the number of ? elements is >1000.

This in clause is so large that it triggers ORA-01795: maximum number of expressions in a list is 1000 error in our database.

Here's the Java code that adds the 'in' clause:

criteria.add(Restrictions.in("id", getMassiveListOfIDs() ) );

(where I'm adding the >1000 element in clause, in other words)

According to this stackoverflow question just using multiple smaller 'in' clauses is the solution. Makes sense.

I've modified this so that it splits the lists before adding the 'in' queries, like so:

    List<Long> listOfIds = getMassiveListOfIDs();

    if(listOfIds.size()>=1000){ 
        List<List<Long>> listOfSublists = this.splitIntoSubArrays(listOfIds); 
        for(List<Long> subArray : listOfSublists){
            criteria.add(Restrictions.in("id", subArray)); 
        }
    }else{
        criteria.add(Restrictions.in("id", listOfIds));
    }

So now the generated query look like this:

select this_.foo as foo_1_2_3, this_.bar as bar_2_3_4, this_.id as id_5_6_7
from some_table inner join some_other_table inner join
where blah=bloo and
this_.id in (?, ?, .... ?, ?) 
and this_.id in (?, ?, .... ?, ?) 
and this_.id in (?, ?, .... ?, ?) 
and this_.id in (?, ?, ?, ?, ?, ?) 
order by this_.id asc

where each of the (?, ?, .... ?, ?, ?) arrays actually contain 1000 elements, and the last is the remainder.

The problem is these are AND and not OR I want to OR all of the in clauses(in other words, I want to fetch any rows that have the id listed in ANY of these clauses), like:

select this_.foo as foo_1_2_3, this_.bar as bar_2_3_4, this_.id as id_5_6_7
from some_table inner join some_other_table inner join
where blah=bloo and (
this_.id in (?, ?, .... ?, ?) 
OR this_.id in (?, ?, .... ?, ?) 
OR this_.id in (?, ?, .... ?, ?) 
OR this_.id in (?, ?, ?, ?, ?, ?) 
) 
order by this_.id asc

I know I could do something like this:

Criterion c1 = Restrictions.in("id", sublist1);
Criterion c2 = Restrictions.in("id", sublist2);
Criterion c3 = Restrictions.in("id", sublist3);
Criterion c4 = Restrictions.in("id", sublist4);

Criterion or1 = Restrictions.or(c1, c2);
Criterion or2 = Restrictions.or(c3, c4);

criteria.add(Restrictions.or(or1, or1) );

But I have no idea at compile time how big listOfIds will be. 500 or 10000.

is there a way to either

Dynamically produce the ORing shown above
OR an arbitrarily sized list of criterion together and add them to the query?

I realize this is a very niche question, so I would appreciate any help or advice. I've also massively simplified the examples for the sake of illustration.

Really, I'm looking for a Java Code solution, rather than a change to my database or hibernate mapping if at all possible.

Many thanks.

Daniel Jipa · Answer 1 · 2015-08-05T18:14:29.067

You need something like this:

if (arguments.size() > 1000) {
            Criterion criterionIn = Restrictions.sqlRestriction("1<>1");
            final List<Serializable> inList = new ArrayList<Serializable>();
            for (int i = 0; i < arguments.size(); i++) {
                inList.add(arguments.get(i));
                if (inList.size() == 1000) {
                    criterionIn = Restrictions.or(criterionIn, Restrictions.in(restriction.getField(), inList));
                    inList.clear();
                }
            }
            if (!inList.isEmpty()) {
                criterionIn = Restrictions.or(criterionIn, Restrictions.in(restriction.getField(), inList));
            }
            final Criterion criterionNotIn = Restrictions.not(criterionIn);
            return criterionNotIn;
        }

A complete example you can find on github

score 0 · Accepted Answer · answered Aug 06 '15 at 08:54

It turned out a very simple approach like:

Criterion restriction = Restrictions.in("id", listOfSublists.get(0));

for(int i=1; i<listOfSublists.size(); i++){
    restriction = Restrictions.or(restriction, Restrictions.in("id", listOfSublists.get(i)));
}

works just fine and produces the correct query.

I was getting strange queries being generated when I tried this the first time, and got caught up in how .or and .and methods were being strung together in examples, and how resulting queries would be parenthesized, but it turns out this works just fine, and those issues were unrelated to how I add the restriction in Java.

Next time I'll try a nap and a coffee before I put it to stack overflow...

org.hibernate.criterion - I need to OR together an arbitrary number of criterion?

2 Answers2