How to map sql native query result into DTO in spring jpa repository?

Question

Hi what I am trying to achieve is to get SQL native query result map into my DTO in java spring jpa repository, how do I do this properly? I try several code, but it does not work, here is what I tried:

First try :

@Repository
public interface StockRepository extends RevisionRepository<Stock, Long, Integer>, JpaRepository<Stock, Long> {
    
   @Query(value = "SELECT stock_akhir.product_id AS productId, stock_akhir.product_code AS productCode, SUM(stock_akhir.qty) as stockAkhir "
        + "FROM book_stock stock_akhir "
        + "where warehouse_code = (:warehouseCode) "
        + "AND product_code IN (:productCodes) "
        + "GROUP BY product_id, product_code, warehouse_id, warehouse_code", nativeQuery = true)
   List<StockAkhirDto> findStockAkhirPerProductIn(@Param("warehouseCode") String warehouseCode, @Param("productCodes") Set<String> productCode);
}

once I execute the function, I got this error:

No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.b2bwarehouse.Dto.RequestDto.StockDto.StockAkhirDto]

Second try :

@Repository
public interface StockRepository extends RevisionRepository<Stock, Long, Integer>, JpaRepository<Stock, Long> {
    
   @Query(value = "SELECT new com.b2bwarehouse.Dto.RequestDto.StockDto.StockAkhirDto(stock_akhir.product_id AS productId, stock_akhir.product_code AS productCode, SUM(stock_akhir.qty) as stockAkhir) "
      + "FROM book_stock stock_akhir "
      + "where warehouse_code = (:warehouseCode) "
      + "AND product_code IN (:productCodes) "
      + "GROUP BY product_id, product_code, warehouse_id, warehouse_code", nativeQuery = true)
   List<StockAkhirDto> findStockAkhirPerProductIn(@Param("warehouseCode") String warehouseCode, @Param("productCodes") Set<String> productCode);
}

in second here is the error:

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

below is my DTO:

@Data
@AllArgsConstructor
@NoArgsConstructor
public class StockAkhirDto {
   private Long productId;
   private String productCode;
   private Integer stockAkhir;
}

How should I correct my code? So, can I get the result into my DTO?

Answer 1

You can define the following named native query with appropriate sql result set mapping:

import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.ConstructorResult;
import javax.persistence.ColumnResult;

@Entity
@NamedNativeQuery(
    name = "find_stock_akhir_dto",
    query =
        "SELECT " + 
        "  stock_akhir.product_id AS productId, " + 
        "  stock_akhir.product_code AS productCode, " + 
        "  SUM(stock_akhir.qty) as stockAkhir " + 
        "FROM book_stock stock_akhir " + 
        "where warehouse_code = :warehouseCode " + 
        "  AND product_code IN :productCodes " + 
        "GROUP BY product_id, product_code, warehouse_id, warehouse_code",
    resultSetMapping = "stock_akhir_dto"
)
@SqlResultSetMapping(
    name = "stock_akhir_dto",
    classes = @ConstructorResult(
        targetClass = StockAkhirDto.class,
        columns = {
            @ColumnResult(name = "productId", type = Long.class),
            @ColumnResult(name = "productCode", type = String.class),
            @ColumnResult(name = "stockAkhir", type = Integer.class)
        }
    )
)
public class SomeEntity
{
}

and then use it:

@Repository
public interface StockRepository extends RevisionRepository<Stock, Long, Integer>, JpaRepository<Stock, Long> {

   @Query(name = "find_stock_akhir_dto", nativeQuery = true)
   List<StockAkhirDto> findStockAkhirPerProductIn(
      @Param("warehouseCode") String warehouseCode,
      @Param("productCodes") Set<String> productCode
   );
}

Answer 2

i find a way which is not usual, but i find data type called "Tuple" when i try to use QueryDsl to solved this problem, but i won't recommend QueryDsl if you are just getting started just like me. Lets focus on how i do it with "Tuple"

i changed my return type to Tuple, here is how my repository looked like :

@Repository
public interface StockRepository extends RevisionRepository<Stock, Long, Integer>, JpaRepository<Stock, Long> {
    
   @Query(value = "SELECT stock_akhir.product_id AS productId, stock_akhir.product_code AS productCode, SUM(stock_akhir.qty) as stockAkhir "
        + "FROM book_stock stock_akhir "
        + "where warehouse_code = (:warehouseCode) "
        + "AND product_code IN (:productCodes) "
        + "GROUP BY product_id, product_code, warehouse_id, warehouse_code", nativeQuery = true)
   List<Tuple> findStockAkhirPerProductIn(@Param("warehouseCode") String warehouseCode, @Param("productCodes") Set<String> productCode);
}

