java JPA 和聚合函数。如何使用查询结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2911558/
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
JPA and aggregate functions. How do I use the result of the query?
提问by Bogdan
I'm new to ORM stuff and I need some help understanding something.
我是 ORM 的新手，我需要一些帮助来理解某些东西。
Let's assume I have the following standard SQL query:
假设我有以下标准 SQL 查询：
SELECT *, COUNT(test.testId) AS noTests FROM inspection
LEFT JOIN test ON inspection.inspId = test.inspId
GROUP BY inspection.inspId
which I want to use in JPA.
我想在 JPA 中使用。
I have an Inspection entity with a one-to-many relationship to a Test entity. (an inspection has many tests) I tried writing this in JPQL:
我有一个与测试实体具有一对多关系的检查实体。（检查有很多测试）我试着用 JPQL 写这个：
Query query = em.createQuery("SELECT insp, COUNT(???what???) " +
"FROM Inspection insp LEFT JOIN insp.testList " +
"GROUP BY insp.inspId");
1) How do I write the COUNT clause? I'd have to apply count to elements from the test table but testList is a collection, so I can't do smth like COUNT(insp.testList.testId)
1) 如何编写 COUNT 子句？我必须对测试表中的元素应用计数，但 testList 是一个集合，所以我不能像COUNT(insp.testList.testId)
2) Assuming 1 is resolved, what type of object will be returned. It will definitely not be an Inspection object... How do I use the result?
2）假设1被解析，返回什么类型的对象。它绝对不会是一个检查对象......我如何使用结果？
回答by Bozho
- You can give an alias to the joined entity (with
AS) - You can create either a new object, or a
Listwith the returned values
- 您可以为加入的实体指定别名（使用
AS） - 您可以创建一个新对象，或者
List使用返回值创建一个
So:
所以：
SELECT new com.yourproject.ResultHolder(insp, COUNT(test.testId))
FROM Inspection insp LEFT JOIN insp.testList AS test GROUP BY insp.inspId
Or
或者
SELECT new list(insp, COUNT(test.testId))
FROM Inspection insp LEFT JOIN insp.testList AS test GROUP BY insp.inspId
The result is then accessible either as an instance of ResultHolder, or as a java.util.List, where the inspis list.get(0), and the count is list.get(1)
然后可以将结果作为 的实例ResultHolder或作为 访问java.util.List，其中insp是list.get(0)，计数是list.get(1)
