如何将sql原生查询结果映射到spring jpa存储库中的DTO中？

Question

嗨，我试图实现的是将SQL原生查询结果映射到java spring jpa存储库中的DTO中，如何正确地做到这一点？我尝试了几个代码，但都不起作用，下面是我所尝试的：

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

java prettyprint-override">@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);
}

第二个错误是：

无法提取ResultSet；SQL[N/A]；嵌套异常是org.hibernate.exception.SqlGrammareXception：无法提取ResultSet

下面是我的DTO：

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

Answer 1

可以使用适当的sql结果集映射定义以下命名本机查询：

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
{
}

然后使用它：

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