How to return DTO from JPA Repository

by

Last updated on October 5th, 2024

Return (Data Transfer Object) DTO from the JPA Repository is commonly used in the Spring Data JPA application. DTO is used to return data without exposing the JPA Entity class and sending a limited amount of data is needed for clients via API endpoints. We can return DTO from the JPA Repository using JPQL with Constructor Expressions, Spring Data JPA Projections and Native SQL Queries. In this topic, we will learn how to return from the JPA Repository using different approaches.

Return DTO from JPA Repository

Approaches to Returning DTOs

  • Using JPQL with Constructor Expressions
  • Using Spring Data JPA Projections
  • Using Native SQL Queries

Using JPQL with Constructor Expressions

In this approach use JPQL to directly instantiate the DTO class objects with their constructors. We need to create a DTO class and then add a custom method annotated with @Query annotation in the repository interface.

Step 1: Creating a DTO class

Create a DTO class with a constructor that matches the fields which we need.

import lombok.AllArgsConstructor;

import lombok.Data;

@Data

@AllArgsConstructor

public class WebSeriesDTO {

private String name;

private String ott;

}

Step 2: Add a custom method in the repository interface

Add a custom method to the repository interface and use JPQL and this method returns DTOs. 

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Query;

public interface WebSeriesRepository extends JpaRepository<WebSeries, Long> {

@Query("SELECT new com.springjava.dto.WebSeriesDTO(w.name, w.ott) FROM WebSeries w")

List<WebSeriesDTO> findAllWebSeriesDTOs();

}
  • Constructor Expression: new com.springjava.dto.WebSeriesDTO(ws.name, ws.ott) instantiates the WebSeriesDTO for each row returned through the query.
  • Field Matching: Ensure the constructor parameters have matched the query’s order and type of fields.

Using Spring Data JPA Projections

In this approach, we can create an interface to return DTO from the repository without making the class’s explicit object.

Step 1: Create Projection Interface

Create an interface and define the getter methods for the fields that we want to include in DTO.

public interface WebSeriesDTOI {

String getName();

String getOtt();

}

Step 2: Add the method to the repository interface

Add a method in the repository that uses a projection interface.

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Query;

public interface WebSeriesRepository extends JpaRepository<WebSeries, Long> {

List<WebSeriesDTOI> findBy();

}

Using Native SQL Queries

In this approach return DTO from JPA Repository using Interface-based DTO and Class-based DTO projection. Using this class-based DTO projection the automatic mapping doesn’t work then we need to use @NamedNativeQuery with an @SqlResultSetMapping in the JPA Entity class.

Interface-based DTO with Native Query

In this way, we need to create an Interface with getter methods for fields included in the DTO then add a custom method annotation @Query annotation with nativeQuery = true  attribute in the repository interface.

Step 1: Create an interface

public interface WebSeriesDTOI {

String getName();

String getOtt();

}

Step 2: Add the custom method to the repository interface

Add a custom method which uses the interface.

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Query;

public interface WebSeriesRepository extends JpaRepository<WebSeries, Long> {

@Query(value="SELECT w.name AS name, w.ott AS ott FROM web_series w",nativeQuery = true)

List<WebSeriesDTO> findAllWebSeriesDTONative();

}

Class-based DTO with Native Query

In this way, we need to use @NamedNativeQuery with an @SqlResultSetMapping in the JPA Entity class to define the query and DTO class. Then we need to add a custom method in the repository.

Step 1: Create a DTO class

Create a DTO class with fields and constructor.

import lombok.AllArgsConstructor;

import lombok.Data;

@Data

@AllArgsConstructor

public class WebSeriesDTO {

private String name;

private String ott;

}

Step 2: Add a custom method to the repository interface

Add a custom method with annotated @Query annotation with its nativeQuery=true attribute in the repository interface.

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Query;

public interface WebSeriesRepository extends JpaRepository<WebSeries, Long> {

@Query(nativeQuery = true)

List<WebSeriesDTO> findAllWithClassBased();

}

Step 3: Use @NamedNativeQuery with an @SqlResultSetMapping

Use @NamedNativeQuery with an @SqlResultSetMapping in the JPA entity class.

import jakarta.persistence.ColumnResult;

import jakarta.persistence.ConstructorResult;

import jakarta.persistence.Entity;

import jakarta.persistence.GeneratedValue;

import jakarta.persistence.GenerationType;

import jakarta.persistence.Id;

