Just like ON condition of JPQL LEFT JOIN, we can also apply ON condition in Criteria API.
Following are the methods of Join<Z,X> interface which can be used to apply ON condition:
Join<Z, X> on(Expression<Boolean> restriction);
Join<Z, X> on(Predicate... restrictions);
Quick example:
CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
Root<Employee> employee = query.from(Employee.class);
ListJoin<Employee, Task> tasks = employee.join(Employee_.tasks, JoinType.LEFT);
tasks.on(criteriaBuilder.equal(tasks.get(Task_.supervisor), "Denise"));
....
Example
Entities
@Entity
public class Employee {
@Id
@GeneratedValue
private long id;
private String name;
@ManyToMany(cascade = CascadeType.ALL)
private List<Task> tasks;
.............
}
@Entity
public class Task {
@Id
@GeneratedValue
private long id;
private String description;
private String supervisor;
.............
}
Queries involving Join ON condition
First, let's not use 'ON' condition to understand the difference:
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEmployees();
findEmployeesBySupervisor();
} finally {
entityManagerFactory.close();
}
}
public static void persistEmployees() {
Task task1 = new Task("Coding", "Denise");
Task task2 = new Task("Refactoring", "Rose");
Task task3 = new Task("Designing", "Denise");
Task task4 = new Task("Documentation", "Mike");
Employee employee1 = Employee.create("Diana", task1, task3);
Employee employee2 = Employee.create("Mike", task2, task4);
Employee employee3 = Employee.create("Tim", task3, task4);
Employee employee4 = Employee.create("Jack");
EntityManager em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();
em.persist(employee1);
em.persist(employee2);
em.persist(employee3);
em.persist(employee4);
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);
}
private static void findEmployeesBySupervisor() {
System.out.println("-- find employees left joined with tasks --");
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
Root<Employee> employee = query.from(Employee.class);
ListJoin<Employee, Task> tasks = employee.join(Employee_.tasks, JoinType.LEFT);
query.select(criteriaBuilder.tuple(employee.get(Employee_.name).alias("employeeName"),
tasks.get(Task_.supervisor).alias("supervisor")))
.distinct(true);
TypedQuery<Tuple> typedQuery = entityManager.createQuery(query);
for (Tuple tuple : typedQuery.getResultList()) {
System.out.printf("name: %s, supervisor: %s%n",
tuple.get("employeeName", String.class),
tuple.get("supervisor", String.class));
}
}
} -- Employee persisted -- Employee{id=1, name='Diana', tasks=[Task{id=2, description='Coding', supervisor='Denise'}, Task{id=3, description='Designing', supervisor='Denise'}]} Employee{id=4, name='Mike', tasks=[Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]} Employee{id=7, name='Tim', tasks=[Task{id=3, description='Designing', supervisor='Denise'}, Task{id=6, description='Documentation', supervisor='Mike'}]} Employee{id=8, name='Jack', tasks=[]} -- find employees left joined with tasks -- name: Diana, supervisor: Denise name: Mike, supervisor: Mike name: Mike, supervisor: Rose name: Jack, supervisor: null name: Tim, supervisor: Mike name: Tim, supervisor: Denise
The query returned all employee names and supervisor names even there are no tasks (because of LEFT JOIN) and so the corresponding supervisor does not exit.
Now let's apply an 'ON' condition. This time we will filter the results using ON condition so that the results will only show supervisor = 'Denise'
public class ExampleMain2 {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEmployees();
findEmployeesBySupervisor();
} finally {
entityManagerFactory.close();
}
}
.............
private static void findEmployeesBySupervisor() {
System.out.println("-- find employees left joined with tasks with supervisor 'Denise' --");
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
Root<Employee> employee = query.from(Employee.class);
ListJoin<Employee, Task> tasks = employee.join(Employee_.tasks, JoinType.LEFT);
tasks.on(criteriaBuilder.equal(tasks.get(Task_.supervisor), "Denise"));
query.select(criteriaBuilder.tuple(employee.get(Employee_.name).alias("employeeName"),
tasks.get(Task_.supervisor).alias("supervisor")))
.distinct(true);
TypedQuery<Tuple> typedQuery = entityManager.createQuery(query);
for (Tuple tuple : typedQuery.getResultList()) {
System.out.printf("name: %s, supervisor: %s%n",
tuple.get("employeeName", String.class),
tuple.get("supervisor", String.class));
}
}
} -- Employee persisted -- Employee{id=1, name='Diana', tasks=[Task{id=2, description='Coding', supervisor='Denise'}, Task{id=3, description='Designing', supervisor='Denise'}]} Employee{id=4, name='Mike', tasks=[Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]} Employee{id=7, name='Tim', tasks=[Task{id=3, description='Designing', supervisor='Denise'}, Task{id=6, description='Documentation', supervisor='Mike'}]} Employee{id=8, name='Jack', tasks=[]} -- find employees left joined with tasks with supervisor 'Denise' -- name: Diana, supervisor: Denise name: Jack, supervisor: null name: Mike, supervisor: null name: Tim, supervisor: Denise
Example ProjectDependencies and Technologies Used: - h2 1.4.197: H2 Database Engine.
- hibernate-core 5.3.5.Final: Hibernate's core ORM functionality.
Implements javax.persistence:javax.persistence-api version 2.2 - hibernate-jpamodelgen 5.3.5.Final: Annotation Processor to generate JPA 2 static metamodel classes.
- JDK 1.8
- Maven 3.5.4
|
|