and then here in my service class, since it's returned as Tuple, i have to map the column one by one manually, here is my service function looked like :

public List<StockTotalResponseDto> findStocktotal() {
    List<Tuple> stockTotalTuples = stockRepository.findStocktotal();
    
    List<StockTotalResponseDto> stockTotalDto = stockTotalTuples.stream()
            .map(t -> new StockTotalResponseDto(
                    t.get(0, String.class), 
                    t.get(1, String.class), 
                    t.get(2, BigInteger.class)
                    ))
            .collect(Collectors.toList());
    
    return stockTotalDto;
}

the column field start with 0, in this way i can keep my query neat at Repository level. But i will accept SternK answer as the accepted answer because that way worked too, i will keep my answer here if someone need something like this

Answer 3

Create standard native @Query

@Query(value = "select id, age, name FROM Person WHERE age=?1", nativeQuery=true)
List<PersonView> getPersonsByAge(int age);

and an interface

public interface PersonView {
    Long getId();
    Integer getAge();
    String getName();
}

columns are matched by order (not by names). In this way, you have a native query, no entities and not too much boiler plate code (aka many annotations).

However, resulting views (Jdk proxy etc.) are very slow in access, i had some code doing some grouping over a stream, and it's 10x !! slower than with standard DTO/Pojos ! so at the end, I don't use nativeQuery anymore, but:

SELECT new com.my_project.myDTO(p.id, p.age, p.name) .....

Answer 4

The second variant is pretty close. You just have to remove the aliases for the constructor expression:

new com.b2bwarehouse.Dto.RequestDto.StockDto.StockAkhirDto(
    stock_akhir.product_id, 
    stock_akhir.product_code, 
    SUM(stock_akhir.qty)
)

should work.

Answer 5

Another valid option based on Sternk answer woul be as follows

You can define the following named native query with appropriate sql result set mapping:

resources/META-INF/orm.xml

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"
                 version="2.0">
    <named-native-query name="find_stock_akhir_dto" result-class="com.fullyqualified.name.SomeEntity"
                        result-set-mapping="stock_akhir_dto">
        <query><![CDATA[
            SELECT 
              stock_akhir.product_id AS productId, 
              stock_akhir.product_code AS productCode, 
              SUM(stock_akhir.qty) as stockAkhir 
            FROM book_stock stock_akhir 
            where warehouse_code = :warehouseCode 
              AND product_code IN :productCodes  
            GROUP BY product_id, product_code, warehouse_id, warehouse_code]]></query>
    </named-native-query>
</entity-mappings>

import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.ConstructorResult;
import javax.persistence.ColumnResult;

@Entity
@SqlResultSetMapping(
    name = "stock_akhir_dto",
    classes = @ConstructorResult(
        targetClass = StockAkhirDto.class,
        columns = {
            @ColumnResult(name = "productId", type = Long.class),
            @ColumnResult(name = "productCode", type = String.class),
            @ColumnResult(name = "stockAkhir", type = Integer.class)
        }
    )
)
public class SomeEntity
{
}

and then use it:

@Repository
public interface StockRepository extends RevisionRepository<Stock, Long, Integer>, JpaRepository<Stock, Long> {

   @Query(name = "find_stock_akhir_dto", nativeQuery = true)
   List<StockAkhirDto> findStockAkhirPerProductIn(
      @Param("warehouseCode") String warehouseCode,
      @Param("productCodes") Set<String> productCode
   );
}

Answer 6

Map your result of a native SQL query into an interface-based DTO in a Spring JPA repository is very simplified by spring boot framework as below steps you can follow

1. Define the DTO Interface:

Create an interface that defines the getter methods for the properties you want to map from the native SQL query result. The interface methods should match the column aliases used in your SQL query.

public interface MyDTO {
    Long getId();
    String getName();
    // Other getter methods for other properties
}

2. Define the Native SQL Query:

In your Spring JPA repository interface, use the @Query annotation with a native SQL query that returns the columns needed for the DTO interface.

@Repository
public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {

    @Query(value = "SELECT id as id, name as name FROM my_table WHERE ...", nativeQuery = true)
    List<MyDTO> findDataUsingNativeQuery();
}

You can now use the findDataUsingNativeQuery() method from your service layer to retrieve the data mapped to the DTO interface.