import jakarta.persistence.NamedNativeQuery;

import jakarta.persistence.SqlResultSetMapping;

import lombok.Data;

@NamedNativeQuery(name = "WebSeries.findAllWithClassBased",

query = "SELECT w.name AS name, w.ott AS ott FROM web_series w",

resultSetMapping = "Mapping.WebSeriesDTO")

@SqlResultSetMapping(name = "Mapping.WebSeriesDTO",

  classes = @ConstructorResult(targetClass = WebSeriesDTO.class,

                               columns = {@ColumnResult(name = "name"),

                                          @ColumnResult(name = "ott")}))

@Data

@Entity

public class WebSeries {

@Id

@GeneratedValue(strategy = GenerationType.IDENTITY)

private Long id;

private String name;

private String season;

private String released;

private String ott;

}

Step-by-Step Guide to Return DTO from JPA Repository

Let’s make a Spring Boot Application step-by-step guide to returning DTO from the JPA Repository to get records from the table in the JPA Repository. We will create an example to provide names and ott of the web series to the client through API endpoints through the approaches mentioned above.  

These are the following steps:

  1. Create a Spring Boot Project
  2. Setup in the IDE
  3. Configure H2 Database
  4. Create a JPA Entity
  5. Create a DTO class
  6. Create a DTO interface
  7. Create a Repository Interface
  8. Create a Service Interface
  9. Implement the Service Interface
  10. Create a Controller
  11. Run the Spring Boot Application

1. Create a Spring Boot Project

  We are creating a Spring Boot Project from the web tool Spring Initializr. By Providing details for the project and select the following Maven dependencies:

  • Spring Web
  • Spring Data JPA
  • H2 Database
  • Lombok

2. Setup in the IDE

We use Eclipse IDE to set up and configure the created Spring Boot Project. You can use other IDE to setup the project.

Project Structure of Spring Boot

This image shows the project structure of Spring Boot in Eclipse IDE.

Return DTO from JPA Repository

Maven Dependency

Here is the complete maven dependencies file pom.xml for the project which will implement methods to use Group By in Spring Boot JPA.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project
	xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.3.0</version>
		<relativePath/>
		<!-- lookup parent from repository -->
	</parent>
	<groupId>com.springjava</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>17</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

3. Configure H2 Database

We are going to configure the H2 database connection in the application.properties file.

application.properties

# H2 Database Configuration
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update
spring.h2.console.enabled=true

4. Create a JPA Entity

Let’s create a JPA Entity class. For example, consider a WebSeries entity and use Lombok for generating setter and getter methods, a constructor, etc.

WebSeries.java

package com.springjava.entity;

import com.springjava.dto.WebSeriesDTO;

import jakarta.persistence.ColumnResult;

import jakarta.persistence.ConstructorResult;

import jakarta.persistence.Entity;

import jakarta.persistence.GeneratedValue;

import jakarta.persistence.GenerationType;

import jakarta.persistence.Id;

import jakarta.persistence.NamedNativeQuery;

import jakarta.persistence.SqlResultSetMapping;

import lombok.Data;

@NamedNativeQuery(name = "WebSeries.findAllWebSeriesDTONative",

  query = "SELECT w.name AS name, w.ott AS ott FROM web_series w",

  resultSetMapping = "Mapping.WebSeriesDTO")

@SqlResultSetMapping(name = "Mapping.WebSeriesDTO",

  classes = @ConstructorResult(targetClass = WebSeriesDTO.class,

    columns = {
      @ColumnResult(name = "name"),

      @ColumnResult(name = "ott")
    }))

@Data

@Entity

public class WebSeries {

  @Id

  @GeneratedValue(strategy = GenerationType.IDENTITY)

  private Long id;

  private String name;

  private String season;

  private String released;

  private String ott;

}

5. Create a DTO class

We are creating a DTO class and using Lombok annotations @Getter for generating methods and @AllArgsConstructor for generating a constructor with arguments.

WebSeriesDTO.java

package com.springjava.dto;

import lombok.AllArgsConstructor;

import lombok.Getter;

@AllArgsConstructor

@Getter

public class WebSeriesDTO {

  private String name;

  private String ott;

}

6. Create a DTO interface

We are creating a projection interface WebSeriesDTOI with getter methods.

WebSeriesDTOI.java

package com.springjava.dto;

public interface WebSeriesDTOI {

  String getName();

  String getOtt();

}

7. Create a Repository Interface

