休眠标准 IN 条款 1000 分手

java hibernate database-performance

2022-09-03 00:27:15

嗨，我有这个大的预言机休眠Web应用程序，它似乎给了这个错误

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

我需要一个由某人测试的Java代码作为休眠用户定义的组件，以尽可能容易地添加到我的屏幕中的搜索Java类中，有人可以有这样的测试组件吗？

答案 1

我从链接尝试了下面的代码，它似乎工作得很好，我会粘贴代码，以防将来链接被破坏。

保持简单保持微笑:)

    /**
    * 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;
    }
}

答案 2

同样的想法，但使用javax谓词。

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

和用法

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