How To Use Criteriaquery Sum Of Custom Operation On Some Cells?

Consider you have table T, with fields A and B. With regular SQL, I could do this: SELECT SUM(A * (100.0 - B) / 100.0) AS D FROM T; And I would get exactly what I expect. However,

Solution 1:

The CriteriaBuilder interface provides the following arithmetic functions:

• addition: sum(a, b)
• substraction: diff(a, b)
• multiplication: prod(a, b)
• division: quot(a, b)

where ab parameters can be an expression and/or literal.

As for the query, here is an exampe written in a human readable form:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Number> q = cb.createQuery(Number.class);
Root<T> t = q.from(T.class);

// build SUM(A * (100.0 - B) / 100.0) expression
Expression<Double> diff = cb.diff(100.0, t.<Double>get("B"));
Expression<Double> prod = cb.prod(t.<Double>get("A"), diff);
Expression<Number> quot = cb.quot(prod, 100.0);
Expression<Number> sum = cb.sum(quot);
q.select(sum.alias("D"));

System.out.println(em.createQuery(q).getSingleResult());

You can also build the query as an one-liner:

q.select(cb.sum(cb.quot(cb.prod(t.<Double>get("A"), cb.diff(100.0, t.<Double>get("B"))), 100.0)).alias("D"));

I hope it clarifies your doubts.