Spring Data JPA: Query Projections

By Thorben Janssen


Projection is one of the first things you’re probably thinking about when implementing a query with Spring Data JPA. This is because projection defines the entity attributes and the database columns returned by your query. So, selecting the right columns is important for your business logic. At the same time, projection is also crucial for the performance of your application and the maintainability of your code. In addition, you need to choose a projection that keeps the overhead as low as possible and provides the data in an easy to use structure.

Types of Projections Supported by Spring Data JPA

Based on JPA’s query capabilities, Spring Data JPA gives you several options for defining your use case’s perfect projection. You can:

  • Use a scalar projection that consists of one or more database columns that are returned as an Object[]. This projection provides great performance for the read operation but it is used rarely. This is because DTO projections offer the same benefits while being much easier to use.
  • Use a DTO projection, which selects a custom set of database columns. It uses them to call a constructor and returns one or more unmanaged objects. This is a great projection if you don’t need to change the selected data.
  • Use an entity projection that selects all database columns mapped by an entity class and returns them as a managed object. Entities are the recommended projection if you want to change the retrieved information.

You can use all three projections with Spring Data JPA’s derived and custom queries. Spring will provide you with the required boilerplate code. In addition, it also makes DTO projections a lot easier to use and allows you to define the projection returned by a repository method dynamically.

Scalar Projections

Scalar projections allow you to select entity attributes that you need for your business logic and exclude the rest. That makes this projection a great fit for all read operations if the results aren’t returned as Object[]s.

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {

	@Query("SELECT b.id, b.title FROM Book b")
	List<Object[]> getIdAndTitle();	
}

A query result stored in an Object[] is hard to use. You need to remember at which position you selected a particular entity attribute. Also, you need to cast the element to the correct type. The good news is that you can avoid all of that and define a use-case-specific projection by using a DTO projection.

DTO Projections

When using a DTO projection, you tell your persistence provider to map each record of your query result to an unmanaged object. As shown in a previous article, this performs much better than entities if you don’t need to change the selected data. And, in contrast to scalar value projections, they are also very easy to use. This is because the DTO objects are named and strongly typed.

JPA’s DTOs

The goal of a DTO class is to provide an efficient and strongly typed representation of the data returned by your query. To achieve that, a DTO class typically only defines a set of attributes, getter and setter methods for each of them, and a constructor that sets all attributes.

public class AuthorSummaryDTO {
	
	private String firstName;
	private String lastName;
	
	public AuthorSummaryDTO(String firstName, String lastName) {
		this.firstName = firstName;
		this.lastName = lastName;
	}
	
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
}

To use this class as a projection with plain JPA, you need to use a constructor expression in your query. It describes a call of the constructor. It starts with the keyword new, followed by the DTO class’s fully-qualified class name and a list of constructor parameters.

@Repository
public interface AuthorRepository extends CrudRepository<Author, Long> {

	@Query("SELECT new com.thorben.janssen.spring.jpa.projections.dto.AuthorSummaryDTO(a.firstName, a.lastName) FROM Author a WHERE a.firstName = :firstName")
	List<AuthorSummaryDTO> findByFirstName(String firstName);
}

As you can see in the code snippet, you can use this approach in Spring Data JPA’s @Query annotation. Your persistence provider then executes a query that selects the columns mapped by the referenced entity attributes and executes the described constructor call.

2020-07-12 20:42:09.875 DEBUG 61380 --- [           main] org.hibernate.SQL                        : select author0_.first_name as col_0_0_, author0_.last_name as col_1_0_ from author author0_ where author0_.first_name=?

In addition to this, Spring provides a few other options to select a DTO projection.

Spring Data’s Simplified DTOs

You can use DTO projections in a derived query without a constructor expression. As long as the DTO class has only one constructor and its parameter names match your entity class’s attribute names, Spring generates a query with the required constructor expression.

@Repository
public interface AuthorRepository extends CrudRepository<Author, Long> {

	List<AuthorSummaryDTO> findByFirstName(String firstName);
}

This makes the query’s definition easier and still provides you with the performance benefits of a query that only selects the required database columns.

2020-07-12 20:43:23.316 DEBUG 61200 --- [           main] org.hibernate.SQL                        : select author0_.first_name as col_0_0_, author0_.last_name as col_1_0_ from author author0_ where author0_.first_name=?

DTOs as Interfaces

Instead of defining a class with an all arguments constructor, you can also use an interface as your DTO projection. As long as your interface only defines getter methods for basic attributes, this is identical to the projection I showed you before.

public interface AuthorView {
	String getFirstName();
	String getLastName();
}

To clarify, for each attribute that you want to use in your projection, your interface needs to provide a getter method. In addition, the name of that method has to be identical to that of a getter method defined on the entity class used in your query.

@Repository
public interface AuthorRepository extends CrudRepository<Author, Long> {
	
	AuthorView  findViewByFirstName(String firstName);
}

