CODEWITHSUNDEEP
javaormjpajava-ee-6jpa-2.0
BinCode
BinCode

Reputation: 633

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

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

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

Upvotes: 28

Views: 65865

Answers (3)

Binu S
Binu S

Reputation: 401

Lets think we have entity 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();

Upvotes: 9

Pascal Thivent
Pascal Thivent

Reputation: 570335

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

Upvotes: 41

Jose Diaz
Jose Diaz

Reputation: 5398

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,

Upvotes: 5

Related Questions