How to load multiple Hibernate entities in order of the identifiers or Primary Keys

Question

How do we load multiple entities using Hibernate in the order of the list of Pks that is provided to the Hibernate query?

In the code below, the order of the list output is in ascending order rather than the order in which the Pks is supplied in the argument

Criteria criteria = s.createCriteria(entityClass).add(Restrictions.in(idPropertyName, pks));

List list = criteria.list();

Answer 1

You get them, then sort them using a comparator that compares the index of each entity in the list.

For example:

Map<Long, Integer> indexById = new HashMap<>();
for (int i = 0; i < pks.size(); i++) {
    indexById.put(pks.get(i), i);
}

List<MyEntity> entities = seachByIds(pks);

entities.sort(Comparator.comparing(entity -> indexById.get(entity.getId())));

Answer 2

As I explained in this article, there are several ways you can achieve this goal.

In your example, you were using the legacy Hibernate Criteria, but since it's been deprecated since Hibernate 4 and will probably be removed in Hibernate 6.

Therefore, it's better to use one of the following alternatives.

Note that, in your example, you have the entity identifier values defined in a pks variable of the List type, and I'm going to reuse that in the examples below as well.

JPQL

You can use a JPQL query like the following one:

List<Book> books = entityManager
.createQuery(
    "select b " +
    "from Book b " +
    "where b.id in (:ids)", Book.class)
.setParameter("ids", pks)
.getResultList();

When using JPQL, The ids parameter will pass the entity identifiers in the same order they were defined in the pks variable.

Criteria API

If you want to build the query dynamically, then you can use a Criteria API query:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Book> query = builder.createQuery(Book.class);

ParameterExpression<List> ids = builder.parameter(List.class);

Root<Book> root = query
.from(Book.class);

query
.where(
    root.get("id").in(
        ids
    )
);

List<Book> books = entityManager
.createQuery(query)
.setParameter(ids, pks)
.getResultList();

When using Criteria API, The ids parameter will pass the entity identifiers in the same order they were defined in the pks variable.

Hibernate-specific multiLoad

List<Book> books = entityManager
.unwrap(Session.class)
.byMultipleIds(Book.class)
.multiLoad(pks);

By default, the Hibernate multiLoad, The ids parameter will pass the entity identifiers in the same order they were defined in the pks variable. Only if you called enableOrderedReturn(false) explicitly, then the result set will not be ordered.

Now, the JPQL and Criteria API can benefit from the hibernate.query.in_clause_parameter_padding optimization as well, which allows you to increase the SQL statement caching mechanism.

For more details about loading multiple entities by their identifier, check out this article.