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 of the scenario's need to use native query to join with multiple tables to get the required details. In such case, we should not have the entity mapping so need to discuss how to map the native query result to the entity or DTO. We can achieve this using interface based DTO. The interface based DTO approach, JPA repository query maps with DTO instead of entity so need to convert again entity object to 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.