Hibernate criteria IN clause: List size more than 1000 values

If list size is more than 1000 values in IN CLAUSE then it throws below oracle error:

Error : ORA-01795: maximum number of expressions in a list is 1000

Oracle does not allow more than 1000 parameter values in a IN clause. 

Avoiding Error By Oracle :
You could use one of the workarounds i.e. By Splitting the IN Clause list into several sub-clauses

Avoiding Error By Hibernate :
Creating a generic method (utility method) in a class HibernateBuildInCriterion to build the Criterion Query IN clause if the number of parameter values passed has a size more than 1000.

class HibernateBuildInCriterion {

private static final int PARAMETER_LIMIT = 800;

public static Criterion buildInCriterion(String propertyName, List<?> values) {
 Criterion criterion = null;
 int listSize = values.size();
 for (int i = 0; i < listSize; i += PARAMETER_LIMIT) {
 List<?> subList;
 if (listSize > i + PARAMETER_LIMIT) {
    subList = values.subList(i, (i + PARAMETER_LIMIT));
   } else {
    subList = values.subList(i, listSize);
   }
 if (criterion != null) {
    criterion = Restrictions.or(criterion, Restrictions.in(propertyName, subList));
   } else {
    criterion = Restrictions.in(propertyName, subList);
   }
 }
 return criterion;
   }
 }

HOW TO USE THE ABOVE METHOD :

Need to add the below line wherever we use IN CLAUSE.

criteria.add(HibernateBuildInCriterion.buildInCriterion(propertyName, list));
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: