How to Convert Native Query Results to DTO in a Spring Boot

When working with a Spring Boot project, you may encounter situations where you need to execute a native SQL query and map the results directly to a Data Transfer Object (DTO). This is particularly useful for performance optimization or when you need to fetch only specific columns from a database. In this blog post, we'll walk through the steps to convert native query results to a DTO in a Spring Boot project.

What is a DTO?

A Data Transfer Object (DTO) is a simple Java object used to encapsulate data and transfer it between different parts of an application. DTOs are particularly useful when we want to decouple our domain entities from the data representation sent to clients, ensuring that the client only receives the necessary data.

Step-by-Step Guide

1. Setting Up the Project

First, make sure you have a Spring Boot 3 project set up. You can create a new Spring Boot project using Spring Initializr. Select the following dependencies:

• Spring Data JPA
• Spring Web
• H2 Database (for simplicity, you can choose any other database)

2. Creating the Entity

Let's create a simple Employee entity.

package com.example.demo.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "employees")
public class Employee {
    @Id
    private Long id;
    private String firstName;
    private String lastName;
    private String email;

    // Getters and Setters
}

3. Defining the DTO

Next, create a DTO class that will represent the data we want to transfer.

package com.example.demo.dto;

public class EmployeeDTO {
    private String firstName;
    private String lastName;
    private String email;

    // Constructor
    public EmployeeDTO(String firstName, String lastName, String email) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
    }

    // Getters and Setters
}

4. Writing the Native Query

Create a repository to execute the native query and map the results to the DTO.

package com.example.demo.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query(value = "SELECT e.first_name AS firstName, e.last_name AS lastName, e.email AS email " +
                   "FROM employees e WHERE e.email = :email", nativeQuery = true)
    List<Object[]> findEmployeeByEmail(@Param("email") String email);
}

5. Converting the Native Query Results to DTO

Create a service class to handle the conversion of raw query results to DTOs using constructor-based dependency injection.

package com.example.demo.service;

import com.example.demo.dto.EmployeeDTO;
import com.example.demo.repository.EmployeeRepository;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.stream.Collectors;

@Service
public class EmployeeService {

    private final EmployeeRepository employeeRepository;

    public EmployeeService(EmployeeRepository employeeRepository) {
        this.employeeRepository = employeeRepository;
    }

    public List<EmployeeDTO> getEmployeeByEmail(String email) {
        List<Object[]> results = employeeRepository.findEmployeeByEmail(email);
        return results.stream()
                .map(result -> new EmployeeDTO(
                        (String) result[0],
                        (String) result[1],
                        (String) result[2]))
                .collect(Collectors.toList());
    }
}

6. Creating a REST Controller

Finally, create a REST controller to expose an endpoint for fetching the employee data.

package com.example.demo.controller;

import com.example.demo.dto.EmployeeDTO;
import com.example.demo.service.EmployeeService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class EmployeeController {

    private final EmployeeService employeeService;

    public EmployeeController(EmployeeService employeeService) {
        this.employeeService = employeeService;
    }

    @GetMapping("/employees")
    public List<EmployeeDTO> getEmployeesByEmail(@RequestParam String email) {
        return employeeService.getEmployeeByEmail(email);
    }
}

7. Running the Application

Run your Spring Boot application and test the endpoint by navigating to http://localhost:8080/employees?email=test@example.com with the appropriate email parameter.

Conclusion

In this blog post, we've demonstrated how to convert native query results into a DTO in a Spring Boot 3 project using constructor-based dependency injection. By following these steps, you can ensure that your application remains clean and that your clients only receive the data they need. This approach also helps in maintaining the separation of concerns and improves the overall architecture of your Spring Boot application.

Happy coding!