Map Custom Query Results to DTO in Spring Data JPA

By · Spring JPA · Tags: ,

Introduction

In real-world applications,

you may encounter scenarios where returning entity objects directly from a Spring Data JPA repository is not ideal. Instead, you might need to use custom or native queries and map the results into DTOs (Data Transfer Objects). This approach improves performance, avoids serialization issues, and ensures better control over the data exposed to clients.

This tutorial demonstrates how to map custom query results into DTOs using Spring Data JPA.

Database Setup

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;

Also dump some data to test the application right away once the coding part 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

Create a Spring Boot project named spring-data-jpa-native-query-to-dto.

You can refer to the 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>

  <properties>
      <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
      <maven.compiler.release>22</maven.compiler.release>
    </properties>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.5.4</version>
	</parent>

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

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

		<dependency>
			<groupId>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>			
		</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.

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

@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.

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

import java.util.Date;

public class EventDto {

	private int id;

	private String title;





	private String url;

	private String clasz;





	private Date start;

	private Date end;

	public EventDto() {
	}

	public EventDto(int id, String title, String url, String clasz, Date start, Date 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.

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

public interface SpringDataJpaNativeQueryRepository extends JpaRepository<Event, Integer> {

	@Query(value = "SELECT e.id, e.title, e.url, e.clasz, e.start_date as start, e.end_date 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, e.startDate, e.endDate) 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, e.startDate, e.endDate) 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, e.startDate, e.endDate) FROM Event e WHERE e.clasz = ?1 AND DATE(e.endDate) = ?2")
	public List<EventDto> getEventsByClaszAndEnd(String clasz, Date endDate);

}

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.

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

@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());
			dto.setEnd(e.getEndDate());

			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, LocalDate end) {
		return repository.getEventsByClaszAndEnd(clasz, java.sql.Date.valueOf(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.

Spring Boot Main Application 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.

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

@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", LocalDate.of(2020, 04, 10)).stream()
				.forEach(e -> System.out.println(e));
		System.out.println("=======================================");
	}

}

Output Example

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

id=1, title=Example, url=http://www.example.com, clasz=event-success, start=2021-03-03 15:27:51.0, end=2020-04-10 20:01:02.0
id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=2021-03-11 19:00:00.0, end=2020-03-12 19:42:45.0
id=3, title=Roy Tutorial, url=https://roytuts.com, clasz=event-info, start=2021-03-12 20:03:05.0, end=2020-05-13 08:45:53.0
---------------------------------------
EventDto [id=1, title=Example, url=http://www.example.com, clasz=event-success, start=2021-03-03 15:27:51.0, end=2020-04-10 20:01:02.0]
EventDto [id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=2021-03-11 19:00:00.0, end=2020-03-12 19:42:45.0]
EventDto [id=3, title=Roy Tutorial, url=https://roytuts.com, clasz=event-info, start=2021-03-12 20:03:05.0, end=2020-05-13 08:45:53.0]
---------------------------------------
EventDto [id=1, title=Example, url=http://www.example.com, clasz=event-success, start=2021-03-03 15:27:51.0, end=2020-04-10 20:01:02.0]
EventDto [id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=2021-03-11 19:00:00.0, end=2020-03-12 19:42:45.0]
EventDto [id=3, title=Roy Tutorial, url=https://roytuts.com, clasz=event-info, start=2021-03-12 20:03:05.0, end=2020-05-13 08:45:53.0]
---------------------------------------
EventDto [id=2, title=Jee Tutorials, url=https://roytuts.com, clasz=event-important, start=2021-03-11 19:00:00.0, end=2020-03-12 19:42:45.0]
---------------------------------------
EventDto [id=1, title=Example, url=http://www.example.com, clasz=event-success, start=2021-03-03 15:27:51.0, end=2020-04-10 20:01:02.0]

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

Conclusion

Mapping custom query results into DTOs in Spring Data JPA is a powerful technique to optimize data transfer and avoid exposing entire entity graphs. This approach is especially useful when working with native queries or when performance and security are priorities.

Share

Related posts

2 comments

  1. test

    great

    Reply
  2. Pratima

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

    Reply

Leave a comment

↑