Spring Data JPA: Query result directly into DTO

Introduction

One of the hidden gems of Spring Data JPA is the possibility to put your query results directly into a DTO instead of having it transformed by a mapper on a higher level. Of course this depends on the question if you are okay with this way of handling data.

What is Spring data?

Spring Data itself is an abstraction layer between the way of how you query data and the underlying different data source types. The way how you use Spring Data to write your queries does not change. It will transform the queries into the corresponding underlying query languages for JPA, MongoDB, Couchbase, Neo4j a.s.o..

The special about Spring Data is that it is sufficient to just create interface methods. There is no need of implementing any Repositories or DataAccessObjects.

Example of a basic interface method in Spring Data JPA

This is just an example for a JpaRepository showing how Spring Data is working:

package de.smarterco.example.repositories;

import de.smarterco.example.entities.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    List<User> findAll();
}

The keywords:

• @Repository - Marks the class as a repository to get picked up by the dependency framework.
• JpaRepository - Extends our interface UserRepository by base functionality for the use with JPA datasources.
• <User, Long> - User is our entity on which this repository will perform its queries. The Long is the type of the id in the User entity.
• List<User> findAll() - Example of a method, which will retrieve all Users from the repository and store them into a List. Query methods in Spring Data start with the keyword find, followed by restrictions and parameters.

Using the @Query annotation

There is also another way of how you can achieve the same result using Spring Data JPA. Spring Data JPA offers you the keyword Query, which allows you to write your own queries with JPQL - Java Persistence Query Language.

package de.smarterco.example.repositories;

import de.smarterco.example.entities.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT u FROM User AS u")
    List<User> findAll();
}

Querying results directly to a DTO

This paragraph describes how to put results from a query directly into a DTO without mapping the data on a second layer outside of the Repository.

The first thing you will need is a DTO, like in our case our UserNameDTO.

Please note that you will need a constructor accepting all internal variables as parameters.

package de.smarterco.example.dto;

public class UserNameDTO {

    private Long id;
    private String name;

    public UserNameDTO(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }
}

Storing the data directly into the DTO (explanation below) is done by writing a JPQL query in the JpaRepository:

package de.smarterco.example.repositories;

import de.smarterco.example.dto.UserNameDTO;
import de.smarterco.example.entities.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT u FROM User AS u")
    List<User> findAll();

    @Query("SELECT new de.smarterco.example.dto.UserNameDTO(u.id, u.name) FROM User u WHERE u.name = :name")
    List<UserNameDTO> retrieveUsernameAsDTO(@Param("name") String name);
}

• List<UserNameDTO> retrieveUsernameAsDTO(...) - This is the new method, which will retrieve the results as a List of UserNameDTOs instead of User.
• @Param("name") String name - @Param helps the system to recognize the passed parameter and insert it into the query. Here :name.
• new de.smarterco.example.dto.UserNameDTO(u.id, u.name) - This is where the magic happens. The JPQL query creates a new UserNameDTO by the use of the complete package name and the parameterized constructor created before. The only thing left is to map the necessary User variables(u.id, u.name) onto the DTO.