Prevent Hibernate from doing N+1 selects with constructor expression

The Motivation

While basic CRUD operations with JPA / Hibernate are easy, every application sooner or later needs to introduce DTO style result objects for specific use cases like projections. That is part of the deal, no OR mapper can do your homework for you.

Luckily JPA gives us a way of specifying exactly what result objects we expect from a query. One way to do that is to use JPQL with its so called ‘constructor expression’ in the select part of the query.

select new com.example.NameDto(e.firstName, e.lastName) from Employee e

Note the new keyword and the fully qualified class name. This does exactly what you would think it would do. It creates a new instance of NameDto using the appropriate public constructor for each result row.

The Problem

Using parts of an entity and projecting them into a DTO is fine and all but what happens if we want to fetch the whole entity and wrap it into a DTO? Imagine the use case where we want to select an entity but also some auxiliary information that is not stored along with the entity. For example suppose we want to select “places near me in a 2000m radius”. That’s only one where condition and then you have a result set of the actual place entities right? What if we also want to show to the end user how far away from his position those places happen to be? Why don’t we just wrap the Place entity into an appropriate DTO that also holds the distance, say DistanceResult? Let’s try it out.

Here we have the Place entity, you can see it has a location field indicating where it is located.

And then we a DTO called DistanceResult which should just wrap our entity together with how far it is from the origin of the query.

Prepared with that let’s fire a simple JPQL query like this:

select new com.example.DistanceResult(p, distance(:center, p.location))
  from Place p
  where dwithin(:center, p.location, :radiusMeters) = true

Ignore for a moment that we are dealing with geographic types and functions here. This is actually part of hibernate-spatial and I use it on an instance of postgis but this doesn’t matter here.

This is what our query log gives us for that query:

select place1_.id as col_0_0_, st_distance(?, place0_.location) as col_1_0_ from places place0_ inner join places place1_ on (place0_.id=place1_.id) where st_dwithin(?, place0_.location, ?)=true

select place0_.id as id1_0_0_, place0_.location as location2_0_0_, place0_.name as name3_0_0_ from places place0_ where place0_.id=?

select place0_.id as id1_0_0_, place0_.location as location2_0_0_, place0_.name as name3_0_0_ from places place0_ where place0_.id=?

We can clearly see that our singular JPQL query resulted in one query that is roughly its SQL equivalent. On top of that we can see additional selects one for each entity instance in our result set (here the result size is 2). This looks like the dreaded N+1 problem.

Solution 1: result transformers

On one hand someone at the hiberante project clearly had a reasoning behind why using the constructor expression would not fetch complete entities but rather only the immediate needed parts. On the other hand in the given use case N+1 is nothing we want to have. Sadly there is no clear way of telling hibernate to fetch the whole entity. For example a self join using the fetch keyword doesn’t work. Also the fetch all properties stanza does not affect the resulting SQL in this case.

Luckily there is some other way to do the DTO instantiation we want to have. This is hibernate specific and called ResultTransformer. Given any hibernate query we can attach an instance of this interface and have our results handled by it.

Result Transformers with Spring Data JPA

I trust it a large part of JPA and/or hibernate users will probably be using Spring Data JPA to avoid boilerplate. Here is how to use ResultTransformer in that case:

First we have our usual repository interface without anything fancy, note that it extends an additional interface.

@Repository
public interface PlaceRepository extends PagingAndSortingRepository<Place, Long>,
    DistancePlaceRepository {
}

This is that additional interface, we can see our distance query method is defined here.

public interface DistancePlaceRepository {
    List<DistanceResult> findInDistance(Point center, double radiusMeters);
}

But how is this method implemented? We can add another @Repository bean into the context that implements only this interface. Spring Data JPA will actually do some kind of mixin magic behind the covers such that our concrete repository bean later on uses the following implementation:

@Repository
public class DistancePlaceRepositoryImpl implements DistancePlaceRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @SuppressWarnings({"unchecked", "deprecation"})
    @Override
    public List<DistanceResult> findInDistance(Point center, double radiusMeters) {
        String jpql = "select p, distance(:center, p.location) from Place p where dwithin(:center, p.location, :radiusMeters) = true";

        return (List<DistanceResult>) entityManager.createQuery(jpql)
                .setParameter("center", center)
                .setParameter("radiusMeters", radiusMeters)
                .unwrap(Query.class)
                .setResultTransformer(
                        (ListResultTransformer)
                                (tuple, aliases) -> new DistanceResult(
                                        (Place) tuple[0],
                                        ((Number) tuple[1]).doubleValue()
                                )
                ).getResultList();
    }
}

Now how does our query log look like if we use this transformer?

select place0_.id as col_0_0_, st_distance(?, place0_.location) as col_1_0_, place0_.id as id1_0_, place0_.location as location2_0_, place0_.name as name3_0_ from places place0_ where st_dwithin(?, place0_.location, ?)=true

As you can see its only a single select which is much better. No N+1 in sight.

Drawbacks

The first thing you will note when trying to use ResultTransformer is that the actual method to use them Query#setResultTransformer is marked as deprecated. This is actually some form of premature deprecation by the hibernate developers. Unless you’re using hibernate 6.0 there is no way around using this deprecated method. So it is clearly fine to ignore the warning here (suppress it until 6.0 is GA and then migrate).

The second thing to note is that clearly this solution is hibernate specific. ResultTransformer is nothing JPA has an equivalent of (yet).

Solution 2: Blaze Persistence entity view

While investigating this problem and related info on the internet I happened upon a stack overflow answer leading me to discover so called “Blaze Persistence Entity Views”. It looks like we could redefine our DTO with this. I leave it as an exercise to the reader to find out via their documentation how exactly we would achieve the wrapping of the whole entity and also map the distance part of our original query. Its an extensive library, I’m sure there is a way.

Solution 3: use jOOQ

Last but not least I still have to mention that of course there is nothing preventing us from just mixing our existing hibernate mapping with a little bit of jOOQ on the side. This would give us full control over what SQL exactly we are executing. And we can easily wrap the resulting tuples into our DTO if we want. Sure we would have to pay attention that the result are no managed entities but in our use case this isn’t necessary in the first place.

I said not to pay attention to the spatial functions earlier but if we actually want to use jOOQ for this kind of query then we would need support for those SQL functions in the query builder. Luckily there is a project for this on github by the name of jooq-postgis-spatial (in the case of postgis that is).

Links

• Vlad Mihalcea on hibernate result transformers: Blog Post
• Blaze Persistence entity views documentation
• Original Question on stackoverflow
• jOOQ homepage
• Photo by Jason Leung on Unsplash