This tutorial shows how to use following arithmetic methods of CriteriaBuilder:

package javax.persistence.criteria;
 .........
public interface CriteriaBuilder {
    .........
    //Returns the arithmetic negation of its argument
    <N extends Number> Expression<N> neg(Expression<N> x);

    //Returns the absolute value of its argument
    <N extends Number> Expression<N> abs(Expression<N> x);
	
    //Create an expression that returns the sum of its arguments
    <N extends Number> Expression<N> sum(Expression<? extends N> x, Expression<? extends N> y);
    <N extends Number> Expression<N> sum(Expression<? extends N> x, N y);
    <N extends Number> Expression<N> sum(N x, Expression<? extends N> y);

    //Create an expression that returns the product of its arguments
    <N extends Number> Expression<N> prod(Expression<? extends N> x, Expression<? extends N> y);
    <N extends Number> Expression<N> prod(Expression<? extends N> x, N y);
    <N extends Number> Expression<N> prod(N x, Expression<? extends N> y);

    //Create an expression that returns the difference between its arguments
    <N extends Number> Expression<N> diff(Expression<? extends N> x, Expression<? extends N> y);
    <N extends Number> Expression<N> diff(Expression<? extends N> x, N y);
    <N extends Number> Expression<N> diff(N x, Expression<? extends N> y);
	
    //Create an expression that returns the quotient of its arguments (division)
    Expression<Number> quot(Expression<? extends Number> x, Expression<? extends Number> y);
    Expression<Number> quot(Expression<? extends Number> x, Number y);
    Expression<Number> quot(Number x, Expression<? extends Number> y);
	
    //Create an expression that returns the modulus of its arguments.
    Expression<Integer> mod(Expression<Integer> x, Expression<Integer> y);
    Expression<Integer> mod(Expression<Integer> x, Integer y);
    Expression<Integer> mod(Integer x, Expression<Integer> y);

    //Create an expression that returns the square root of its argument
    Expression<Double> sqrt(Expression<? extends Number> x);
	
    ...............
    //aggregate functions:
    <N extends Number> Expression<Double> avg(Expression<N> x);
    <N extends Number> Expression<N> sum(Expression<N> x);
    Expression<Long> sumAsLong(Expression<Integer> x);
    Expression<Double> sumAsDouble(Expression<Float> x);
    <N extends Number> Expression<N> max(Expression<N> x);
    <N extends Number> Expression<N> min(Expression<N> x);
    <X extends Comparable<? super X>> Expression<X> greatest(Expression<X> x);
    <X extends Comparable<? super X>> Expression<X> least(Expression<X> x);
    Expression<Long> count(Expression<?> x);
    Expression<Long> countDistinct(Expression<?> x);
    ...............
}

In above snippet, the aggregate functions are the ones which are applied to a single field (a single table column) rather than inter-fields arithmetic evaluation. Check out Criteria API Aggregate tutorial here.

Also check out JPQL Arithmetic tutorial here.

Example

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private double salary;
  private String dept;
    .............
}

