Following method of CriteriaBuilder can be used to apply EXISTS predicate in WHERE clause:
package javax.persistence.criteria;
........
public interface CriteriaBuilder {
........
//Creates a predicate testing the existence of a subquery result.
Predicate exists(Subquery<?> subquery);
........
}
To apply 'NOT EXISTS' predicate we can use criteriaBuilder.not(criteriaBuilder.exists(.....)).
Quick Example
Consider following JPQL:
"SELECT e FROM Employee e WHERE EXISTS (SELECT j from JobInfo j WHERE j.jobName = e.job)"
The equivalent code using Criteria API:
//main query
CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
Root<Employee> employee = employeeQuery.from(Employee.class);
//subquery
Subquery<JobInfo> jobInfoSubquery = employeeQuery.subquery(JobInfo.class);
Root<JobInfo> jobInfo = jobInfoSubquery.from(JobInfo.class);
jobInfoSubquery.select(jobInfo)//subquery selection
.where(criteriaBuilder.equal(jobInfo.get(JobInfo_.jobName),
employee.get(Employee_.job)));//subquery restriction
//main query selection
employeeQuery.select(employee)
.where(criteriaBuilder.exists(jobInfoSubquery));
TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
List<Employee> resultList = typedQuery.getResultList();
Complete Example
Entities
@Entity
public class Employee {
@Id
@GeneratedValue
private long id;
private String name;
private String job;
.............
}
@Entity
public class JobInfo {
@Id
@GeneratedValue
private long id;
private String jobName;
private String jobType;
.............
}
Using CriteriaBuilder.exists() method:
The first method findEmployeeIfJobInfoExists uses 'EXISTS' predicate in WHERE clause. The result should be the employees whose job details exists in JobInfo table. The second method findEmployeeIfJobInfoNotExists uses 'NOT EXISTS' expression in WHERE clause which returns the employees whose job details does not exist in JobInfoTable.
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEntities();
findEmployeeIfJobInfoExists();
findEmployeesIfJobInfoNotExists();
} finally {
entityManagerFactory.close();
}
}
private static void findEmployeeIfJobInfoExists() {
System.out.println("-- executing EXISTS query --");
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
//main query
CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
Root<Employee> employee = employeeQuery.from(Employee.class);
//subquery
Subquery<JobInfo> jobInfoSubquery = employeeQuery.subquery(JobInfo.class);
Root<JobInfo> jobInfo = jobInfoSubquery.from(JobInfo.class);
jobInfoSubquery.select(jobInfo)//subquery selection
.where(criteriaBuilder.equal(jobInfo.get(JobInfo_.jobName),
employee.get(Employee_.job)));//subquery restriction
//main query selection
employeeQuery.select(employee)
.where(criteriaBuilder.exists(jobInfoSubquery));
TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
List<Employee> resultList = typedQuery.getResultList();
resultList.forEach(System.out::println);
entityManager.close();
// equivalent JPQL
//"SELECT e FROM Employee e WHERE EXISTS (SELECT j from JobInfo j WHERE j.jobName = e.job)"
}
private static void findEmployeesIfJobInfoNotExists() {
System.out.println("-- executing NOT EXISTS query --");
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
//main query
CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
Root<Employee> employee = employeeQuery.from(Employee.class);
//subquery
Subquery<JobInfo> jobInfoSubquery = employeeQuery.subquery(JobInfo.class);
Root<JobInfo> jobInfo = jobInfoSubquery.from(JobInfo.class);
jobInfoSubquery.select(jobInfo)
.where(criteriaBuilder.equal(jobInfo.get(JobInfo_.jobName),
employee.get(Employee_.job)));
//main query selection
employeeQuery.select(employee)
.where(criteriaBuilder.not(criteriaBuilder.exists(jobInfoSubquery)));
TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
List<Employee> resultList = typedQuery.getResultList();
resultList.forEach(System.out::println);
entityManager.close();
///equivalent JPQL
// "SELECT e FROM Employee e WHERE NOT EXISTS(SELECT j from JobInfo j WHERE j.jobName = e.job)"
}
public static void persistEntities() {
System.out.println("-- persisting entities --");
EntityManager em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();
List<Employee> employees = getEmployees();
employees.forEach(em::persist);
List<JobInfo> jobInfoList = getJobInfoList();
jobInfoList.forEach(em::persist);
em.getTransaction().commit();
em.close();
employees.forEach(System.out::println);
jobInfoList.forEach(System.out::println);
}
private static List<Employee> getEmployees() {
List<Employee> employees = new ArrayList<>();
employees.add(Employee.create("Diana", "Developer"));
employees.add(Employee.create("Mike", "Manager"));
employees.add(Employee.create("Tim", "Salesman"));
employees.add(Employee.create("Jack", "Architect"));
return employees;
}
private static List<JobInfo> getJobInfoList() {
List<JobInfo> list = new ArrayList<>();
list.add(JobInfo.create("Developer", "Consultant"));
list.add(JobInfo.create("Manager", "FullTime"));
list.add(JobInfo.create("Architect", "Consultant"));
return list;
}
} -- persisting entities -- Employee{id=1, name='Diana', job='Developer'} Employee{id=2, name='Mike', job='Manager'} Employee{id=3, name='Tim', job='Salesman'} Employee{id=4, name='Jack', job='Architect'} JobInfo{id=5, jobName='Developer', jobType='Consultant'} JobInfo{id=6, jobName='Manager', jobType='FullTime'} JobInfo{id=7, jobName='Architect', jobType='Consultant'} -- executing EXISTS query -- Employee{id=1, name='Diana', job='Developer'} Employee{id=2, name='Mike', job='Manager'} Employee{id=4, name='Jack', job='Architect'} -- executing NOT EXISTS query -- Employee{id=3, name='Tim', job='Salesman'}
Example ProjectDependencies and Technologies Used: - hibernate-core 5.4.0.Final: Hibernate's core ORM functionality.
Implements javax.persistence:javax.persistence-api version 2.2 - hibernate-jpamodelgen 5.4.0.Final: Annotation Processor to generate JPA 2 static metamodel classes.
- h2 1.4.197: H2 Database Engine.
- JDK 1.8
- Maven 3.5.4
|