A correlated subquery is a query nested inside another query which uses values from the outer query.
Criteria API, provides methods for the situation where a correlated subquery uses a field of the outer (parent) query in the 'from' clause.
Following methods of Subquery allows to correlate the subquery with the parent query:
package javax.persistence.criteria;
....
public interface Subquery<T> extends AbstractQuery<T>, Expression<T> {
....
<Y> Root<Y> correlate(Root<Y> parentRoot);
<X, Y> Join<X, Y> correlate(Join<X, Y> parentJoin);
<X, Y> CollectionJoin<X, Y> correlate(CollectionJoin<X, Y> parentCollection);
<X, Y> SetJoin<X, Y> correlate(SetJoin<X, Y> parentSet);
<X, Y> ListJoin<X, Y> correlate(ListJoin<X, Y> parentList);
<X, K, V> MapJoin<X, K, V> correlate(MapJoin<X, K, V> parentMap);
....
}
Quick Example
Let's consider following JPQL:
Query query = em.createQuery(
"SELECT e FROM Employee e WHERE (SELECT COUNT(p) FROM e.phoneNumbers p) >= 2");
List<Employee> resultList = query.getResultList();
Equivalent code using Criteria Query:
//main query
CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
Root<Employee> employee = employeeQuery.from(Employee.class);
//phone count subquery
Subquery<Long> countSubQuery = employeeQuery.subquery(Long.class);
// following two lines make 'from' clause of the subquery
Root<Employee> employee2 = countSubQuery.correlate(employee);
ListJoin<Employee, String> phoneNumbers = employee2.join(Employee_.phoneNumbers);
//above two lines can be written as
//ListJoin<Employee, String> phoneNumbers = countSubQuery.correlate(employee).join(Employee_.phoneNumbers);
//subquery selection
countSubQuery.select(criteriaBuilder.count(phoneNumbers));
//main query selection
employeeQuery.select(employee)
.where(criteriaBuilder.greaterThanOrEqualTo(countSubQuery, 2L));
TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
List<Employee> employees = typedQuery.getResultList();
Complete Example
Entity
@Entity
public class Employee {
@Id
@GeneratedValue
private long id;
private String name;
private String dept;
private long salary;
@ElementCollection
private List<String> phoneNumbers;
.............
}
Using Subquery.correlate()
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEmployees();
findEmployeeByPhoneCount();
} finally {
entityManagerFactory.close();
}
}
private static void findEmployeeByPhoneCount() {
System.out.println("-- Employees who have more than 2 phones inclusively --");
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
//main query
CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
Root<Employee> employee = employeeQuery.from(Employee.class);
//phone count subquery
Subquery<Long> countSubQuery = employeeQuery.subquery(Long.class);
//create the subquery root correlated to the parent subquery root
Root<Employee> employee2 = countSubQuery.correlate(employee);
//employee-phones join
ListJoin<Employee, String> phoneNumbers = employee2.join(Employee_.phoneNumbers);
//subquery selection
countSubQuery.select(criteriaBuilder.count(phoneNumbers));
//main query selection
employeeQuery.select(employee)
.where(criteriaBuilder.greaterThanOrEqualTo(countSubQuery, 2L));
TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
List<Employee> employees = typedQuery.getResultList();
employees.forEach(System.out::println);
entityManager.close();
}
public static void persistEmployees() {
Employee employee1 = Employee.create("Diana", "IT", 3000, "111-111-111", "222-111-111");
Employee employee2 = Employee.create("Rose", "Admin", 2000, "333-111-111", "444-111-111");
Employee employee3 = Employee.create("Denise", "Admin", 4000, "555-111-111");
Employee employee4 = Employee.create("Mike", "IT", 3500, "777-111-111");
Employee employee5 = Employee.create("Linda", "Sales", 2000);
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("-- all employees --");
System.out.println(employee1);
System.out.println(employee2);
System.out.println(employee3);
System.out.println(employee4);
System.out.println(employee5);
}
} -- all employees -- Employee{id=1, name='Diana', dept='IT', salary=3000, phoneNumbers=[111-111-111, 222-111-111]} Employee{id=2, name='Rose', dept='Admin', salary=2000, phoneNumbers=[333-111-111, 444-111-111]} Employee{id=3, name='Denise', dept='Admin', salary=4000, phoneNumbers=[555-111-111]} Employee{id=4, name='Mike', dept='IT', salary=3500, phoneNumbers=[777-111-111]} Employee{id=5, name='Linda', dept='Sales', salary=2000, phoneNumbers=[]} -- Employees who have more than 2 phones inclusively -- Employee{id=1, name='Diana', dept='IT', salary=3000, phoneNumbers=[111-111-111, 222-111-111]} Employee{id=2, name='Rose', dept='Admin', salary=2000, phoneNumbers=[333-111-111, 444-111-111]}
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
|
|