如何将Sum SQL和Spring Data JPA一起使用?

问题描述

我想按数量装载最畅销的产品.这些是我的表格:

I want to load best seller products by quantity. These are my tables:

Product
id  name
1    AA
2    BB

Productorder
order_id  product_id  quantity
1          1          10
2          1          100
3          2          15
4          1          15

这是我的Spring数据存储库:

This is my Spring Data Repository:

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = "select top 5 p.name, sum(po.quantity) as total_quantity from product p " +
            "inner join productorder po " +
                "on p.id = po.product_id " +
            "group by p.id, p.name " +
            "order by total_quantity desc", nativeQuery = true)
    List<Product> findTopFiveBestSeller();
}

我正在获取 HsqlException:找不到列:id

我认为此错误与id列无关，因为这两个表都存在. 按汇总查询"可以用于Spring数据吗?因为对于Spring Data应该仅从数据库中选择产品属性，这对我来说似乎并不奇怪，并且使用此sql我们还选择了sum(po.quantity). Spring数据可以处理此问题并将结果转换为列表吗?

I think this error does not have anything to do with id column, as it is there for both tables. Do "group by Sum queries" work with Spring data? Because it seems little strange for me as Spring Data should select just product attributes from the database, and with this sql we are selecting also the sum(po.quantity). Can Spring data handle this and convert the result as a List?

PS:我正在使用作为数据库嵌入的HSQLDB.

PS: I am using HSQLDB embedded as DB.

推荐答案

将select语句的显示从p.name更改为p.*后，表明我在选择多个值，而不仅仅是必须魔术转换的String对象到Product对象，可以正常工作:

After changing the select statements projection from p.name to p.* to indicate that I am selecting multiple values rather than just String objects that have to be magically converted to Product objects, this works:

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = "select top 5 p.*, sum(po.quantity) as total_quantity from product p " +
        "inner join productorder po " +
            "on p.id = po.product_id " +
        "group by p.id, p.name " +
        "order by total_quantity desc", nativeQuery = true)
    List<Product> findTopFiveBestSeller();

}

感谢@JMK和@JB Nizet.

Thanks @JMK und @JB Nizet.

这篇关于如何将Sum SQL和Spring Data JPA一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！