JPA/Eclipselink sum all columns value using criteria

Question

I'm developing a web app using Spring MVC 4 with EclipseLink 2.5.0 and MySQL 5.6 in Windows 7. I'm using criteria (Criteria Builder and CriteriaQuery) to get the sum of all fields from the database. What I want to achieve is the equivalent in native MySQL of this statement.

SELECT SUM(che_monto) FROM ssi_cheque WHERE che_fecha BETWEEN '2015-03-01
' AND '2015-03-31';

| sum(che_monto) |
+----------------+
|    54351367.00 |

Note: che_monto is a Numeric(10,2) field.

The issue I have is that in several tutorials doesn't show how to make this (I think because is simple), showing only how to sum two factors or making complex math operations. Anyways I have generated the following code:

CriteriaBuilder criteriaBuilder = entityManager().getCriteriaBuilder();
CriteriaQuery<Number> query = criteriaBuilder.createQuery(Number.class);
Root<SsiCheque> fromSsiCheque = query.from(SsiCheque.class);
...
/** Code that works correctly to generate predicate */
...
query.select(criteriaBuilder.sum(ssiCheque.get("che_monto")))
    .where(predicates.toArray(new Predicate[]{}));

But I have the following compilation error:

Bound mismatch: The generic method sum(Expression) of type CriteriaBuilder is not applicable for the arguments (Path). The inferred type Object is not a valid substitute for the bounded parameter

What am I missing? Does eclipselink version is affecting?

Any help is appreciated.

Thanks in advance!!

Answer 1

The generic method sum(Expression) of type CriteriaBuilder is not applicable for the arguments (Path). The inferred type Object is not a valid substitute for the bounded parameter

The reason is that the compiler is unable to infer a proper type for the sum expression. According to JPA specification javax.persistence.CriteriaBuilder.sum method is defined as:

 <N extends Number> Expression<N> sum(Expression<N> x);

so it's expected to pass x as Number, Integer, Double, BigDecimal, etc. rather than Object. This is why the above error is produced. In other words the compiler is unable to perform an implicit cast from Object type represented by che_moto parameter to the Number type (or its descendant) which is expected by javax.persistence.CriteriaBuilder.sum method.

---

Assuming that che_monto field is defined as BigDecimal, an example solution may look as follows:

query.select(criteriaBuilder.sum(ssiCheque.<BigDecimal>get("che_monto")));

If you would like to make use of strongly typed query definitions with the Metamodel API then casting is no longer needed:

query.select(criteriaBuilder.sum(ssiCheque.get(_SsiCheque.che_monto)));