Loading multiple entities by id efficiently in Hibernate

Question

So, I'm getting a number of instances of a particular entity by id:

for(Integer songId:songGroup.getSongIds()) {
   session = HibernateUtil.getSession();
   Song song = (Song) session.get(Song.class,id);
   processSong(song);
}

This generates a SQL query for each id, so it occurred to me that I should do this in one, but I couldn't find a way to get multiple entities in one call except by running a query. So I wrote a query

return (List) session.createCriteria(Song.class)
       .add(Restrictions.in("id",ids)).list();

But, if I enable 2nd level caching doesn't that mean that my old method would be able to return the objects from the 2nd level cache (if they had been requested before) but my query would always go to the database.

What the correct way to do this?

Foliar

Did you ever figure out a way to do this? – Stria

Answer 1

What you're asking to do here is for Hibernate to do special case handling for your Criteria, which is kind of a lot to ask.

You'll have to do it yourself, but it's not hard. Using SessionFactory.getCache(), you can get a reference to the actual storage for cached objects. Do something like the following:

for (Long id : allRequiredIds) {
  if (!sessionFactory.getCache().containsEntity(Song.class, id)) {
    idsToQueryDatabaseFor.add(id)
  } else {
    songs.add(session.get(Song.class, id));
  }
}

List<Song> fetchedSongs = session.createCriteria(Song.class).add(Restrictions.in("id",idsToQueryDatabaseFor).list();
songs.addAll(fetchedSongs);

Then the Songs from the cache get retrieved from there, and the ones that are not get pulled with a single select.

Guttapercha

Answer 2

If you know that the IDs exist, you can use load(..) to create a proxy without actually hitting the DB:

Return the persistent instance of the given entity class with the given identifier, obtaining the specified lock mode, assuming the instance exists.

List<Song> list = new ArrayList<>(ids.size());
for (Integer id : ids)
  list.add(session.load(Song.class, id, LockOptions.NONE));

Once you access a non-identifier accessor, Hibernate will check the caches and fallback to DB if needed, using batch-fetching if configured.

If the ID doesn't exists, a ObjectNotFoundException will occur once the object is loaded. This might be somewhere in your code where you wouldn't really expect an exception - you're using a simple accessor in the end. So either be 100% sure the ID exists or at least force a ObjectNotFoundException early where you'd expect it, e.g. right after populating the list.

Growler

Answer 3

There is a difference between hibernate 2nd level cache to hibernate query cache. The following link explains it really well: http://www.javalobby.org/java/forums/t48846.html

In a nutshell, If you are using the same query many times with the same parameters then you can reduce database hits using a combination of both.

Brainwashing

Answer 4

Another thing that you could do is to sort the list of ids, and identify subsequences of consecutive ids and then query each of those subsequences in a single query. For example, given List<Long> ids, do the following (assuming that you have a Pair class in Java):

List<Pair> pairs=new LinkedList<Pair>();
List<Object> results=new LinkedList<Object>();
Collections.sort(ids);
Iterator<Long> it=ids.iterator();

Long previous=-1L;
Long sequence_start=-1L;
while (it.hasNext()){
    Long next=it.next();

    if (next>previous+1) {
        pairs.add(new Pair(sequence_start, previous));
        sequence_start=next;
    }
    previous=next;
}
pairs.add(new Pair(sequence_start, previous));

for (Pair pair : pairs){
    Query query=session.createQuery("from Person p where p.id>=:start_id and p.id<=:end_id");
    query.setLong("start_id", pair.getStart());
    query.setLong("end_id", pair.getEnd());

    results.addAll((List<Object>)query.list());

}

Vamp

Answer 5

Fetching each entity one by one in a loop can lead to N+1 query issues.

Therefore, it's much more efficient to fetch all entities at once and do the processing afterward.

Now, in your proposed solution, you were using the legacy Hibernate Criteria, but since it's been deprecated since Hibernate 4 and will probably be removed in Hibernate 6, so it's better to use one of the following alternatives.

JPQL

You can use a JPQL query like the following one:

List<Song> songs = entityManager
.createQuery(
    "select s " +
    "from Song s " +
    "where s.id in (:ids)", Song.class)
.setParameter("ids", songGroup.getSongIds())
.getResultList();

Criteria API

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

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

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

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

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

List<Song> songs = entityManager
.createQuery(query)
.setParameter(ids, songGroup.getSongIds())
.getResultList();

Hibernate-specific multiLoad

List<Song> songs = entityManager
.unwrap(Session.class)
.byMultipleIds(Song.class)
.multiLoad(songGroup.getSongIds());

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.

Decare

Just a minor remark, please. Oracle (not much familiar with other DBs) IN clause has a limit of 1000 items by default. So the incoming incoming list of IDs should be split into the sublists first (baeldung.com/java-list-split) and the resulting sublists should be joined back. Did I get it right that there is no way to automate it? – Delcine

Not just oracle, same issue with PostgreSQL. In PostgreSQL you can use ANY(...) with an array, but unfortunately Hibernate does not support most of the PostgreSQL syntax and JDBC types. – Contrast