Create a repository interface for the WebSeries JPA Entity class that interface extends the JpaRepository interface to perform persistence operations on the web_series database table.

WebSeriesRepository.java

package com.springjava.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Query;

import com.springjava.dto.WebSeriesDTO;

import com.springjava.dto.WebSeriesDTOI;

import com.springjava.entity.WebSeries;

public interface WebSeriesRepository extends JpaRepository < WebSeries, Long > {

  @Query("SELECT new com.springjava.dto.WebSeriesDTO(w.name, w.ott) FROM WebSeries w")

  List < WebSeriesDTO > findAllWebSeriesDTOs();

  List < WebSeriesDTOI > findBy();

  @Query(value = "SELECT w.name AS name, w.ott AS ott FROM web_series w", nativeQuery = true)

  List < WebSeriesDTOI > findAllWithInterfaceBased();

  @Query(nativeQuery = true)

  List < WebSeriesDTO > findAllWithClassBased();

}

8. Create a Service Interface

Create a Service interface WebSeriesService with some method declaration.

WebSeriesService.java

package com.springjava.service;

import java.util.List;

import com.springjava.dto.WebSeriesDTO;

import com.springjava.dto.WebSeriesDTOI;

import com.springjava.entity.WebSeries;

public interface WebSeriesService {

  void saveAll(List < WebSeries > listWebSeries);

  List < WebSeriesDTO > getAllWebSeriesDTO();

  List < WebSeriesDTOI > getAllWebSeriesDTOI();

  List < WebSeriesDTOI > getAllWithInterfaceBased();

  List < WebSeriesDTO > getAllWithClassBased();

}

9. Implement the Service Interface

Implement the WebSeriesService interface in the WebSeriesServiceImpl class. This class is annotated with @Service annotation, where we inject WebSeriesRepository to call all its methods.

WebSeriesServiceImpl.java

package com.springjava.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import com.springjava.dto.WebSeriesDTO;

import com.springjava.dto.WebSeriesDTOI;

import com.springjava.entity.WebSeries;

import com.springjava.repository.WebSeriesRepository;

@Service

public class WebSeriesServiceImpl implements WebSeriesService {

  @Autowired

  private WebSeriesRepository webSeriesRepo;

  @Override

  public void saveAll(List < WebSeries > listWebSeries) {

    webSeriesRepo.saveAll(listWebSeries);

  }

  @Override

  public List < WebSeriesDTO > getAllWebSeriesDTO() {

    return webSeriesRepo.findAllWebSeriesDTOs();

  }

  @Override

  public List < WebSeriesDTOI > getAllWebSeriesDTOI() {

    return webSeriesRepo.findBy();

  }

  @Override

  public List < WebSeriesDTOI > getAllWithInterfaceBased() {

    return webSeriesRepo.findAllWithInterfaceBased();

  }

  @Override

  public List < WebSeriesDTO > getAllWithClassBased() {

    return webSeriesRepo.findAllWithClassBased();

  }

}

10. Create a Controller

Create a controller class WebSeriesController. This is annotated with @RestController to make this class a RestController.

WebSeriesController.java

package com.springjava.controller;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.web.bind.annotation.RequestBody;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import com.springjava.dto.WebSeriesDTO;

import com.springjava.dto.WebSeriesDTOI;

import com.springjava.entity.WebSeries;

import com.springjava.service.WebSeriesService;

@RestController

@RequestMapping("/api/webseries")

public class WebSeriesController {

  @Autowired

  private WebSeriesService webSeriesService;

  @PostMapping("/save-all")

  public ResponseEntity < ? > save(@RequestBody List < WebSeries > listWebSeries) {

    Map < String, Object > respWebSeries = new LinkedHashMap < String, Object > ();

    webSeriesService.saveAll(listWebSeries);

    respWebSeries.put("status", 1);

    respWebSeries.put("message", "Record is Saved Successfully!");

    return new ResponseEntity < > (respWebSeries, HttpStatus.CREATED);

  }

  @GetMapping("/dto-with-jpql")

  public ResponseEntity < ? > getAllWebSeriesDTO() {

    Map < String, Object > respWebSeries = new LinkedHashMap < String, Object > ();

    List < WebSeriesDTO > webSeriesDTOList = webSeriesService.getAllWebSeriesDTO();

    if (!webSeriesDTOList.isEmpty()) {

      respWebSeries.put("status", 1);

      respWebSeries.put("data", webSeriesDTOList);

      return new ResponseEntity < > (respWebSeries, HttpStatus.OK);

    } else {

      respWebSeries.clear();

      respWebSeries.put("status", 0);

      respWebSeries.put("message", "Data is not found");

      return new ResponseEntity < > (respWebSeries, HttpStatus.NOT_FOUND);

    }

  }

