如何通过 Sum SQL 和 Spring Data JPA 使用组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27493944/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use a group by Sum SQL with Spring Data JPA?
提问by akcasoy
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
This is my Spring Data Repository:
这是我的 Spring 数据存储库：
@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();
}
I am getting HsqlException: Column not found: id
我收到HsqlException: Column not found: id
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?
我认为这个错误与 id 列没有任何关系，因为这两个表都存在。“按总和查询分组”是否适用于 Spring 数据？因为对我来说这似乎并不奇怪，因为 Spring Data 应该只从数据库中选择产品属性，并且使用这个 sql 我们还选择了 sum(po.quantity)。Spring数据可以处理这个并将结果转换为列表吗？
PS: I am using HSQLDB embedded as DB.
PS：我使用 HSQLDB 作为 DB 嵌入。
采纳答案by akcasoy
After changing the select statements projection from p.nameto p.*to indicate that I am selecting multiple values rather than just String objects that have to be magically converted to Productobjects, this works:
将 select 语句的投影从 更改p.name为p.*以指示我正在选择多个值而不仅仅是必须神奇地转换为Product对象的String对象后，这有效：
@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();
}
Thanks @JMK und @JB Nizet.
谢谢@JMK 和@JB Nizet。
