Spring Data JPA – How to Return DTOs from Native Queries

---

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!

---

When using Spring Data JPA, you are used to derived and custom queries that return the result in your preferred format. A typical example is a DTO projection, which is the most efficient one for read-only operations. To use it in a derived or custom JPQL query, you only need to change the return type of your repository method to your DTO class or interface. But when using a native query, some limitations make DTOs a little harder to use.

In this article, I will show you how to use interface-based DTO projections, the problem when using class-based DTO projections and how to use them anyway.

Interface-based DTO projections

Let’s start with the good news: You can use an interface-based DTO projection with a native query in the same way you use it with a derived or custom JPQL query. The only thing you need to take care off is the alias of each column.

You first need to define an interface that defines a getter method for each attribute your projection shall contain. At runtime, Spring Data JPA then generates a class that implements that interface.

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

You can then use that interface as the return type of a repository method. To assign a native query to that method, you need to annotate it with @Query, provide the native SQL statement, and set the native attribute to true.

public interface ChessPlayerRepository extends JpaRepository<ChessPlayer, Long> {

    @Query(value = "SELECT p.first_name as firstName, p.last_name as lastName FROM Chess_Player p WHERE id = :id", nativeQuery = true)
    PlayerName findPlayerNameById(Long id);
}

When you define the query, you should double-check if the alias of each column can get mapped to the corresponding getter method. In my example, I’m using snake case for the database columns, and Spring Data JPA wouldn’t be able to map that to the getter methods, which are in camel case. I, therefore, defined an alias in camel case for each column. Spring Data JPA then handles the mapping of the query result to the generated implementation of the interface automatically. If it can’t find a matching alias for a getter method, a call of that method returns null.

Class-based DTO projections

Even though Spring Data JPA is more flexible, the JPA specification only supports class-based DTO projections. It requires a Java class with a constructor that initializes all attributes you want to use.

public class PlayerNameDto {
    
    private String firstName;
    private String lastName;
    
    public PlayerNameDto(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }

    public String getFirstName() {
        return firstName;
    }
    
    public String getLastName() {
        return lastName;
    }
    
}

You can easily use this class as a projection in derived queries and custom JPQL queries. Your custom query needs to use a constructor expression that defines the constructor you want to call. Spring Data JPA generates such an expression when deriving the query statement from the method name.

The Automatic Mapping Doesn’t Work

Unfortunately, you can’t rely on Spring Data JPA’s automatic mapping feature when using a native query. It throws a ConverterNotFoundException similar to the following one:

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.thorben.janssen.spring.data.model.PlayerNameDto]

You have 2 options to avoid this exception:

1. Define and reference a @NamedNativeQuery with an @SqlResultSetMapping.
2. Use a fragment interface to provide your own implementation of a repository method.

Fragment interfaces are by far the most flexible approach to add your own functionality to a repository. It enables you to provide your own implementation using all features and APIs defined by the JPA specification and provided by your persistence provider. But it also adds unnecessary complexity to your project if you only want to use a class-based DTO projection. Because of that, I will not show this approach in this article. If you want to learn more about fragment interfaces, please read my article on composite repositories.

Defining a @NamedNativeQuery and an @SqlResultSetMapping is by far the easier approach and the one I want to show you in this article.

@NamedNativeQuery with an @SqlResultSetMapping

As explained in a previous article, for every repository method not annotated with @Query, Spring Data JPA checks if there is a named query with a name that follows the pattern <entity class>.<method name>. If it finds a @NamedQuery or @NamedNativeQuery with that name, it instantiates and executes that query instead of deriving the statement from the method name.

I use that feature in the following repository definition to execute a @NamedNativeQuery with the name ChessPlayer.findPlayerNameDtoById_Named.

public interface ChessPlayerRepository extends JpaRepository<ChessPlayer, Long> {
    @Query(nativeQuery = true)
    PlayerNameDto findPlayerNameDtoById_Named(Long id);
}

I defined that query together with a constructor result mapping on my ChessPlayer entity class.

@NamedNativeQuery(name = "ChessPlayer.findPlayerNameDtoById_Named",
                  query = "SELECT p.first_name as first, p.last_name as last FROM Chess_Player p WHERE id = :id",
                  resultSetMapping = "Mapping.PlayerNameDto")
@SqlResultSetMapping(name = "Mapping.PlayerNameDto",
                     classes = @ConstructorResult(targetClass = PlayerNameDto.class,
                                                  columns = {@ColumnResult(name = "first"),
                                                             @ColumnResult(name = "last")}))
@Entity
public class ChessPlayer { ... }

As I explained in great detail in a series of articles, the @SqlResultSetMapping annotation enables you to define the mapping of a native query result to entities, DTOs, scalar values, and any combination of these three. In this case, I use it to define a constructor call of my PlayerNameDto class and tell Hibernate to provide the columns first and last of the result set as parameters.

You can reference an @SqlResultSetMapping in the definition of your @NamedNativeQuery. The mapping gets automatically applied to the result set when you instantiate and execute the query. That’s also the case when Spring Data JPA executes this query when you call the corresponding repository method. This is the easiest option to use a class-based DTO project with a native query.

Conclusion

As I showed in this article, you can easily use Spring Data JPA’s interface-based DTO projections with native queries. You only need to set the interface as the return type of the repository method that executes the native query.

Spring Data JPA doesn’t provide an automatic mapping of class-based DTOs for native queries. The easiest way to use this projection is to define your query as a @NamedNativeQuery and assign an @SqlResultSetMapping that defines a constructor result mapping. The instantiation of the DTO objects is then handled by the underlying persistence provider when Spring Data JPA executes the @NamedNativeQuery.