JPA Criteria API supports both simple and general case expressions.
Following methods/interfaces of CriteriaBuilder can be used to build case expressions.
package javax.persistence.criteria;
.........
public interface CriteriaBuilder {
.........
//Interface used to build simple case expressions.
public static interface SimpleCase<C,R> extends Expression<R> {
Expression<C> getExpression();
SimpleCase<C, R> when(C condition, R result);
SimpleCase<C, R> when(C condition, Expression<? extends R> result);
Expression<R> otherwise(R result);
Expression<R> otherwise(Expression<? extends R> result);
}
//Create a simple case expression.
<C, R> SimpleCase<C,R> selectCase(Expression<? extends C> expression);
//Interface used to build general case expressions
public static interface Case<R> extends Expression<R> {
Case<R> when(Expression<Boolean> condition, R result);
Case<R> when(Expression<Boolean> condition, Expression<? extends R> result);
Expression<R> otherwise(R result);
Expression<R> otherwise(Expression<? extends R> result);
}
//Create a general case expression.
<R> Case<R> selectCase();
.........
}
Quick Example
Simple case expression:
CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
Root<Employee> employee = query.from(Employee.class);
query.multiselect(employee.get(Employee_.name),
employee.get(Employee_.salary),
criteriaBuilder.selectCase(employee.get(Employee_.DEPT))
.when("IT", "Information Technology")
.when("Admin", "Administration")
.otherwise(employee.get(Employee_.DEPT))
);
//The equivalent JPQL
/* SELECT e.name, e.salary,
CASE e.dept
WHEN 'IT' THEN 'Information Technology'
WHEN 'Admin' THEN 'Administration'
ELSE e.dept
END
FROM Employee e*/
List<Object[]> resultList = entityManager.createQuery(query).getResultList();
General case expression:
CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
Root<Employee> employee = query.from(Employee.class);
query.multiselect(employee.get(Employee_.name),
employee.get(Employee_.salary),
criteriaBuilder.selectCase()
.when(criteriaBuilder
.equal(employee.get(Employee_.DEPT), "IT"), "Information Technology")
.when(criteriaBuilder.equal(employee.get(Employee_.DEPT), "Admin"), "Administration")
.otherwise(employee.get(Employee_.DEPT))
);
//The equivalent JPQL
/* SELECT e.name, e.salary,
CASE
WHEN e.dept='IT' THEN 'Information Technology'
WHEN e.dept='Admin' THEN 'Administration'
ELSE e.dept
END
FROM Employee e*/
List<Object[]> resultList = entityManager.createQuery(query).getResultList();
Complete Example
JPA Entity
@Entity
public class Employee {
@Id
@GeneratedValue
private long id;
private String name;
private double salary;
private String dept;
.............
}
Using selectCase() methods
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEmployees();
findAllEmployeesViaSimpleCase();
findAllEmployeesViaGeneralCase();
findAllEmployeesSalaryViaGeneralCase();
} finally {
entityManagerFactory.close();
}
}
private static void findAllEmployeesViaSimpleCase() {
EntityManager entityManager = entityManagerFactory.createEntityManager();
System.out.println("-- Finding all employees with simple case --");
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
Root<Employee> employee = query.from(Employee.class);
query.multiselect(employee.get(Employee_.name),
employee.get(Employee_.salary),
criteriaBuilder.selectCase(employee.get(Employee_.DEPT))
.when("IT", "Information Technology")
.when("Admin", "Administration")
.otherwise(employee.get(Employee_.DEPT))
);
//The equivalent JPQL
/* SELECT e.name, e.salary,
CASE e.dept
WHEN 'IT' THEN 'Information Technology'
WHEN 'Admin' THEN 'Administration'
ELSE e.dept
END
FROM Employee e*/
List<Object[]> resultList = entityManager.createQuery(query).getResultList();
resultList.forEach(arr -> System.out.println(Arrays.toString(arr)));
entityManager.close();
}
private static void findAllEmployeesViaGeneralCase() {
EntityManager entityManager = entityManagerFactory.createEntityManager();
System.out.println("-- Finding all employees with general case --");
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
Root<Employee> employee = query.from(Employee.class);
query.multiselect(employee.get(Employee_.name),
employee.get(Employee_.salary),
criteriaBuilder.selectCase()
.when(criteriaBuilder
.equal(employee.get(Employee_.DEPT), "IT"), "Information Technology")
.when(criteriaBuilder.equal(employee.get(Employee_.DEPT), "Admin"), "Administration")
.otherwise(employee.get(Employee_.DEPT))
);
//The equivalent JPQL
/* SELECT e.name, e.salary,
CASE
WHEN e.dept='IT' THEN 'Information Technology'
WHEN e.dept='Admin' THEN 'Administration'
ELSE e.dept
END
FROM Employee e*/
List<Object[]> resultList = entityManager.createQuery(query).getResultList();
resultList.forEach(arr -> System.out.println(Arrays.toString(arr)));
entityManager.close();
}
private static void findAllEmployeesSalaryViaGeneralCase() {
EntityManager entityManager = entityManagerFactory.createEntityManager();
System.out.println("-- Finding all employees salary with general case --");
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
Root<Employee> employee = query.from(Employee.class);
query.multiselect(employee.get(Employee_.name),
employee.get(Employee_.salary),
criteriaBuilder.selectCase()
.when(criteriaBuilder.lessThan(employee.get(Employee_.SALARY), 3000),
"Salary Less than 3000")
.when(criteriaBuilder.between(employee.get(Employee_.SALARY), 3000, 4000),
"Salary between 3000 and 4000")
.otherwise("Salary more than 4000")
);
List<Object[]> resultList = entityManager.createQuery(query).getResultList();
resultList.forEach(arr -> System.out.println(Arrays.toString(arr)));
entityManager.close();
}
public static void persistEmployees() {
Employee employee1 = Employee.create("Diana", 2000, "IT");
Employee employee2 = Employee.create("Rose", 3000, "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();
em.close();
System.out.println("-- employee persisted --");
System.out.println(employee1);
System.out.println(employee2);
System.out.println(employee3);
System.out.println(employee4);
System.out.println(employee5);
}
}-- employee persisted --
Employee{id=1, name='Diana', salary=2000.0, dept='IT'}
Employee{id=2, name='Rose', salary=3000.0, dept='Admin'}
Employee{id=3, name='Denise', salary=2500.0, dept='Admin'}
Employee{id=4, name='Mike', salary=4000.0, dept='IT'}
Employee{id=5, name='Linda', salary=4500.0, dept='Sales'}
-- Finding all employees with simple case --
[Diana, 2000.0, Information Technology]
[Rose, 3000.0, Administration]
[Denise, 2500.0, Administration]
[Mike, 4000.0, Information Technology]
[Linda, 4500.0, Sales]
-- Finding all employees with general case --
[Diana, 2000.0, Information Technology]
[Rose, 3000.0, Administration]
[Denise, 2500.0, Administration]
[Mike, 4000.0, Information Technology]
[Linda, 4500.0, Sales]
-- Finding all employees salary with general case --
[Diana, 2000.0, Salary Less than 3000]
[Rose, 3000.0, Salary between 3000 and 4000]
[Denise, 2500.0, Salary Less than 3000]
[Mike, 4000.0, Salary between 3000 and 4000]
[Linda, 4500.0, Salary more than 4000]
Example ProjectDependencies and Technologies Used: - h2 1.4.199: H2 Database Engine.
- hibernate-core 5.4.2.Final: Hibernate's core ORM functionality.
Implements javax.persistence:javax.persistence-api version 2.2 - hibernate-jpamodelgen 5.4.2.Final: Annotation Processor to generate JPA 2 static metamodel classes.
- CriteriaBuilder.selectCase() Examples
- JDK 1.8
- Maven 3.5.4
|
|
