Posted in Spring Boot, Spring JPA

How to map Custom Query Results into DTO in Spring Data JPA

Introduction

In this tutorial I will show you how to map custom query results into DTO in Spring Data JPA. Situations may occur while you are unable to return the entity object(s) as results from your Spring Data JPA Repository and instead you need to write custom query or native query for your business requirements. So in such situations you need to convert result into DTO (data Trasfer Object). I will use @Query annotation to create custom queries in Spring Data JPA.

It is also advisable to use DTO instead of entity class or object while you send the result to clients or end users or from your API endpoints to avoid any kind issues, such as, stack overflow error (this error occurs while you are returning large dataset or you have many hierarchical or nested entities). I will use both gradle and maven build tools to build the example.

In this example I will create a native query that will map to DTO class or object, I will also create an inner interface to map the result from native query and return the result.

Related Post:

Prerequisites

Java 1.8+, Gradle 6.1.1 – 6.7.1, Maven 3.6.3/3.8.5, Spring Boot 2.2.4 – 2.7.0, MySQL 8.0.17 – 8.0.26

MySQL Table

I will create the following table event under roytuts database in MySQL server.

CREATE TABLE IF NOT EXISTS `event` (
  `id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `clasz` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `end_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I will also dump few rows to test the application right away once coding is finished.

INSERT INTO `event` (`id`, `title`, `url`, `clasz`, `start_date`, `end_date`) VALUES
	(1, 'Example', 'http://www.example.com', 'event-success', '2021-03-03 15:27:51', '2020-04-10 20:01:02'),
	(2, 'Jee Tutorials', 'https://roytuts.com', 'event-important', '2021-03-11 19:00:00', '2020-03-12 19:42:45'),
	(3, 'Roy Tutorial', 'https://roytuts.com', 'event-info', '2021-03-12 20:03:05', '2020-05-13 08:45:53');

Project Setup

You need to create a Spring Boot project in your favorite IDE or tool. The name of the project is spring-data-jpa-native-query-to-dto.

I will create standalone application so I am not going to use starter-web dependency. Alternatively if you use starter-web dependency then you can exclude embedded server Tomcat from the starter-web dependency.

If you creating a gradle based project in Eclipse then you can use the below build.gradle script:

buildscript {
	ext {
		springBootVersion = '2.2.4.RELEASE' to '2.7.0'
	}
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

plugins {
    id 'java-library'
    id 'org.springframework.boot' version '2.2.4.RELEASE' to 2.4.2
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    mavenCentral()
}

dependencies {
    implementation("org.springframework.boot:spring-boot-starter:${springBootVersion}")
    implementation("org.springframework.boot:spring-boot-starter-data-jpa:${springBootVersion}")
    implementation('mysql:mysql-connector-java:8.0.17/26')
	//required only if jdk 9 or higher version is used
    runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}

If you are creating maven based project in Eclipse then you can use below pom.xml file:

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>spring-data-jpa-native-query-to-dto</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.2 to 2.7.0</version>
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>12</maven.compiler.source>
		<maven.compiler.target>12</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<scope>runtime</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

Entity Class

You need an entity class to map your table into the entity object through JPA API. I am using auto-generated value for primary key id column.

Notice that I have put few column names using @Column annotation if there is a difference between table column name and Java attribute name.

You may like to read why do you need @Temporal annotation on field name.

@Entity
@Table(name = "event")
public class Event {

	@Id
	@Column
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column
	private String title;

	@Column
	private String url;

	private String clasz;

	@Column(name = "start_date")
	@Temporal(TemporalType.TIMESTAMP)
	private Date startDate;

	@Column(name = "end_date")
	@Temporal(TemporalType.TIMESTAMP)
	private Date endDate;

	//getters and setters

}

DTO Class

As I had already mentioned that it is not advisable to use entity class or object in the client results so I am going to create DTO class that will help me to map the native query result or custom query result into DTO object as well as it will help us to return the DTO object as a response to the client.

public class EventDto {

	private int id;

	private String title;

	private String url;

	private String clasz;

	private long start;

	private long end;

	public EventDto() {
	}

	public EventDto(int id, String title, String url, String clasz, long start, long end) {
		this.id = id;
		this.title = title;
		this.url = url;
		this.clasz = clasz;
		this.start = start;
		this.end = end;
	}

	//getters and setters

	@Override
	public String toString() {
		return "EventDto [id=" + id + ", title=" + title + ", url=" + url + ", clasz=" + clasz + ", start=" + start
				+ ", end=" + end + "]";
	}

}

JPA Repository

Spring Data JPA provides built-in methods for your basic CRUD operations but as I am going to create custom query or native query so I will build query in my own way. To write a repository you need to create an interface that will extend JpaRepository.

public interface SpringDataJpaNativeQueryRepository extends JpaRepository<Event, Integer> {

	@Query(value = "SELECT e.id, e.title, e.url, e.clasz, UNIX_TIMESTAMP(start_date)*1000 as start, UNIX_TIMESTAMP(end_date)*1000 as end FROM event e", nativeQuery = true)
	public List<Event> getEvents();

	public interface Event {

		String getId();

		String getTitle();

		String getUrl();

		String getClasz();

		String getStart();

		String getEnd();

	}

	@Query("SELECT new com.roytuts.spring.data.jpa.natve.query.dto.EventDto(e.id, e.title, e.url, e.clasz, UNIX_TIMESTAMP(start_date)*1000 as start, UNIX_TIMESTAMP(end_date)*1000 as end) FROM Event e")
	public List<EventDto> getEventList();

	@Query("SELECT new com.roytuts.spring.data.jpa.natve.query.dto.EventDto(e.id, e.title, e.url, e.clasz, UNIX_TIMESTAMP(start_date)*1000 as start, UNIX_TIMESTAMP(end_date)*1000 as end) FROM Event e WHERE e.id = ?1")
	public EventDto getEventById(int id);

	@Query("SELECT new com.roytuts.spring.data.jpa.natve.query.dto.EventDto(e.id, e.title, e.url, e.clasz, UNIX_TIMESTAMP(start_date)*1000 as start, UNIX_TIMESTAMP(end_date)*1000 as end) FROM Event e WHERE e.clasz = ?1 AND UNIX_TIMESTAMP(end_date)*1000 = ?2")
	public List<EventDto> getEventsByClaszAndEnd(String clasz, long end);

}

In the above repository interface, the first query method getEvents() returns the result of interface type Event that is defined as an inner interface with getters to map the result fields. Notice the query I have built here is native and it was indicated using nativeQuery = true.

Next the query method getEventList() returns the result and maps to the DTO class EventDto. Notice how did I create the new object with fully classified package name of the class EventDto. Make sure you have the appropriate constructor defined in the DTO class for mapping/returning the fields as a result.

Then I have a query method getEventById(int id) which takes a single parameter and returns a single result.

Finally I have shown how to work with multiple input parameters, therefore I have created another query method getEventsByClaszAndEnd(String clasz, long end).

Notice how do I map the parameters using notation ?1, ?2, etc. These are positional parameters at first place, at second place, and so on.

Service Class

The service class is sole responsible for providing the services, where I write the business logic for the application.

@Service
public class SpringDataJpaNativeQueryService {

	@Autowired
	private SpringDataJpaNativeQueryRepository repository;

	public List<EventDto> getEventList() {
		List<EventDto> eventList = repository.findAll().stream().map(e -> {
			EventDto dto = new EventDto();

			dto.setId(e.getId());
			dto.setTitle(e.getTitle());
			dto.setUrl(e.getUrl());
			dto.setClasz(e.getClasz());
			dto.setStart(e.getStartDate().getTime());
			dto.setEnd(e.getEndDate().getTime());

			return dto;
		}).collect(Collectors.toList());

		return eventList;

	}

	public List<com.roytuts.spring.data.jpa.natve.query.repository.SpringDataJpaNativeQueryRepository.Event> getEvents() {
		return repository.getEvents();
	}

	public List<EventDto> getEventList2() {
		return repository.getEventList();
	}

	public EventDto getEventById(int id) {
		return repository.getEventById(id);
	}

	public List<EventDto> getEventListByClaszAndEnd(String clasz, long end) {
		return repository.getEventsByClaszAndEnd(clasz, end);
	}

}

In the above class, the first method getEventList() actually first retrieves all events from the table. Notice I use method findAll() from the Spring Data JPA API, which returns of type Event class and later I have converted the list into EventDto using Java’s stream API.

The second method returns the data of type Event interface that was declared inside the repository itself.

The third, fourth and fifth methods return the data type of EventDto directly from the repository.

Main Class

I will now create the main method in a class. I want to run our Spring Boot application as a standalone application. Therefore I will implement CommandLineRunner interface.

I have also let the application know where the repository interface is located.

@SpringBootApplication
@EnableJpaRepositories(basePackages = "com.roytuts.spring.data.jpa.natve.query.repository")
public class SpringDataJpaNativeQueryApp implements CommandLineRunner {

	@Autowired
	private SpringDataJpaNativeQueryService service;

	public static void main(String[] args) {
		SpringApplication.run(SpringDataJpaNativeQueryApp.class, args).close();
	}

	@Override
	public void run(String... args) throws Exception {
		System.out.println("=======================================");
		service.getEvents().stream()
				.forEach(e -> System.out.println("id=" + e.getId() + ", title=" + e.getTitle() + ", url=" + e.getUrl()
						+ ", clasz=" + e.getClasz() + ", start=" + e.getStart() + ", end=" + e.getEnd()));
		System.out.println("---------------------------------------");
		service.getEventList().stream().forEach(e -> System.out.println(e));
		System.out.println("---------------------------------------");
		service.getEventList2().stream().forEach(e -> System.out.println(e));
		System.out.println("---------------------------------------");
		System.out.println(service.getEventById(2));
		System.out.println("---------------------------------------");
		service.getEventListByClaszAndEnd("event-success", 1523370662000l).stream().forEach(e -> System.out.println(e));
		System.out.println("=======================================");
	}

}

Testing the Application

Now executing the above main class will give you the following output in the Eclipse console:

=======================================
id=1, title=Example, url=http://www.example.com, clasz=event-success, start=1614765471000, end=1586529062000
id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=1615469400000, end=1584022365000
id=3, title=Roy Tutorial, url=https://roytuts.com, clasz=event-info, start=1615559585000, end=1589339753000
---------------------------------------
EventDto [id=1, title=Example, url=http://www.example.com, clasz=event-success, start=1614765471000, end=1586529062000]
EventDto [id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=1615469400000, end=1584022365000]
EventDto [id=3, title=Roy Tutorial, url=https://roytuts.com, clasz=event-info, start=1615559585000, end=1589339753000]
---------------------------------------
EventDto [id=1, title=Example, url=http://www.example.com, clasz=event-success, start=1614765471000, end=1586529062000]
EventDto [id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=1615469400000, end=1584022365000]
EventDto [id=3, title=Roy Tutorial, url=https://roytuts.com, clasz=event-info, start=1615559585000, end=1589339753000]
---------------------------------------
EventDto [id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=1615469400000, end=1584022365000]
---------------------------------------
=======================================

If you want to make the above application, then it is very easy to do so. Just you need to include starter-web dependency instead of starter dependency. You just remove the implementation of CommandLineRunner interface. You can create REST Controller class to build rest API and call the service methods from REST API. You can also use JSP pages or ThymeLeaf template for your front-end or UI.

Source Code

Download

2 thoughts on “How to map Custom Query Results into DTO in Spring Data JPA”

Leave a Reply

Your email address will not be published. Required fields are marked *