How to run an aggregate function like SUM on two columns in JPA and display their results?

Question

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

Below is a simple query in SQL that I would like to convert to JPA. I already have a TimeEnt entity TimeEnt .

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

Answer 1

The JPA query language supports aggregate functions in the SELECT clause, such as AVG, COUNT, MAX, MIN, SUM, and supports several select_expressions in the SELECT clause, in which case the result is a List the Object ( Object[] ) array. From the JPA specification:

4.8.1 Result SELECT clause type

...

The type of the SELECT result is determined by the result of the select_expressions types contained in it. When multiple select_expressions are used in SELECT, the query result is of type Object[] , and the elements in this result correspond to the order in the order they are specified in the SELECT clause and each of the select_expressions is type by type of result .

In other words, the type of request you mentioned in the comment (and since you did not provide your entity, I will describe my answer to your example), this is not a problem. Here is a sample code:

 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 SELECT clause type
  • 4.8.4 Aggregate Functions in the SELECT Section

Answer 2

Let's think that we have an object called 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(); 

Answer 3

Take a look at the EJB Query Language specification .

The idiom is very similar to standard SQL

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

Hello,