How to Return DTO's from Native Queries in Spring Data JPA

          In this post, we will discuss how to return DTO's of non entity basis native query result from JPA repository method. JPA provides many implemented methods for database operations like findAll, findById and so on. If we use in-built JPA repository methods then corresponding entity to map with the query result. 
           

        In some scenarios, we need to use native queries to join multiple tables and fetch the required details. In such cases, entity mapping may not be available, so we need to discuss how to map the native query result to an entity or a DTO.

This can be achieved using an interface-based DTO approach. In this approach, the JPA repository query maps the result directly to a DTO instead of an entity, eliminating the need to manually convert the entity object to a DTO object.

Let's discuss this with code as below,

JPA repository with 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();
}

Created ReportDto interface with getter methods of the native query fields, there are three fields used in the select native query zoneName, propertyType and amountCollected. 

Interface based DTO class as follows,

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.