java Hibernate Criterion IN Clause 1000 分手
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14869054/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate Criterion IN Clause 1000 break up
提问by shareef
Hi i have this large oracle hibernate web applications and it seems to give this error
嗨，我有这个大型的 oracle hibernate web 应用程序，它似乎给出了这个错误
ORA-01795: maximum number of expressions in a list is 1000
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?
我需要一个由某人测试的 java 代码作为休眠用户定义的组件，以尽可能简单地添加到我的屏幕中的搜索 java 类中，有人可以拥有这样的测试组件吗？
回答by shareef
i tried this below code from linkand 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;
}
}
回答by AlexV
The same idea, but using javax Predicate.
相同的想法，但使用 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();
}
回答by walen
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 severalCriteriausingRestrictions.or().Listsis a utility class from Guava;partition(list, limit)splitslistinto sublists of sizelimit.
Restrictions.disjunction()相当于Criteria使用连接几个Restrictions.or()。Lists是来自 Guava 的实用程序类；partition(list, limit)拆分list为 size 的子列表limit。
The returned Criterioncan be used as-is in any place a Criterionis expected, like:
返回的Criterion可以在Criterion预期的任何地方按原样使用，例如：
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 INclause limits, you can turn the hardcoded 1000into a parameter instead.
如果您需要支持具有不同IN子句限制的不同数据库，您可以将硬编码1000转换为参数。
