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