Projections with JPA and Hibernate

By Thorben Janssen

Query

Choosing the right projection when selecting data with JPA and Hibernate is incredibly important. When I’m working with a coaching client to improve the performance of their application, we always work on slow queries. At least 80% of them can be tremendously improved by either adjusting the projection or by using the correct FetchType.

Unfortunately, changing the projection of an existing query always requires a lot of refactoring in your business code. So, better make sure to pick a good projection in the beginning. That’s relatively simple if you follow a few basic rules that I will explain in this article.

But before we do that, let’s quickly explain what a projection is.

What is a projection?

The projection describes which columns you select from your database and in which form Hibernate provides them to you. Or in other words, if you’re writing a JPQL query, it’s everything between the SELECT and the FROM keywords.

em.createQuery("SELECT b.title, b.publisher, b.author.name FROM Book b");

What projections do JPA and Hibernate support?

JPA and Hibernate support 3 groups of projections:

  1. Scalar values
  2. Entities
  3. DTOs

SQL only supports scalar projections, like table columns or the return value of a database function. So, how can JPA and Hibernate support more projections?

Hibernate first checks which information it needs to retrieve from the database and generates an SQL statement with a scalar value projection for it. It then executes the query and returns the result if you used a scalar value projection in your code. If you requested a DTO or entity projection, Hibernate applies an additional transformation step. It iterates through the result set and instantiates an entity or a DTO object for each record.

Let’s take a closer look at all 3 projections and discuss when you should use which of them.

Entity projections

For most teams, entities are the most common projection. They are very easy to use with JPA and Hibernate.

You can either use the find method on your EntityManager or write a simple JPQL or Criteria query that selects one or more entities. Spring Data JPA can even derive a query that returns an entity from the name of your repository method.

TypedQuery<Book> q = em.createQuery("SELECT b FROM Book b", Book.class);
List<Book> books = q.getResultList();

All entities that you load from the database or retrieve from one of Hibernate’s caches are in the lifecycle state managed. That means that your persistence provider, e.g., Hibernate, will automatically update or remove the corresponding database record if you change the value of an entity attribute or decide to remove the entity.

b.setTitle("Hibernate Tips - More than 70 solutions to common Hibernate problems");

Entities are the only projection that has a managed lifecycle state. Whenever you want to implement a write operation, you should fetch entities from the database. They make the implementation of write operations much easier and often even provide performance optimizations.

But if you implement a read-only use case, you should prefer a different projection. Managing the lifecycle state, ensuring that there is only 1 entity object for each mapped database record within a session, and all the other features provided by Hibernate create an overhead. This overhead makes the entity projection slower than a scalar value or DTO projection.

Scalar value projections

Scalar value projections avoid the management overhead of entity projections, but they are not very comfortable to use. Hibernate doesn’t transform the result of the query. You, therefore, get an Object or an Object[] as the result of your query.

Query q = em.createQuery("SELECT b.title, b.publisher, b.author.name FROM Book b");
List<Object[]> books = (Object[]) q.getResultList();

In the next step, you then need to iterate through each record in your result set and cast each Object to its specific type before you can use it. That makes your code error-prone and hard to read.

Instead of an Object[], you can also retrieve a scalar projection as a Tuple interface. The interface is a little easier to use than the Object[].

TypedQuery<Tuple> q = em.createQuery("SELECT b.title as title, b.publisher as publisher, b.author.name as author FROM Book b", Tuple.class);
List<Tuple> books = q.getResultList();

for (Tuple b : books) {
	log.info(b.get("title"));
}

But don’t expect too much. It only provides a few additional methods to retrieve an element, e.g., by its alias. But the returned values are still of type Object, and your code is still as error-prone as it is if you use an Object[].

Database functions in scalar value projections

Scalar value projections are not limited to singular entity attributes. You can also include the return values of one or more database functions.

TypedQuery<Tuple> q = em.createQuery("SELECT AVG(b.sales) as avg_sales, SUM(b.sales) as total_sales, COUNT(b) as books, b.author.name as author FROM Book b GROUP BY b.author.name", Tuple.class);
List<Tuple> authors = q.getResultList();

for (Tuple a : authors) {
	log.info("author:" + a.get("author")
			+ ", books:" + a.get("books")
			+ ", AVG sales:" + a.get("avg_sales")
			+ ", total sales:" + a.get("total_sales"));
}

This is a huge advantage compared to an entity projection. If you used an entity projection in the previous example, you would need to select all Book entities with their associated Author entity. In the next step, you would then need to count the number of books each author has written, and calculate the total and average sales values.

As you can see in the code snippet, using a database function is easier, and it also provides better performance.

DTO projections

DTO projections are the best kind of projection for read-only operations. Hibernate instantiates the DTO objects as a post-processing step after it retrieved the query result from the database. It then iterates through the result set and executes the described constructor call for each record.

Here you can see a simple example of a JPQL query that returns the query result as a List of BookDTO objects. By using the keyword new and providing the fully qualified class name of your DTO class and an array of references to entity attributes, you can define a constructor call. Hibernate will then use reflection to call this constructor.

TypedQuery<BookDTO> q = em.createQuery("SELECT new org.thoughtsonjava.projection.dto.BookDTO(b.title, b.author.name, b.publisher) FROM Book b", BookDTO.class);
List<BookDTO> books = q.getResultList();

In contrast to the entity projection, the overhead of a DTO projection is minimal. The objects are not part of the current persistence context and don’t follow any managed lifecycle. Due to that, Hibernate will not generate any SQL UPDATE statements if you change the value of a DTO attribute. But it also doesn’t have to spend any management effort, which provides significant performance benefits.

Database functions in DTO projections

Similar to a scalar value projection, you can also use database functions in a DTO projection. As explained earlier, the instantiation of the DTO object is a post-processing step after Hibernate retrieved the query result. At that phase, it doesn’t make any difference if a value was stored in a database column or if it was calculated by a database function. Hibernate simply gets it from the result set and provides it as a constructor parameter.

Conclusion

JPA and Hibernate support 3 groups of projections:

  1. Entities are the easiest and most common projection. They are a great fit if you need to change data, but they are not the most efficient ones for read-only use cases.
  2. Scalar projections are returned as Object[]s or instances of the Tuple interface. Both versions don’t provide any type-information and are hard to use. Even though they are very efficient for read-only operations, you should avoid them in your application.
  3. DTO projections provide similar performance as scalar value projections but are much easier to use. That makes them the best projection for read-only operations.

Tags

Query


About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Repl​​​​​y

Your email address will not be published. Required fields are marked

This site uses Akismet to reduce spam. Learn how your comment data is processed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}