如何在 JPA 中的两列上运行像 SUM 这样的聚合函数并显示它们的结果? [英] How to run an aggregate function like SUM on two columns in JPA and display their results?

问题描述

我是 JPA 的新手.所以我的问题对某些人来说应该很简单.

I am new to JPA. So my question should be so simple to some.

下面是我想转换为 JPA 的 SQL 中的简单查询.我已经有一个名为 TimeEnt 的实体类.

Below is the Simple Query in SQL which i would like to convert to JPA. I already have an entity class called TimeEnt.

SELECT 
     SUM(TimeEntryActualHours) as UnBilledHrs,
     SUM (TimeEntryAmount) as UnbilledAmount
FROM TimeEnt WHERE MatterID = 200

推荐答案

JPA 查询语言确实支持 SELECT 子句中的聚合函数，例如 AVG、COUNT、MAX、MIN、SUM，并且确实支持多个 select_expressions 在 SELECT 子句中，在这种情况下，结果是 Object 数组 (Object[]) 的 List.来自 JPA 规范:

The JPA Query Language does support aggregates functions in the SELECT clause like AVG, COUNT, MAX, MIN, SUM and does support multiple select_expressions in the SELECT clause, in which case the result is a List of Object array (Object[]). From the JPA specification:

...

SELECT 的结果类型子句由结果定义select_expressions 的类型包含在其中.当多个select_expressions 用于SELECT 子句，查询的结果是 Object[] 类型，并且此结果中的元素对应于按照他们的顺序SELECT 子句中的规范和类型到结果类型每个select_expressions.

The result type of the SELECT clause is defined by the the result types of the select_expressions contained in it. When multiple select_expressions are 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.

换句话说，支持您在评论中提到的查询类型(并且由于您没有提供您的实体，我将根据您的示例回答)，没问题.这是一个代码示例:

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);
}

参考资料

• JPA 1.0 规范
  • 4.8.1 SELECT 子句的结果类型
  • 4.8.4 SELECT 子句中的聚合函数

  这篇关于如何在 JPA 中的两列上运行像 SUM 这样的聚合函数并显示它们的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！