Why, When and How to Use DTO Projections with JPA and Hibernate

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

DTOs are easy to use and the most efficient projection for read-only operations. So, whenever you don’t need to change the requested information, you should prefer a DTO projection.

But what exactly is a DTO? How does such a projection work with Hibernate? And how can you use it in your queries?

I will answer all of these questions in this article. And I will even show you a way to use a DTO projection that doesn’t require a custom DTO class.

What is a DTO?

DTO is an abbreviation that stands for Data Transfer Object. Originally, Martin Fowler defined a DTO in his famous book Patterns of Enterprise Application Architecture as:

An object that carries data between processes in order to reduce the number of method calls.
Source: https://martinfowler.com/eaaCatalog/dataTransferObject.html

Several years ago, when we used EJB 1 and 2 to build our applications, reducing the number of remote method calls was important. Nowadays, we use other technologies and design our systems differently so that we no longer need DTOs in their original form.

But that doesn’t mean that you can forget about the pattern. Its definition has evolved, and you can find DTOs in almost all applications. They are used as a specialized class to transfer data that you selected in a database query. Now, the goal of a DTO is to read the required information with as few database queries as possible and to provide it in an efficient and easy to use form. That’s still pretty close to Martin Fowler’s definition, isn’t it?

Let’s take a look at an example of a typical DTO.

One DTO instead of multiple entities

If you’re building an online book store, you probably have a Book and an Author entity and a many-to-one association between them.

These entities model all information about books and their authors. But when a user searches for a book by its title or author, you don’t need all this information. On the search result page, you probably only want to show the title, price, and the name of the author.

This is where a DTO class comes into play. It’s a simple Java class that you can specifically design for this use case. So, in this example, the BookWithAuthorNames class only has the 4 attributes that are required for the search result page. These are the id, title, and price of the book and a String with the name of the author.

This representation is obviously easier to use and more efficient than a Book entity with a List of associated Author entities. The DTO only contains the required information, and it already concatenated the author’s first and last name into a String.

Let’s now take a closer look at how your persistence provider creates a DTO projection and how you can use them in your queries.

How do DTO projections work with JPA and Hibernate

Your database and the SQL language don’t know about your Java classes. They only know tables, columns, views, stored procedures, and other database-related concepts.

So, your persistence provider, e.g., Hibernate or EclipseLink, need to handle the DTO projection. It does that when it processes the result set of your query. Instead of mapping each row to an Object[], your persistence provider calls the constructor of your DTO to instantiate a new object. So, you need to make sure that your DTO always has a constructor that matches the columns selected by your query. But more about that later.

Using DTO projections with JPA and Hibernate

After you defined your DTO class, you can use it as a projection with JPQL, criteria and native queries. For each kind of query, you need to define the DTO projection differently, but the result is always the same. Your persistence provider instantiates a new DTO object for each record in the result set.

DTO projections in JPQL

JPQL queries offer a feature called constructor expression. With such an expression you can define a constructor call with the keyword new followed by the fully qualified class name of your DTO and a list of constructor parameters in curly braces.

Here you can see an example of a query that returns a list of BookWithAuthorNames object. I provide the fully qualified class name, reference the Book entity attributes id, title and price and use the function concat to concatenate the firstName and lastName attribute of the Author entity. Based on this constructor expression, Hibernate tries to call a constructor on the BookWithAuthorNames object that takes a Long, a String, a Double and a String as parameters.

TypedQuery<BookWithAuthorNames> q = em.createQuery(
		"SELECT new org.thoughts.on.java.model.BookWithAuthorNames(b.id, b.title, b.price, concat(a.firstName, ' ', a.lastName)) FROM Book b JOIN b.author a WHERE b.title LIKE :title",
		BookWithAuthorNames.class);
q.setParameter("title", "%Hibernate Tips%");
List<BookWithAuthorNames> books = q.getResultList();

for (BookWithAuthorNames b : books) {
	log.info(b);
}

When you execute this query and activate the logging of SQL statements, you can see that Hibernate generated an SQL query that selects the required database columns and concatenates the author names.

