Close

JPA Criteria API - Aggregate Methods

[Updated: Dec 14, 2018, Created: Dec 14, 2018]

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

package javax.persistence.criteria;
 .....
public interface CriteriaBuilder {
 .....
   //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);
 .....
}	

The methods greatest() and least() are used for Comparables (Date, String, Number etc) whereas other methods are used for only a Number type.

Quick Example

  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  CriteriaQuery<Double> query = criteriaBuilder.createQuery(Double.class);
  Root<Employee> employee = query.from(Employee.class);
  query.select(criteriaBuilder.avg(employee.get(Employee_.salary)));
  TypedQuery<Double> typedQuery = entityManager.createQuery(query);
  Double average = typedQuery.getSingleResult();

Examples

Entity

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

Using aggregate methods

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeeCount();
          findEmployeeAvgSalary();
          findEmployeeMaxSalary();
          findEmployeeMinSalary();
          findEmployeeSalariesSum();
          findOldestJoinDate();
          findNewestJoinDate();
          findGreatestNameAlphabetically();
          findLeastNameAlphabetically();

      } finally {
          entityManagerFactory.close();
      }
  }

  private static void findEmployeeCount() {
      System.out.println("-- Employee COUNT --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(criteriaBuilder.count(employee));
      TypedQuery<Long> typedQuery = entityManager.createQuery(query);
      Long count = typedQuery.getSingleResult();
      System.out.println(count);
      entityManager.close();
  }

  private static void findEmployeeAvgSalary() {
      System.out.println("-- Employee AVG Salary --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Double> query = criteriaBuilder.createQuery(Double.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(criteriaBuilder.avg(employee.get(Employee_.salary)));
      TypedQuery<Double> typedQuery = entityManager.createQuery(query);
      Double average = typedQuery.getSingleResult();
      System.out.println(average);
      entityManager.close();
  }

  private static void findEmployeeMaxSalary() {
      System.out.println("-- Employee MAX Salary --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(criteriaBuilder.max(employee.get(Employee_.salary)));
      TypedQuery<Long> typedQuery = entityManager.createQuery(query);
      Long max = typedQuery.getSingleResult();
      System.out.println(max);
      entityManager.close();
  }

  private static void findEmployeeMinSalary() {
      System.out.println("-- Employee MIN Salary --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(criteriaBuilder.min(employee.get(Employee_.salary)));
      TypedQuery<Long> typedQuery = entityManager.createQuery(query);
      Long min = typedQuery.getSingleResult();
      System.out.println(min);
      entityManager.close();
  }

  private static void findEmployeeSalariesSum() {
      System.out.println("-- Employee SUM Salary --");
      EntityManager em = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
      CriteriaQuery<BigDecimal> query = criteriaBuilder.createQuery(BigDecimal.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(criteriaBuilder.sum(employee.get(Employee_.salary).as(BigDecimal.class)));
      TypedQuery<BigDecimal> typedQuery = em.createQuery(query);
      BigDecimal sum = typedQuery.getSingleResult();
      System.out.println(sum);
      em.close();
  }

  private static void findOldestJoinDate() {
      System.out.println("-- oldest join date --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<LocalDate> query = criteriaBuilder.createQuery(LocalDate.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(criteriaBuilder.least(employee.get(Employee_.joinDate)));
      TypedQuery<LocalDate> typedQuery = entityManager.createQuery(query);
      LocalDate least = typedQuery.getSingleResult();
      System.out.println(least);
      entityManager.close();
  }

  private static void findNewestJoinDate() {
      System.out.println("-- newest join date --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<LocalDate> query = criteriaBuilder.createQuery(LocalDate.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(criteriaBuilder.greatest(employee.get(Employee_.joinDate)));
      TypedQuery<LocalDate> typedQuery = entityManager.createQuery(query);
      LocalDate greatest = typedQuery.getSingleResult();
      System.out.println(greatest);
      entityManager.close();
  }


  private static void findGreatestNameAlphabetically() {
      System.out.println("-- greatest name alphabetically --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<String> query = criteriaBuilder.createQuery(String.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(criteriaBuilder.greatest(employee.get(Employee_.name)));
      TypedQuery<String> typedQuery = entityManager.createQuery(query);
      String greatest = typedQuery.getSingleResult();
      System.out.println(greatest);
      entityManager.close();
  }

  private static void findLeastNameAlphabetically() {
      System.out.println("-- least name alphabetically --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<String> query = criteriaBuilder.createQuery(String.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(criteriaBuilder.least(employee.get(Employee_.name)));
      TypedQuery<String> typedQuery = entityManager.createQuery(query);
      String least = typedQuery.getSingleResult();
      System.out.println(least);
      entityManager.close();
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", 3000, LocalDate.of(1999, 6, 1));
      Employee employee2 = Employee.create("Rose", 4000, LocalDate.of(2005, 2, 1));
      Employee employee3 = Employee.create("Denise", 1500, LocalDate.of(2011, 1, 1));
      Employee employee4 = Employee.create("Mike", 2000, LocalDate.of(2007, 8, 1));
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.getTransaction().commit();
      em.close();
      System.out.println("-- all employees --");
      System.out.println(employee1);
      System.out.println(employee2);
      System.out.println(employee3);
      System.out.println(employee4);
  }
}
-- all employees --
Employee{id=1, name='Diana', salary=3000, joinDate=1999-06-01}
Employee{id=2, name='Rose', salary=4000, joinDate=2005-02-01}
Employee{id=3, name='Denise', salary=1500, joinDate=2011-01-01}
Employee{id=4, name='Mike', salary=2000, joinDate=2007-08-01}
-- Employee COUNT --
4
-- Employee AVG Salary --
2625.0
-- Employee MAX Salary --
4000
-- Employee MIN Salary --
1500
-- Employee SUM Salary --
10500.0
-- oldest join date --
1999-06-01
-- newest join date --
2011-01-01
-- greatest name alphabetically --
Rose
-- least name alphabetically --
Denise

Example Project

Dependencies and Technologies Used:

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

Aggregation Methods Select All Download
  • jpa-criteria-api-aggregation-methods
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also