Performing arithmetic operations

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          findAbsoluteDifferenceWithAvgSalary();
          findStandardDeviationOfSalaries();
          findSalariesNearestToThousands();
          findSalariesNearestToThousands2();
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void findAbsoluteDifferenceWithAvgSalary() {
      System.out.println("-- Find salaries' positive difference with average salary --");
      //first find average salary
      Double avgSal = getAverageSalary();
      System.out.println("Average Salary: " + avgSal);

      //now find positive difference with average salary
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
      Root<Employee> employeeRoot = query.from(Employee.class);
      query.multiselect(employeeRoot.get(Employee_.NAME),
              criteriaBuilder.abs(
                      criteriaBuilder.diff(employeeRoot.get(Employee_.salary), avgSal)
              )
      );
      //The equivalent JPQL:
      //"SELECT e.name, ABS(e.salary - avgSal) FROM Employee e")

      entityManager.createQuery(query).getResultList()
                   .forEach(arr -> System.out.println(Arrays.toString(arr)));
      entityManager.close();
  }

  private static Double getAverageSalary() {
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Double> query = criteriaBuilder.createQuery(Double.class);
      Root<Employee> employeeRoot = query.from(Employee.class);
      query.select(criteriaBuilder.avg(employeeRoot.get(Employee_.SALARY)));
      Double avgSal = entityManager.createQuery(query)
                                   .getSingleResult();
      entityManager.close();
      return avgSal;
  }

  private static void findStandardDeviationOfSalaries() {
      System.out.println("-- Find salaries' standard deviation --");
      //find average salary
      Double avgSal = getAverageSalary();

      //find square root of squared sum salary difference from avg
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

      CriteriaQuery<Double> queryDeviationSum = criteriaBuilder.createQuery(Double.class);
      Root<Employee> employeeRoot = queryDeviationSum.from(Employee.class);
      Expression<Double> salDiffWithAvgSal = criteriaBuilder.diff(employeeRoot.get(Employee_.salary), avgSal);
      queryDeviationSum.select(criteriaBuilder.sqrt(
              criteriaBuilder.sum(criteriaBuilder.prod(salDiffWithAvgSal, salDiffWithAvgSal))));
      //The equivalent JPQL:
      //SELECT SQRT(SUM((e.salary - :avgSal)*(e.salary - :avgSal))) from Employee e
      Double deviationSum = entityManager.createQuery(queryDeviationSum).getSingleResult();

      //find square root of employee's count
      CriteriaQuery<Double> querySampleSizeSquaredRoot = criteriaBuilder.createQuery(Double.class);
      Root<Employee> employeeRoot2 = querySampleSizeSquaredRoot.from(Employee.class);
      querySampleSizeSquaredRoot.select(criteriaBuilder.sqrt(criteriaBuilder.count(employeeRoot2)));
      Double sampleSizeSquaredRoot = entityManager.createQuery(querySampleSizeSquaredRoot).getSingleResult();
      //The equivalent JPQL:
      //SELECT SQRT(COUNT(e)) from Employee e

      Double standardDeviation = deviationSum / sampleSizeSquaredRoot;
      System.out.println("standard deviation: " + standardDeviation);
      entityManager.close();
  }

  private static void findSalariesNearestToThousands() {
      System.out.println("-- Find salaries nearest to 1000 using mod() --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
      Root<Employee> employeeRoot = query.from(Employee.class);
      query.multiselect(employeeRoot.get(Employee_.NAME),
              criteriaBuilder.diff(
                      employeeRoot.get(Employee_.SALARY),
                      criteriaBuilder.mod(employeeRoot.get(Employee_.SALARY), 1000)
              )
      );
      //The equivalent JPQL:
      //SELECT e.name, e.salary - MOD(e.salary, 1000) from Employee e

      List<Object[]> resultList = entityManager.createQuery(query).getResultList();
      resultList.forEach(o -> System.out.println(Arrays.toString(o)));
      entityManager.close();
  }

  private static void findSalariesNearestToThousands2() {
      System.out.println("-- Find salaries nearest to 1000 using quot() --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
      Root<Employee> employeeRoot = query.from(Employee.class);
      query.multiselect(employeeRoot.get(Employee_.NAME),
              criteriaBuilder.prod(
                      criteriaBuilder.quot(
                              employeeRoot.get(Employee_.SALARY).as(Integer.class),
                              1000
                      ).as(Integer.class),
                      1000
              )
      );
      //The equivalent JPQL:
      //SELECT e.name, e.salary - MOD(e.salary, 1000) from Employee e

      List<Object[]> resultList = entityManager.createQuery(query).getResultList();
      resultList.forEach(o -> System.out.println(Arrays.toString(o)));
      entityManager.close();
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", 2000, "IT");
      Employee employee2 = Employee.create("Rose", 3500, "Admin");
      Employee employee3 = Employee.create("Denise", 2500, "Admin");
      Employee employee4 = Employee.create("Mike", 4000, "IT");
      Employee employee5 = Employee.create("Linda", 4500, "Sales");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.persist(employee5);
      em.getTransaction().commit();

      System.out.println("-- Employees persisted --");
      Query query = em.createQuery(
              "SELECT e FROM Employee e");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }
}

-- Employees persisted --
Employee{id=1, name='Diana', salary=2000.0, dept='IT', phoneNumbers=[111-111-111, 666-666-666, 777-777-777]}
Employee{id=2, name='Rose', salary=3500.0, dept='Admin', phoneNumbers=[222-222,222, 888-888-888]}
Employee{id=3, name='Denise', salary=2500.0, dept='Admin', phoneNumbers=[333-333-333]}
Employee{id=4, name='Mike', salary=4000.0, dept='IT', phoneNumbers=[444-444-444]}
Employee{id=5, name='Linda', salary=4500.0, dept='Sales', phoneNumbers=[555-555-555]}
-- Find salaries' positive difference with average salary --
Average Salary: 3300.0
[Diana, 1300.0]
[Rose, 200.0]
[Denise, 800.0]
[Mike, 700.0]
[Linda, 1200.0]
-- Find salaries' standard deviation --
standard deviation: 927.3618495495705
-- Find salaries nearest to 1000 using mod() --
[Diana, 2000.0]
[Rose, 3000.0]
[Denise, 2000.0]
[Mike, 4000.0]
[Linda, 4000.0]
-- Find salaries nearest to 1000 using quot() --
[Diana, 2000]
[Rose, 3000]
[Denise, 2000]
[Mike, 4000]
[Linda, 4000]

Example Project

Dependencies and Technologies Used:

• hibernate-core 5.4.1.Final: Hibernate's core ORM functionality.
  Implements javax.persistence:javax.persistence-api version 2.2
• hibernate-jpamodelgen 5.4.1.Final: Annotation Processor to generate JPA 2 static metamodel classes.
• h2 1.4.198: H2 Database Engine.
• JDK 1.8
• Maven 3.5.4