Blog

Understanding DTO Projection with use case

Introduction

• Query projection is one of the most important things when we write a query in JPA since it determines which column we are selecting from the table and how to map that to the appropriate data type in Java (i.e. dto, entity, object[], tuple, etc.)
• In this article we will learn about one such projection type called DTO projection and when to use it.

Use Case

• We have an account table as defined below.

@Entity
@Table(name="ACCOUNTS")
public class Account {
    @Id @GeneratedValue(strategy= GenerationType.SEQUENCE, generator = "accounts_seq")
    @SequenceGenerator(name = "accounts_seq", sequenceName = "accounts_seq", allocationSize = 1)
    @Column(name = "user_id")
    private int userId;
    private String username;
    private String password;
    private String email;
    private Timestamp createdOn;
    private Timestamp lastLogin;
}

• We would like to get a list of user credentials that contains username and password instead of all attributes of the account entity.

DTO Projection

• In DTO projection, we can define a DTO for the query result which is unmanaged by JPA.
• Defining DTO requires attributes, getters, setters, and parameterized constructors.
• JPQL then uses this DTO definition to map the query result.

public class UserCredDTO{
    private String username;
    private String password;

    public UserCredDTO(String username, String password) {
        this.username = username;
        this.password = password;
    }

    // getters, setters, to_string
}

• Once we defined DTO for the query result, we can add a method to the repository interface and define the query using @Query, which takes JPQL query.
• Here we are querying the account table to get a list of usernames and passwords.
• One thing to remember is that we need to call fully qualified DTO constructor call with required args which we query from DB, and JPQL then constructs the necessary DTO.

public interface AccountRepository extends JpaRepository<Account, Integer> {
    @Query("SELECT new com.example.springpostgres.dto.UserCredDTO(a.username, a.password) from Account a")
    List<UserCredDTO> findAllUserCredentials();
}

Now we are ready to test our setup. we are printing all the accounts for brevity.

 @Transactional
    public void testDTOProjection(){
        List<UserCredDTO> allUserCredentials = accountRepository.findAllUserCredentials();
        allUserCredentials.forEach(System.out::println);
    }

Output

• We get the list of UserCredDTO as a result.

• We can also call the query directly in the logic class using entitymanager createQuery method instead of defining it in the repository.

    @Transactional
    public void testDTOProjection(){
        TypedQuery<UserCredDTO> query = em.createQuery("SELECT new com.example.springpostgres.dto.UserCredDTO(a.username, a.password) from Account a", UserCredDTO.class);
        query.getResultList().forEach(System.out::println);
    }

• We get the result without any issues

Using Record for DTO

• One of the optimization we can do is we can define records which help us to generate constructors and getters and setters instead of defining them explicitly.

public record UserCredRecord(String username, String password) { }

• Now we can use UserCredRecord as the data type for query results.

 @Transactional
    public void testDTOProjection() {
        TypedQuery<UserCredRecord> query1 = em.createQuery("SELECT new com.example.springpostgres.dto.UserCredRecord(a.username, a.password) from Account a", UserCredRecord.class);
        query1.getResultList().forEach(System.out::println);
    }

• We get the output without any issues.

Conclusion

• In this article, we learned about DTO projection in Spring Data JPA. We also discussed how & when we can use it.

Before You Leave

• Upgrade your Java skills with Grokking the Java Interview

• If you want to upskill your Java skills, you should definitely check out
  [NEW] Master Spring Boot 3 & Spring Framework 6 with Java
  [ 38 hrs content, 4.7/5 stars, 6+ students already enrolled]