Hibernate Criterion IN Clause 1000 break up

Question

Hi i have this large oracle hibernate web applications and it seems to give this error

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

and i need a java code tested by someone as a hibernate user defined component to add to my search java classes in my screen as easy as possible could someone have such tested component?

Answer 1

i tried this below code from link and it seem to work beautifully i will paste the code in-case the link were broken in future.

Keep it Simple Keep it Smile :)

    /**
    * An utility method to build the Criterion Query IN clause if the number of parameter
    * values passed has a size more than 1000. Oracle does not allow more than
    * 1000 parameter values in a IN clause. Doing so a {@link SQLException} is
    * thrown with error code, 'ORA-01795: maximum number of expressions in a list is 1000'.
    * @param propertyName
    * @param values
    * @return
    */
import java.util.List;
import org.hibernate.criterion.Restrictions;

/**
 *
 * @author 2796
 */
public class SplitHibernateIn {

    private static int PARAMETER_LIMIT = 999;

    public static org.hibernate.criterion.Criterion buildInCriterion(String propertyName, List values) {
        org.hibernate.criterion.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;
    }
}

Answer 2

The same idea, but using javax Predicate.

private static int PARAMETER_LIMIT = 999;

private static Predicate createInStatement(CriteriaBuilder cb, Path fieldName, List values) {
    int listSize = values.size();
    Predicate predicate = null;
    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 (predicate == null) {
            predicate = fieldName.in(subList);
        } else {
            predicate = cb.or(predicate, fieldName.in(subList));
        }
    }
    return predicate;
}

And the usage

public List<Bean> getBeanList(List<Long> pkList) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Bean> query = cb.createQuery(Bean.class);
    Root<Bean> root = query.from(Bean.class);

    // "Bean_" is a @StaticMetamodel(Bean.class)
    Predicate inStatement = createInStatement(cb, root.get(Bean_.pk), pkList);

    query.select(root).where(inStatement);
    return entityManager.createQuery(query).getResultList();
}

Answer 3

While current answers are fine, I think this one is simpler both to implement and to understand:

private <T> Disjunction restrictionPropertyIn(String property, ArrayList<T> list) {
    Disjunction criterion = Restrictions.disjunction();
    for (List<T> idSubset : Lists.partition(list, 1000)) {
        criterion.add(Restrictions.in(property, idSubset));
    }
    return criterion;
}

• Restrictions.disjunction() is equivalent to concatenating several Criteria using Restrictions.or().
• Lists is a utility class from Guava; partition(list, limit) splits list into sublists of size limit.

The returned Criterion can be used as-is in any place a Criterion is expected, like:

List<Long> fiveThousandIds = Arrays.asList(1, 2, 3, ..., 999, 1000, 1001, ..., 5000);
Criteria crit = session.createCriteria(Employee.class);
crit.add(restrictionPropertyIn("employeeId", fiveThousandIds));
crit.list();

If you need to support different DBs with different IN clause limits, you can turn the hardcoded 1000 into a parameter instead.