java - Spring CrudRepository 按结果返回分组作为 Map

Question

我使用 CrudRepository 执行 native 查询，其中使用求和和分组依据。我希望查询将结果作为(哈希)映射返回。我的查询如下所示:

 @Query(value = "SELECT l.currency, sum(l.price) as total_revenue from line l " + "where ol.id0 = :id0 and l.date_time between :dateStart and :dateEnd and " +
        "(cast(:id1 as text) is null or cast(l.id1 as text) = cast(:id1 as text)) and " +
        "(cast(:id2 as text) is null or cast(l.id2 as text) = cast(:id2 as text))" +
        "group by l.currency" ,
        nativeQuery = true)
List<Object[]> findTotalRevenueByCurrency(@Param("id0") UUID id0,
                                                     @Param("dateStart") Instant dateTimeStart,
                                                     @Param("dateEnd") Instant dateTimeEnd,
                                                     @Param("id1") UUID id1,
                                                     @Param("id2") UUID id2);

这会返回一个对象列表，我手动将其转换为 Map<String, BigDecimal>使用以下代码:

var result = orderLineRepository.findAveragePriceByCurrency(id0, orderDate.minus(2, DAYS), orderDate.plus(2, DAYS), id1, id2);

Map<String, BigDecimal> revenueByCurrency = result.stream()
    .map(arr -> Map.of((String) arr[0], (BigDecimal) arr[1]))
    .flatMap(m -> m.entrySet().stream())
    .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));```

它非常脆，需要类型转换。有没有办法让这个查询返回 Map<String, BigDecimal>盒子外面？

Answer 1

您可以创建一个自定义类来匹配此查询的结果，然后将结果列表映射到 Map。

public class CustomQueryResult {
 //class properties
 public CustomQueryResult(String currency, BigDecimal price) {
    //assign 
 }
}
@Query(value = "SELECT new CustomQueryResult(l.currency, sum(l.price) as total_revenue) from line l " + "where ol.id0 = :id0 and l.date_time between :dateStart and :dateEnd and " +
        "(cast(:id1 as text) is null or cast(l.id1 as text) = cast(:id1 as text)) and " +
        "(cast(:id2 as text) is null or cast(l.id2 as text) = cast(:id2 as text))" +
        "group by l.currency" ,
        nativeQuery = true)
List<CustomQueryResult> findTotalRevenueByCurrency(@Param("id0") UUID id0,
                                                     @Param("dateStart") Instant dateTimeStart,
                                                     @Param("dateEnd") Instant dateTimeEnd,
                                                     @Param("id1") UUID id1,
                                                     @Param("id2") UUID id2);

var result = orderLineRepository.findAveragePriceByCurrency(id0, orderDate.minus(2, DAYS), orderDate.plus(2, DAYS), id1, id2);

 Map<String, BigDecimal> revenueByCurrency = result.stream()
                .collect(Collectors.toMap(k -> k.getCurrency(), v -> v.getPrice());