08:45:05,374 DEBUG [org.hibernate.SQL] - 
    select
        book0_.id as col_0_0_,
        book0_.title as col_1_0_,
        book0_.price as col_2_0_,
        (author1_.firstName||' '||author1_.lastName) as col_3_0_ 
    from
        Book book0_ 
    inner join
        Author author1_ 
            on book0_.author_id=author1_.id 
    where
        book0_.title like ?
08:45:05,394 INFO  [org.thoughts.on.java.date.TestDtoProjection] - BookWithAuthorNames [bookId=1, title=Hibernate Tips, price=19.99, authorNames=Thorben Janssen]

DTO projections in criteria queries

You define a DTO projection in a CriteriaQuery in a pretty similar way as you do in JPQL. But instead of using the new keyword to specify the constructor call in a query String, you call the construct method on the CriteriaBuilder with a reference to the DTO class and a List of constructor parameters.

The following code snippet creates the same query as you saw in the JPQL example. The Author_ and the Book_ classes are part of the JPA metamodel and enable you to reference entity attributes in a typesafe way.

// Create query
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<BookWithAuthorNames> cq = cb
		.createQuery(BookWithAuthorNames.class);
// Define FROM clause
Root<Book> root = cq.from(Book.class);
Join<Book, Author> author = root.join(Book_.author);

// Define DTO projection
cq.select(cb.construct(
		BookWithAuthorNames.class,
		root.get(Book_.id),
		root.get(Book_.title),
		root.get(Book_.price),
		cb.concat(author.get(Author_.firstName), ' ',
				author.get(Author_.lastName))));

// Define WHERE clause
ParameterExpression<String> paramTitle = cb.parameter(String.class);
cq.where(cb.like(root.get(Book_.title), paramTitle));

// Execute query
TypedQuery<BookWithAuthorNames> q = em.createQuery(cq);
q.setParameter(paramTitle, "%Hibernate Tips%");
List<BookWithAuthorNames> books = q.getResultList();

for (BookWithAuthorNames b : books) {
	log.info(b);
}

And when you execute the CriteriaQuery, the query looks the same as in the JPQL example.

08:59:58,719 DEBUG [org.hibernate.SQL] - 
    select
        book0_.id as col_0_0_,
        book0_.title as col_1_0_,
        book0_.price as col_2_0_,
        (author1_.firstName||' '||author1_.lastName) as col_3_0_ 
    from
        Book book0_ 
    inner join
        Author author1_ 
            on book0_.author_id=author1_.id 
    where
        book0_.title like ?
08:59:58,744 INFO  [org.thoughts.on.java.date.TestDtoProjection] - BookWithAuthorNames [bookId=1, title=Hibernate Tips, price=19.99, authorNames=Thorben Janssen]

DTO projections for native SQL queries

Hibernate sends native SQL queries directly to the database and doesn’t parse them. That’s one of the reasons why you can use all the features supported by your database in a native query.

But that also means that you can’t use a constructor expression to define the DTO projection within your query. You need to define an @SqlResultSetMapping instead. I explained these mappings in great details in a previous series of articles (part 1: basic mappings, part 2: complex mappings, part 3: constructor mappings, part 4: Hibernate specific mappings).

Let’s focus on the DTO projection. The mapping follows the same basic concept as the previous ones. But this time, you define it using an @SqlResultMapping and a @ConstructorResult annotation. As you might guess from its name, the @ConstructorResult annotation defines the constructor call and you need to provide a reference to the DTO class and an array of @ColumnResult annotations, which specify the constructor parameters.

@Entity(name = "Book")
@SqlResultSetMapping(
		name = "BookWithAuthorNamesMapping",
		classes = @ConstructorResult(
				targetClass = BookWithAuthorNames.class,
				columns = { @ColumnResult(name = "id", type = Long.class), 
							@ColumnResult(name = "title"), 
							@ColumnResult(name = "price"), 
							@ColumnResult(name = "authorName")}))
public class Book { ... }

After you defined the @SqlResultSetMapping, you can implement your native query and provide the name of the mapping as the 2nd parameter of the createNativeQuery method.

Query q = em.createNativeQuery(
				"SELECT b.id, b.title, b.price, a.firstName || ' ' || a.lastName as authorName FROM Book b JOIN Author a ON b.author_id = a.id WHERE b.title LIKE :title",
				"BookWithAuthorNamesMapping");
