How to Return DTOs from Native Queries in Spring Data JPA

         In this post, we will discuss how to return DTOs from a JPA repository method using the results of a native query without relying on entity mapping.

     JPA provides many built-in repository methods, such as findAll(), findById(), and others, for performing database operations. These methods map the query results to the corresponding entity classes.

However, in some scenarios, we need to use native SQL queries to join multiple tables and retrieve only the required data. In such cases, there may not be a suitable entity that matches the query result, making direct entity mapping impractical. Instead, we can map the native query result directly to a DTO.

This can be achieved using an interface-based DTO projection. With this approach, the JPA repository maps the native query result directly to a DTO projection, eliminating the need to first map the result to an entity and then manually convert the entity into a DTO.

Let's understand this with the following example.

JPA repository with a native query:

@Repository
public interface PropertyTaxRepository extends JpaRepository<PropertyTax, Long> {
	
     @Query(value=" SELECT z.zone as zoneName, pt.status as propertyType, SUM(pt.tax) as amountCollected "
		+ " FROM property_tax pt "
		+ " INNER JOIN zone z ON z.id=pt.zonal_classification "
		+ " group by z.zone, pt.status;", nativeQuery = true)
     public List<ReportDto> getTaxCollectedReport();
}

Create a ReportDto interface with getter methods corresponding to the fields returned by the native query. In this example, the native query selects three fields: zoneName, propertyType, and amountCollected.

The interface-based DTO is shown below:

public interface ReportDto {
      String getZoneName();
      String getPropertyType();
      BigDecimal getAmountCollected();
}

     If you have any questions or clarifications, please add comment in the comment section or send an email to anilnivargi49@gmail.com.

Thank you for visiting the blog.