In this example, the AuthorView interface and the Author entity define a getFirstName() and a getLastName() method. When you use the AuthorView interface as a return type in the AuthorRepository, Spring Data JPA generates a class that implements the interface.

This makes this form of a DTO projection very comfortable to use. And as you can see in the code snippet, the generated SQL statement only selects the columns mapped by the interface.

2020-07-12 20:57:35.590 DEBUG 38520 --- [           main] org.hibernate.SQL                        : select author0_.first_name as col_0_0_, author0_.last_name as col_1_0_ from author author0_ where author0_.first_name=?

That changes, if your interface maps associations to other entities or uses Spring’s Expression Language.

Mapping Nested Associations

To be able to include associations to other entities in your projection, Spring Data JPA needs to use a different approach. It then selects the underlying entities and performs a programmatic mapping.

In the following example, the Author entity defines a getBooks() method that returns a List of all Books written by an author. You can tell Spring Data to map that List to a List of BookView objects by adding the method List<BookView> getBooks() to the AuthorView interface.

public interface AuthorView {

	String getFirstName();
	String getLastName();
	
	List<BookView> getBooks();
	
	interface BookView {
		
		String getTitle();
	}
}

When you do that, Spring Data JPA will get the Author entity and trigger another query for each Author to fetch the associated Book entities. This creates an n+1 select issue, which can cause severe performance issues. You can avoid that by providing a custom query using a JOIN FETCH clause.

2020-07-12 21:20:00.471 DEBUG 54180 --- [           main] org.hibernate.SQL                        : select author0_.id as id1_0_, author0_.first_name as first_na2_0_, author0_.last_name as last_nam3_0_, author0_.version as version4_0_ from author author0_ where author0_.first_name=?
2020-07-12 21:20:00.503 DEBUG 54180 --- [           main] org.hibernate.SQL                        : select books0_.author_id as author_i4_1_0_, books0_.id as id1_1_0_, books0_.id as id1_1_1_, books0_.author_id as author_i4_1_1_, books0_.title as title2_1_1_, books0_.version as version3_1_1_ from book books0_ where books0_.author_id=?

In the next step, Spring Data uses the Author entity object to instantiate the generated implementation of the AuthorView interface. From a performance point of view, this is the wrong approach. Your query is selecting too many columns and your persistence provider needs to manage another entity object. Because of that, the performance of this projection is worse than the performance of a DTO projection without a mapped association.

Using Spring’s Expression Language

You can also use Spring’s expression language in your interface definition. This enables you to provide an expression that will be processed at runtime to map one or more entity attributes to a DTO attribute.

public interface BookSummary {

	@Value("#{target.title + '-' + target.author.firstName}")
	String getBookNameAndAuthorName();
}

In the above example, Spring will concatenate the Book’s title and the firstName attribute of the associated Author to set the attribute bookNameAndAuthorName.

Internally, this projection uses the same approach as I explained before. Spring Data JPA fetches a Book entity and uses it to perform a programmatic mapping.

Entity Projections

Entities are the most commonly used projections. The persistence context manages all entities returned by a Spring Data repository. So, each change of an attribute will be persisted in the database and you can fetch lazily initialized associations. This creates performance overhead for read operations but makes entities the optimal projection for all write operations.

Entity projections are by far the easiest to use. All standard query methods provided by the Spring Data JPA repository methods return them. And, if you define your own repository method, you only need to use the entity class as the return type.

@Repository
public interface AuthorRepository extends CrudRepository<Author, Long> {
	@Query("select a from Author a left join fetch a.books")
	List<Author> getAuthorsAndBook();
}

Spring Data JPA then uses the mapping provided by the persistence provider. It selects all columns mapped by the entity classes and maps each returned record to a managed entity object.

Dynamic Projections

Add a type class parameter to your repository method to use the same query with different projections. This enables you to define the preferred returned type in your business code.

@Repository
public interface AuthorRepository extends CrudRepository<Author, Long> {
	<T> T findByLastName(String lastName, Class<T> type);	
}

Depending on the class you provide when you call the repository method, Spring Data JPA uses one of the previously described mechanisms to define the projection and map it. For example, if you provide a DTO class, Spring Data JPA generates a query with a constructor expression. Your persistence provider then selects the required database columns and returns a DTO object.

AuthorSummaryDTO authorSummaryDTO = authorRepo.findByLastName("Janssen", AuthorSummaryDTO.class);

Conclusion

Spring Data JPA supports all three projections defined by the JPA specification. Entities are the best fit for write operations. Moreover, you should use class-based DTO projections for read operations.

All other forms of projections are better avoided. Scalar value projections are very uncomfortable to use and make your code hard to maintain. Interface-based DTO projections don’t perform well because they fetch entities and map them in an additional step.


Tags


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.

Tools

Books and Courses

Coaching and Consulting

Leave a Reply

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"}