如何将sql本机查询结果映射到spring jpa存储库中的DTO? [英] How to map sql native query result into DTO in spring jpa repository?
问题描述
我想要实现的是将 SQL 本机查询结果映射到 java spring jpa 存储库中的 DTO 中,我该如何正确执行此操作?我尝试了几个代码,但它不起作用,这是我尝试的:
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:
第一次尝试:
@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:
找不到能够从类型转换的转换器[org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap]输入 [com.b2bwarehouse.Dto.RequestDto.StockDto.StockAkhirDto]
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]
第二次尝试:
@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);
}
第二个是错误:
无法提取结果集;SQL [不适用];嵌套异常是org.hibernate.exception.SQLGrammarException: 无法提取结果集
could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
以下是我的 DTO:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class StockAkhirDto {
private Long productId;
private String productCode;
private Integer stockAkhir;
}
我应该如何更正我的代码?那么,我可以将结果输入到我的 DTO 中吗?
How should I correct my code? So, can I get the result into my DTO?
推荐答案
您可以使用适当的 sql 结果集映射定义以下命名本机查询:
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
{
}
然后使用它:
@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
);
}
这篇关于如何将sql本机查询结果映射到spring jpa存储库中的DTO?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!