What is the better approach for solving Restrictions.in with large lists?

Question

It has been established that when you use Hibernate's Restrictions.in(String property, List list), you have to limit the size of list.
This is because the database server might not be able to handle long queries. Aside from adjusting the configuration of the database server.

Here are the solutions I found:

SOLUTION 1: Split the list into smaller ones and then add the smaller lists separately into several 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 an okay solution since you will only have one select statement. However, I think it's a bad idea to have for loops on the DAO layer because we do not want the connection to be open for a long time.

SOLUTION 2: Use DetachedCriteria. Instead of passing the list, query it on 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 in this solution is on the technical usage of DetachedCriteria. You usually use it when you want to create a query to a another class that is totally not connected (or does not have relationship) on your current class. On the example, Something.class has a property subCd that is a foreign key from DifferentClass. Another, this produces a subquery on 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 select.
Please help me decide which one is more efficient.

Thanks.

Answer 1

For Solution 1 : Instead of using for loops, you can try as below

To avoid this use an utility method to build the Criterion Query IN clause if the number of parameter values passed has a size more 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;
   }
 }     

Using the Method :

criteria.add(HibernateBuildCriteria.buildInCriterion(propertyName, list));

hope this helps.

Answer 2

Solution 1 has one major drawback: you may end up with a lot of different prepared statements which would need to be parsed and for which execution plan would need to be calculated and cached. This process may be much more expensive than the actual execution of the query for which the statement has already been cached by the database. Please see this question for more details.

The way how I solve this is to utilize the algorithm used by Hibernate for batch fetching of lazy loaded associated entities. Basically, I use ArrayHelper.getBatchSizes to get the sublists of ids and then I execute a separate query for each sublist.

Solution 2 is appropriate only if you can project ids in a subquery. But if you can't, then you can't use it. For example, the user of your app edited 20 entities on a screen and now they are saving the changes. You have to read the entities by ids to merge the changes and you cannot express it in a subquery.

However, an alternative approach to solution 2 could be to use temporary tables. For example Hibernate does it sometimes for bulk operations. You can store your ids in the temporary table and then use them in the subquery. I personally consider this to be an unnecessary complication compared to the solution 1 (for this use case of course; Hibernate's reasoning is good for their use case), but it is a valid alternative.