What is the best approach for solving Restrictions.in with large lists?
It has been found that when you use Hibernate Restrictions.in (String property, List) , you must limit the size of the list.
This is because the database server will not be able to handle long queries. Besides setting up the database server configuration.
Here are the solutions I found:
SOLUTION 1: Split the list into smaller ones and then add the smaller lists separately to multiple Restrictions.in
public List<Something> findSomething(List<String> subCdList) {
Criteria criteria = getSession().createCriteria(getEntityClass());
//if size of list is greater than 1000, split it into smaller lists. See List<List<String>> cdList
if(subCdList.size() > 1000) {
List<List<String>> cdList = new ArrayList<List<String>>();
List<String> tempList = new ArrayList<String>();
Integer counter = 0;
for(Integer i = 0; i < subCdList.size(); i++) {
tempList.add(subCdList.get(i));
counter++;
if(counter == 1000) {
counter = 0;
cdList.add(tempList);
tempList = new ArrayList<String>();
}
}
if(tempList.size() > 0) {
cdList.add(tempList);
}
Criterion criterion = null;
//Iterate the list of lists, add the restriction for smaller list
for(List<String> cds : cdList) {
if (criterion == null) {
criterion = Restrictions.in("subCd", cds);
} else {
criterion = Restrictions.or(criterion, Restrictions.in("subCd", cds));
}
}
criteria.add(criterion);
} else {
criteria.add(Restrictions.in("subCd", subCdList));
}
return criteria.list();
}
This is good because you will only have one select statement. However, I think this is a bad idea for loops at the DAO level because we don't want the connection to be open for a long time.
SOLUTION 2: Use DetachedCriteria. Instead of passing in the list, ask for it in the WHERE clause.
public List<Something> findSomething() {
Criteria criteria = getSession().createCriteria(getEntityClass());
DetachedCriteria detached = DetachedCriteria.forClass(DifferentClass.class);
detached.setProjection(Projections.property("cd"));
criteria.add(Property.forName("subCd").in(detached));
return criteria.list();
}
The problem with this solution is the technical use of the DetachedCriteria. You usually use it when you want to create a request for another class that is completely unrelated (or irrelevant) in your current class. In the example, Something.class has a subCd property, which is a foreign key from DifferentClass. Another, this creates a subquery in the where clause.
When you look at the code:
1. SOLUTION 2 is simpler and concise.
2. But SOLUTION 1 offers a query with only one choice.
Please help me decide which one is more efficient.
Thank.
source to share
Solution 1 has one major drawback: you can end up with many different prepared statements that need to be analyzed and for which the execution plan needs to be calculated and cached. This process can be much more expensive than actually executing a query for which the statement has already been cached by the database. See question for details .
The way around this problem is to use the algorithm used by Hibernate to batch fetch lazy loaded related objects. Basically, I use ArrayHelper.getBatchSizes
to get a sublist of ids and then I do a separate query for each sublist.
Solution 2 is only suitable if you can project the identifiers in the subquery. But if you cannot, then you cannot use it. For example, the user of your application has edited 20 objects on the screen, and now they save the changes. You have to read the objects using IDs in order to merge the changes, and you cannot express them in a subquery.
However, an alternative approach to solution 2 might be to use temporary tables. For example, Hibernate is sometimes used for bulk operations. You can store your ids in a temporary table and then use them in a subquery. I personally think this is an unnecessary complication compared to solution 1 (for this use case, of course Hibernate arguments are good to use them), but it is a valid alternative.
source to share
For Solution 1: Instead of using for loops, you can try as below
To avoid this, use the utility method to build the Criterion Query IN clause if the number of parameter values passed is larger than 1000.
class HibernateBuildCriteria {
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;
}
}
Method usage:
criteria.add (HibernateBuildCriteria.buildInCriterion (propertyName, list));
hope this helps.
source to share