The Flowers Are Still Standing

09 September 2014

If you have used Hibernate as your ORM for any length of time, you have inevitably run into the N+1 problem. This occurs when Hibernate queries an entity that has a collection of children entities that does not use a JOIN fetch mode to retrieve the children. The simple solution when using Hibernate directly is to set the FetchMode to JOIN:

User user = (User) session.createCriteria(User.class)
                .setFetchMode("permissions", FetchMode.JOIN)
                .add( Restrictions.idEq(userId) )
                .uniqueResult();

However, if you are using JPA on top of Hibernate, there is no way to set the FetchMode used by Hibernate to JOIN. In fact, JPA only supports two types of fetching: EAGER and LAZY. Luckily, there is another JPA API that can be used to address this problem: Spring Data JPA. The Spring Data JPA library provides a Domain Driven Design Specifications API that allows you to control the behavior of the generated query. This will allow you to tweak things such as the fetch mode to ensure the proper instruction is passed to Hibernate to address the N+1 problem. I’m not going to go into a bunch of details on how to use the JpaSpecificationExecutor, as the Spring Data JPA documentation does a pretty good job of covering it. What is more important is how to control the fetch mode using it:

final long userId = 1;

final Specification<User> spec = new Specification<User>() {
    @Override
    public Predicate toPredicate(final Root<User> root, final CriteriaQuery<?> query, final CriteriaBuilder cb) {
        query.distinct(true);
        root.fetch("permissions", JoinType.LEFT);
        return cb.equal(root.get("id"), userId);
     }
};

List<User> users = userRepository.findAll(spec);

There are two important pieces of the example above. The first is the setting of the distinct flag on the query to ensure that the proper (unique) results are returned as a result of the join. The second is the addition of a fetch hint to the root entity, telling the specification to perform a left join of the root to the entity mapped by the "permissions" field of the root entity. This ensures that Hibernate will now perform a join instead of N+1 queries to fetch the root entity and its associated children.

Avoid Hibernate N+1 Problem with Joins in Spring-Data/JPA