Java 如何在JPA中的两列上运行像SUM这样的聚合函数并显示它们的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3449719/
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 run an aggregate function like SUM on two columns in JPA and display their results?
提问by BinCode
I am new to JPA. So my question should be so simple to some.
我是 JPA 的新手。所以我的问题对某些人来说应该很简单。
Below is the Simple Query in SQL which i would like to convert to JPA. I already have an entity class called TimeEnt.
下面是我想转换为 JPA 的 SQL 中的简单查询。我已经有一个名为TimeEnt.
SELECT
SUM(TimeEntryActualHours) as UnBilledHrs,
SUM (TimeEntryAmount) as UnbilledAmount
FROM TimeEnt WHERE MatterID = 200
采纳答案by Pascal Thivent
The JPA Query Language does support aggregates functions in the SELECT clause like AVG, COUNT, MAX, MIN, SUM and does support multiple select_expressionsin the SELECT clause, in which case the result is a Listof Objectarray (Object[]). From the JPA specification:
在JPA查询语言确实像AVG，COUNT，MAX，MIN，SUM SELECT子句中载体聚集体的功能和不支持多个select_expressionsSELECT子句中，在这种情况下，结果是一个List的Object阵列（Object[]）。从 JPA 规范：
4.8.1 Result Type of the SELECT Clause
...
The result type of the SELECT clause is defined by the the result types of the select_expressionscontained in it. When multiple select_expressionsare used in the SELECT clause, the result of the query is of type
Object[], and the elements in this result correspond in order to the order of their specification in the SELECT clause and in type to the result types of each of the select_expressions.
4.8.1 SELECT 子句的结果类型
...
SELECT 子句的结果类型由其中 包含的select_expressions的结果类型定义。当SELECT子句中使用多个 select_expressions时，查询的结果是type
Object[]，这个结果中的元素按照它们在SELECT子句中的说明顺序和type对应每个select_expressions的结果类型.
In other words, the kind of query you mentioned in a comment (and since you didn't provide your entity, I'll base my answer on your example) is supported, no problem. Here is a code sample:
换句话说，支持您在评论中提到的查询类型（并且由于您没有提供您的实体，我将根据您的示例回答），没问题。这是一个代码示例：
String qlString = "SELECT AVG(x.price), SUM(x.stocks) FROM Magazine x WHERE ...";
Query q = em.createQuery(qlString);
Object[] results = (Object[]) q.getSingleResult();
for (Object object : results) {
System.out.println(object);
}
References
参考
- JPA 1.0 Specification
- 4.8.1 Result Type of the SELECT Clause
- 4.8.4 Aggregate Functions in the SELECT Clause
- JPA 1.0 规范
- 4.8.1 SELECT 子句的结果类型
- 4.8.4 SELECT 子句中的聚合函数
回答by Jose Diaz
Take a look at the EJB Query Languagespecification.
查看EJB 查询语言规范。
The idiom is very similiar to standard SQL
习惯用法与标准 SQL 非常相似
EntityManager em = ...
Query q = em.createQuery ("SELECT AVG(x.price) FROM Magazine x");
Number result = (Number) q.getSingleResult ();
Regards,
问候，
回答by Binu S
Lets think we have entity called Product:
让我们认为我们有名为 的实体Product：
final Query sumQuery = entityManager
.createQuery("SELECT SUM(p.price), SUM(p.sale) FROM Product p WHERE p.item=:ITEM AND ....");
sumQuery.setParameter("ITEM","t1");
final Object result= sumQuery.getSingleResult(); // Return an array Object with 2 elements, 1st is sum(price) and 2nd is sum(sale).
//If you have multiple rows;
final Query sumQuery = entityManager
.createQuery("SELECT SUM(p.price), SUM(p.sale) FROM Product p WHERE p.item in (" + itemlist
+ ") AND ....");
// Return a list of arrays, where each array correspond to 1 item (row) in resultset.
final List<IEniqDBEntity> sumEntityList = sumQuery.getResultList();
