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

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
   );
}

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

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

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.

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
   );
}