In Criteria API, following method in CommonAbstractCriteria interface allows to create subqueries:
package javax.persistence.criteria;
....
public interface CommonAbstractCriteria {
....
<U> Subquery<U> subquery(Class<U> type);
....
}
CommonAbstractCriteria is implemented by all kind of query objects including CriteriaQuery and Subquery itself.
Following is Subquery interface snippet:
package javax.persistence.criteria;
....
public interface Subquery<T> extends AbstractQuery<T>, Expression<T> {
Subquery<T> select(Expression<T> expression);
Subquery<T> where(Expression<Boolean> restriction);
Subquery<T> where(Predicate... restrictions);
Subquery<T> groupBy(Expression<?>... grouping);
Subquery<T> groupBy(List<Expression<?>> grouping);
Subquery<T> having(Expression<Boolean> restriction);
Subquery<T> having(Predicate... restrictions);
Subquery<T> distinct(boolean distinct);
....
}
As seen above, just like CriteriaQuery , Subquery also has select, restriction, join etc methods.
Subquery also implements Expression , so that means it can be used in all those places of Criteria API where expression is used.
Quick Example
//main query
CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
//main query from
Root<Employee> employee = employeeQuery.from(Employee.class);
//create subquery
Subquery<Double> averageSalarySubQuery = employeeQuery.subquery(Double.class);
//subquery from
Root<Employee> subQueryEmployee = averageSalarySubQuery.from(Employee.class);
//subquery selection
averageSalarySubQuery.select(criteriaBuilder.avg(subQueryEmployee.get(Employee_.salary)));
//main query selection
employeeQuery.select(employee)
.where(criteriaBuilder.lessThan(employee.get(Employee_.salary).as(Double.class),
averageSalarySubQuery));
TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
List<Employee> employees = typedQuery.getResultList();
Above query is equivalent to following JPQL:
Query query = entityManager.createQuery(
"SELECT e FROM Employee e where e.salary < (SELECT AVG(e2.salary) FROM Employee e2)");
List<Employee> resultList = query.getResultList();
Example
Entity
@Entity
public class Employee {
@Id
@GeneratedValue
private long id;
private String name;
private String dept;
private long salary;
.............
}
Using subquery()
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEmployees();
//show some pre info
showNetAvgSalary();
findEmployeeWithLessThanAverageSalary();
showAvgSalaryByDept();
findDeptHavingAboveNetAverageSalary();
} finally {
entityManagerFactory.close();
}
}
private static void showNetAvgSalary() {
System.out.println("-- net average salary --");
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Double> netAvgSalaryQuery = criteriaBuilder.createQuery(Double.class);
Root<Employee> employee = netAvgSalaryQuery.from(Employee.class);
netAvgSalaryQuery.select(criteriaBuilder.avg(employee.get(Employee_.salary)));
Double netAvgSalary = entityManager.createQuery(netAvgSalaryQuery).getSingleResult();
System.out.println(netAvgSalary);
}
private static void showAvgSalaryByDept() {
System.out.println("-- Dept by average salaries --");
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> netAvgSalaryQueryByDept = criteriaBuilder.createTupleQuery();
Root<Employee> employee = netAvgSalaryQueryByDept.from(Employee.class);
netAvgSalaryQueryByDept.groupBy(employee.get(Employee_.dept));
netAvgSalaryQueryByDept.multiselect(employee.get(Employee_.dept),
criteriaBuilder.avg(employee.get(Employee_.salary)));
TypedQuery<Tuple> typedQuery = entityManager.createQuery(netAvgSalaryQueryByDept);
List<Tuple> resultList = typedQuery.getResultList();
resultList.forEach(tuple ->
System.out.printf("Dept: %s, Avg Salary: %s%n", tuple.get(0), tuple.get(1)));
}
private static void findEmployeeWithLessThanAverageSalary() {
System.out.println("-- Employees who have less than average salary --");
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
//main query
CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
//main query from
Root<Employee> employee = employeeQuery.from(Employee.class);
//create subquery
Subquery<Double> averageSalarySubQuery = employeeQuery.subquery(Double.class);
//subquery from
Root<Employee> subQueryEmployee = averageSalarySubQuery.from(Employee.class);
//subquery selection
averageSalarySubQuery.select(criteriaBuilder.avg(subQueryEmployee.get(Employee_.salary)));
//main query selection
employeeQuery.select(employee)
.where(criteriaBuilder
.lessThan(employee.get(Employee_.salary).as(Double.class), averageSalarySubQuery));
TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
List<Employee> employees = typedQuery.getResultList();
employees.forEach(System.out::println);
entityManager.close();
}
private static void findDeptHavingAboveNetAverageSalary() {
System.out.println("-- Dept having AVG salaries greater than net AVG salary --");
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
//main query
CriteriaQuery<Tuple> netAvgSalaryQueryByDept = criteriaBuilder.createTupleQuery();
Root<Employee> employee = netAvgSalaryQueryByDept.from(Employee.class);
netAvgSalaryQueryByDept.groupBy(employee.get(Employee_.dept));
netAvgSalaryQueryByDept.multiselect(employee.get(Employee_.dept),
criteriaBuilder.avg(employee.get(Employee_.salary)));
//subquery
Subquery<Double> netAvgSalarySubquery = netAvgSalaryQueryByDept.subquery(Double.class);
Root<Employee> subQueryEmployee = netAvgSalarySubquery.from(Employee.class);
netAvgSalarySubquery.select(criteriaBuilder.avg(subQueryEmployee.get(Employee_.salary)));
//main query with having restriction
netAvgSalaryQueryByDept.having(
criteriaBuilder.greaterThan(criteriaBuilder.avg(employee.get(Employee_.salary)), netAvgSalarySubquery)
);
TypedQuery<Tuple> typedQuery = entityManager.createQuery(netAvgSalaryQueryByDept);
List<Tuple> resultList = typedQuery.getResultList();
resultList.forEach(tuple ->
System.out.printf("Dept: %s, Avg Salary: %s%n", tuple.get(0), tuple.get(1)));
}
public static void persistEmployees() {
Employee employee1 = Employee.create("Diana", "IT", 3000);
Employee employee2 = Employee.create("Rose", "Admin", 2000);
Employee employee3 = Employee.create("Denise", "Admin", 4000);
Employee employee4 = Employee.create("Mike", "IT", 3500);
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} Employee{id=2, name='Rose', dept='Admin', salary=2000} Employee{id=3, name='Denise', dept='Admin', salary=4000} Employee{id=4, name='Mike', dept='IT', salary=3500} Employee{id=5, name='Linda', dept='Sales', salary=2000} -- net average salary -- 2900.0 -- Employees who have less than average salary -- Employee{id=2, name='Rose', dept='Admin', salary=2000} Employee{id=5, name='Linda', dept='Sales', salary=2000} -- Dept by average salaries -- Dept: IT, Avg Salary: 3250.0 Dept: Sales, Avg Salary: 2000.0 Dept: Admin, Avg Salary: 3000.0 -- Dept having AVG salaries greater than net AVG salary -- Dept: IT, Avg Salary: 3250.0 Dept: Admin, Avg Salary: 3000.0
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
|
|