How to run agregate function like Sum on two columsn in JPA and display there results?

Question

Hi,

I am new to JPA.So my question would sould so simple to some. :)

Below is the Simple Query in SQL which i would like to convert to JPA.I already have the an Entity Class as TimeEnt

SELECT SUM (TimeEntryActualHours) As UnBilledHrs,SUM (TimeEntryAmount) As UnbilledAmount
From TimeEnt Where MatterID = 200;

Thanks a lot for you help.

Answer 1

Take a look at the EJB Query Language specification.

The idiom is very similiar to standard SQL

EntityManager em = ...
Query q = em.createQuery ("SELECT AVG(x.price) FROM Magazine x");
Number result = (Number) q.getSingleResult ();

Regards,

Answer 2

See here it might be helpful

Answer 3

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:

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

References

• JPA 1.0 Specification
  • 4.8.1 Result Type of the SELECT Clause
  • 4.8.4 Aggregate Functions in the SELECT Clause