q.setParameter("title", "%Hibernate Tips%");
List<BookWithAuthorNames> books = q.getResultList();

for (BookWithAuthorNames b : books) {
	log.info(b);
}

When you execute this native query, Hibernate sends the defined SQL statement to the database and uses the @SqlResultSetMapping to map the retrieved SQL result set.

09:16:00,271 DEBUG [org.hibernate.SQL] - 
    SELECT
        b.id,
        b.title,
        b.price,
        a.firstName || ' ' || a.lastName as authorName 
    FROM
        Book b 
    JOIN
        Author a 
            ON b.author_id = a.id 
    WHERE
        b.title LIKE ?
09:16:00,295 INFO  [org.thoughts.on.java.date.TestDtoProjection] - BookWithAuthorNames [bookId=1, title=Hibernate Tips, price=19.99, authorNames=Thorben Janssen]

DTO projections without a custom DTO class

All of the previous examples used a custom DTO class. That’s a good approach, if you want to send the result to a client or if you process it in a different part of your application.

But it’s also an unnecessary overhead if you only want to execute a query and immediately process the result. In that case, a Tuple projection might be the easier option.

JPA’s Tuple interface provides a generic and comfortable way to access the elements of a query result. You can use it to access the elements by their index or alias, and you can provide additional type information to cast them automatically.

As I will show you in the following sections, you can use the Tuple interface with all 3 types of queries.

Using the Tuple interface with JPQL

If you want to use the Tuple interface in a JPQL query, you need to provide a reference to the interface as the 2nd parameter of the createQuery method. And you should also define an alias for each selected entity attribute. You can then provide this alias to the get method of the Tuple interface to retrieve the selected value.

TypedQuery<Tuple> q = em.createQuery(
				"SELECT b.id as id, b.title as title, b.price as price, concat(a.firstName, ' ', a.lastName) as authorName FROM Book b JOIN b.author a WHERE b.title LIKE :title",
				Tuple.class);
q.setParameter("title", "%Hibernate Tips%");
List<Tuple> books = q.getResultList();

for (Tuple b : books) {
	log.info("ID: " + b.get("id"));
	log.info("Title: " + b.get("title"));
	log.info("Price: " + b.get("price"));
	log.info("Author: " + b.get("authorName"));
}

As expected, the Tuple projection doesn’t have any effect on the generated SQL query.

09:30:10,042 DEBUG [org.hibernate.SQL] - 
    select
        book0_.id as col_0_0_,
        book0_.title as col_1_0_,
        book0_.price as col_2_0_,
        (author1_.firstName||' '||author1_.lastName) as col_3_0_ 
    from
        Book book0_ 
    inner join
        Author author1_ 
            on book0_.author_id=author1_.id 
    where
        book0_.title like ?
09:30:10,061 INFO  [org.thoughts.on.java.date.TestDtoProjection] - ID: 1
09:30:10,061 INFO  [org.thoughts.on.java.date.TestDtoProjection] - Title: Hibernate Tips
09:30:10,061 INFO  [org.thoughts.on.java.date.TestDtoProjection] - Price: 19.99
09:30:10,062 INFO  [org.thoughts.on.java.date.TestDtoProjection] - Author: Thorben Janssen

Using the Tuple interface with a CriteriaQuery

A CriteriaQuery that returns a Tuple interface is pretty similar to one that returns a DTO projection. Instead of the DTO class, you now create a query that returns a Tuple interface. You can then use the multiselect method of the CriteriaQuery interface to select multiple entity attributes. If you want to access the elements of your Tuple by their alias, you need to specify the alias while selecting them.

After you executed the query, you can use the get method of the Tuple interface to retrieve the selected elements by their index or alias.

// Create query
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);
// Define FROM clause
Root<Book> root = cq.from(Book.class);
Join<Book, Author> author = root.join(Book_.author);

// Define Tuple projection
cq.multiselect(root.get(Book_.id).alias("id"),
		root.get(Book_.title).alias("title"),
		root.get(Book_.price).alias("price"),
		cb.concat(author.get(Author_.firstName), ' ',
				author.get(Author_.lastName)).alias("authorName"));

