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

In this tutorial we will see how to map custom query results into DTO in Spring Data JPA. Situations occur while you are unable to return the entity object(s) as results from your Spring Data JPA Repository and instead you are writing custom query or native query for your business requirements. So in such situations you need to convert result into DTO (data Trasfer Object). We 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 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). We will use both gradle and maven build tools to build the example.

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

Prerequisites

Eclipse 2019-12, Java at least 8, Gradle 6.1.1, Maven 3.6.3, Spring Boot 2.2.4, MySQL 8.0.17

Create MySQL Table

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

CREATE TABLE IF NOT EXISTS `event` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `clasz` varchar(255) CHARACTER SET utf8mb4 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;

We will also dump few rows to test our application once coding is finished.

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

Create Project

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

We will create standalone application so we are 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'
	}
    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'
}

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')
	//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:

<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.2.4.RELEASE</version>
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jpa</artifactId>
		</dependency>
		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.17</version>
		</dependency>
		
		<!--required only if jdk 9 or higher version is used-->
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.4.0-b180830.0359</version>
		</dependency>
	</dependencies>

    <build>
        <plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>at least 8</source>
					<target>at least 8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Entity Class

We need entity class to map our table into entity object through JPA API. We are using auto-generated value for primary key id column.

Notice that we 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 we need @Temporal annotation on field name.

package com.roytuts.spring.data.jpa.natve.query.model;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@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 we are going to create DTO class that will help us 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.

package com.roytuts.spring.data.jpa.natve.query.dto;

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 we are going to create custom query or native query so we will build query in our own way. To write a repository we need to create an interface that will extend JpaRepository.

package com.roytuts.spring.data.jpa.natve.query.repository;

import java.util.List;

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

import com.roytuts.spring.data.jpa.natve.query.dto.EventDto;
import com.roytuts.spring.data.jpa.natve.query.model.Event;

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 we 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 we 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 we have a query method getEventById(int id) which takes a single parameter and returns a single result.

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

Notice how do we 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 we write our business logic.

package com.roytuts.spring.data.jpa.natve.query.service;

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

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

import com.roytuts.spring.data.jpa.natve.query.dto.EventDto;
import com.roytuts.spring.data.jpa.natve.query.repository.SpringDataJpaNativeQueryRepository;
import com.roytuts.spring.data.jpa.natve.query.repository.SpringDataJpaNativeQueryRepository.Event;

@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<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 we use method findAll() from the Spring Data JPA API, which returns of type Event class and later we convert 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.

Create Main Class

We will now create our main class. We want to run our Spring Boot application as a standalone application. Therefore we will implement CommandLineRunner interface.

We have also let our application know where our repository interface.

package com.roytuts.spring.data.jpa.natve.query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import com.roytuts.spring.data.jpa.natve.query.service.SpringDataJpaNativeQueryService;

@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);
	}

	@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=1583229471000, end=1523370662000
id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=1583933400000, end=1520863965000
id=3, title=Roy Tutorial, url=https://roytuts.com, clasz=event-info, start=1584023585000, end=1526181353000
---------------------------------------
EventDto [id=1, title=Example, url=http://www.example.com, clasz=event-success, start=1583229471000, end=1523370662000]
EventDto [id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=1583933400000, end=1520863965000]
EventDto [id=3, title=Roy Tutorial, url=https://roytuts.com, clasz=event-info, start=1584023585000, end=1526181353000]
---------------------------------------
EventDto [id=1, title=Example, url=http://www.example.com, clasz=event-success, start=1583229471000, end=1523370662000]
EventDto [id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=1583933400000, end=1520863965000]
EventDto [id=3, title=Roy Tutorial, url=https://roytuts.com, clasz=event-info, start=1584023585000, end=1526181353000]
---------------------------------------
EventDto [id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=1583933400000, end=1520863965000]
---------------------------------------
EventDto [id=1, title=Example, url=http://www.example.com, clasz=event-success, start=1583229471000, end=1523370662000]
=======================================

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.

Download

Thanks for reading.

One Thought to “How to map Custom Query Results into DTO in Spring Data JPA”

  1. Pratima

    Thank you for posting this.It was really helped me a lot.keep posting

Leave a Comment