  @GetMapping("/dto-with-projection")

  public ResponseEntity < ? > getAllWebSeriesDTOI() {

    Map < String, Object > respWebSeries = new LinkedHashMap < String, Object > ();

    List < WebSeriesDTOI > webSeriesDTOList = webSeriesService.getAllWebSeriesDTOI();

    if (!webSeriesDTOList.isEmpty()) {

      respWebSeries.put("status", 1);

      respWebSeries.put("data", webSeriesDTOList);

      return new ResponseEntity < > (respWebSeries, HttpStatus.OK);

    } else {

      respWebSeries.clear();

      respWebSeries.put("status", 0);

      respWebSeries.put("message", "Data is not found");

      return new ResponseEntity < > (respWebSeries, HttpStatus.NOT_FOUND);

    }

  }

  @GetMapping("/dto-with-native-class-based")

  public ResponseEntity < ? > getAllClassBased() {

    Map < String, Object > respWebSeries = new LinkedHashMap < String, Object > ();

    List < WebSeriesDTO > webSeriesDTOList = webSeriesService.getAllWebSeriesDTO();

    if (!webSeriesDTOList.isEmpty()) {

      respWebSeries.put("status", 1);

      respWebSeries.put("data", webSeriesDTOList);

      return new ResponseEntity < > (respWebSeries, HttpStatus.OK);

    } else {

      respWebSeries.clear();

      respWebSeries.put("status", 0);

      respWebSeries.put("message", "Data is not found");

      return new ResponseEntity < > (respWebSeries, HttpStatus.NOT_FOUND);

    }

  }

  @GetMapping("/dto-with-native-interface-based")

  public ResponseEntity < ? > getAllInterfaceBased() {

    Map < String, Object > respWebSeries = new LinkedHashMap < String, Object > ();

    List < WebSeriesDTOI > webSeriesDTOList = webSeriesService.getAllWithInterfaceBased();

    if (!webSeriesDTOList.isEmpty()) {

      respWebSeries.put("status", 1);

      respWebSeries.put("data", webSeriesDTOList);

      return new ResponseEntity < > (respWebSeries, HttpStatus.OK);

    } else {

      respWebSeries.clear();

      respWebSeries.put("status", 0);

      respWebSeries.put("message", "Data is not found");

      return new ResponseEntity < > (respWebSeries, HttpStatus.NOT_FOUND);

    }

  }

}

11. Run the Spring Boot Application

Right-click this Spring Boot application on the DemoApplication.java, then click Run As and select Java Application.

H2 Database Console

Browse this URL “http://localhost:8080/h2-console” on the browser for checking H2 database console.  

JSON Array

We are making a sample JSON Array to test the API http://localhost:8080/api/webseries/save-all.

[
  {
    "name": "Pill",
    "season": "S1",
    "released": "12 July 2024",
    "ott": "JioCinema"
  },
  {
    "name": "Panchayat",
    "season": "S3",
    "released": "28 May 2024",
    "ott": "Amazon Prime"
  },
  {
    "name": "Gullak",
    "season": "S4",
    "released": "11 June 2024",
    "ott": "Sony LIV"
  }
]

Test the APIs on the Postman Tool

POST: http://localhost:8080/api/webseries/save-all

Return DTO from JPA Repository

GET: http://localhost:8080/api/webseries/dto-with-jpql

Return DTO from JPA Repository

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select ws1_0.name,ws1_0.ott from web_series ws1_0

GET: http://localhost:8080/api/webseries/dto-with-projection

Return DTO from JPA Repository

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select ws1_0.name,ws1_0.ott from web_series ws1_0

GET:http://localhost:8080/api/webseries/dto-with-native-class-based

Return DTO from JPA Repository

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select ws1_0.name,ws1_0.ott from web_series ws1_0

GET: http://localhost:8080/api/webseries/dto-with-native-interface-based

Return DTO from JPA Repository

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: SELECT w.name AS name, w.ott AS ott FROM web_series w

Conclusion

Return DTO from JPA Repository through JPQL, Projection-based and Native SQL queries using Spring Boot REST API.

Leave a Comment