// Define WHERE clause
ParameterExpression<String> paramTitle = cb.parameter(String.class);
cq.where(cb.like(root.get(Book_.title), paramTitle));

// Execute query
TypedQuery<Tuple> q = em.createQuery(cq);
q.setParameter(paramTitle, "%Hibernate Tips%");
List<Tuple> books = q.getResultList();

for (Tuple b : books) {
	log.info("ID: " + b.get("id"));
	log.info("Title: " + b.get("title"));
	log.info("Price: " + b.get("price"));
	log.info("Author: " + b.get("authorName"));
}

If you run this code, you can see that the Tuple projection doesn’t affect the generated SQL statement. Hibernate selects the referenced entity attributes and concatenates the first and last name of the author. It then maps each record of the result set to a Tuple instance.

10:00:17,637 DEBUG [org.hibernate.SQL] - 
    select
        book0_.id as col_0_0_,
        book0_.title as col_1_0_,
        book0_.price as col_2_0_,
        ((author1_.firstName||' ')||author1_.lastName) as col_3_0_ 
    from
        Book book0_ 
    inner join
        Author author1_ 
            on book0_.author_id=author1_.id 
    where
        book0_.title like ?
10:00:17,662 INFO  [org.thoughts.on.java.date.TestDtoProjection] - ID: 1
10:00:17,662 INFO  [org.thoughts.on.java.date.TestDtoProjection] - Title: Hibernate Tips
10:00:17,662 INFO  [org.thoughts.on.java.date.TestDtoProjection] - Price: 19.99
10:00:17,662 INFO  [org.thoughts.on.java.date.TestDtoProjection] - Author: Thorben Janssen

Using the Tuple interface with a native SQL query

You don’t need to provide an @SqlResultSetMapping if you want to use a Tuple projection with a native SQL query. You only need to reference the Tuple interface as the 2nd parameter of the createNativeQuery method. And to make it even better, in contrast to the previously discussed JPQL and Criteria queries, you don’t need to provide an alias, if you select a simple database column.

Query q = em.createNativeQuery(
				"SELECT b.id, b.title, b.price, a.firstName || ' ' || a.lastName as authorName FROM Book b JOIN Author a ON b.author_id = a.id WHERE b.title LIKE :title",
				Tuple.class);
q.setParameter("title", "%Hibernate Tips%");
List books = q.getResultList();

for (Tuple b : books) {
	log.info("ID: " + b.get("id"));
	log.info("Title: " + b.get("title"));
	log.info("Price: " + b.get("price"));
	log.info("Author: " + b.get("authorName"));
}

When you execute this code, Hibernate sends the defined SQL query to the database and maps each record of the result set to a Tuple instance.

10:02:31,707 DEBUG [org.hibernate.SQL] - 
    SELECT
        b.id,
        b.title,
        b.price,
        a.firstName || ' ' || a.lastName as authorName 
    FROM
        Book b 
    JOIN
        Author a 
            ON b.author_id = a.id 
    WHERE
        b.title LIKE ?
10:02:31,740 INFO  [org.thoughts.on.java.date.TestDtoProjection] - ID: 1
10:02:31,740 INFO  [org.thoughts.on.java.date.TestDtoProjection] - Title: Hibernate Tips
10:02:31,740 INFO  [org.thoughts.on.java.date.TestDtoProjection] - Price: 19.99
10:02:31,740 INFO  [org.thoughts.on.java.date.TestDtoProjection] - Author: Thorben Janssen

Conclusion

DTOs are the most efficient projection for read operations. You should, therefore, use it whenever you don’t need to change the requested data.

As you have learned in this article, you can use DTO projections with all 3 kinds of queries supported by JPA:

• In JPQL, you can use the new keyword to define a constructor expression.
• The construct method of the CriteriaBuilder interface enables you to specify a constructor expression for a CriteriaQuery.
• Native SQL queries require an @SqlResultSetMapping to define the mapping of the query result to one or more DTO instances.

If you don’t want to implement a custom DTO class, you can use the Tuple interface with all 3 kinds of queries.