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 ProjectDependencies 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
|
|