Following example shows different scenarios involving collection in where clause. In JPA entities, the collections can be annotated with @ElementCollection (for simple types) or with @ManyToMany/@OneToMany.
Example Entities
@Entity
public class Employee {
@Id
@GeneratedValue
private Long id;
private String name;
private String dept;
@ElementCollection(fetch = FetchType.EAGER)
private Set<String> phones;
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private List<Task> tasks;
.............
}
@Entity
public class Task {
@Id
@GeneratedValue
private long id;
private String name;
private String supervisor;
.............
}
Now let's see what are the different cases for using collections criteria in where clauses.
Single value condition in WHERE clause
Collection single value condition can be apply by using JPQL inner join and with '=' operator in where clause.
For simple collection:
SELECT e FROM Employee e JOIN e.phones p WHERE p='111-111-111'
For @ManyToMany relation:
SELECT DISTINCT e FROM Employee e JOIN e.tasks t WHERE t.name='Designing'
Multiple values condition in WHERE clause
Multiple values with OR conditions
Multiple values with OR condition can be applied by using 'IN' expression.
For simple collection:
SELECT DISTINCT e FROM Employee e JOIN e.phones p WHERE p IN ('333-333-333', '444-444-444')
For @ManyToMany relation:
SELECT DISTINCT e FROM Employee e JOIN e.tasks t WHERE t.name IN ('Documentation', 'Refactoring')
Multiple values with AND conditions
Multiple value with AND condition can be applied by using 'MEMBER OF' expressions.
For simple collection:
SELECT DISTINCT e FROM Employee e WHERE '333-333-333' MEMBER OF e.phones AND '444-444-444' MEMBER OF e.phones
For @ManyToMany relation:
SELECT e FROM Employee e WHERE :docTask MEMBER OF e.tasks AND :designTask MEMBER OF e.tasks
Complete Example
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEmployees();
findAllEmployees();
findEmployeeByPhone();
findEmployeeByPhones();
findEmployeeByPhones2();
findEmployeeByTask();
findEmployeeByTasks();
findEmployeeByTasks2();
} finally {
entityManagerFactory.close();
}
}
public static void persistEmployees() {
Task task1 = Task.create("Refactoring", "Rose");
Task task2 = Task.create("Designing", "Denise");
Task task3 = Task.create("Documentation", "Jacky");
Employee employee1 = Employee.create("Diana", "IT",
Arrays.asList("111-111-111", "222-222-222"), Arrays.asList(task1, task2));
Employee employee2 = Employee.create("Rose", "Sales",
Arrays.asList("444-444-444", "333-333-333"), Arrays.asList(task3, task1));
Employee employee3 = Employee.create("Denise", "Admin",
Arrays.asList("444-444-444", "555-555-555"), Arrays.asList(task2, task3));
Employee employee4 = Employee.create("Mike", "HR",
Arrays.asList("666-666-666", "777-777-777"), Arrays.asList(task1));
EntityManager em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();
em.persist(employee1);
em.persist(employee2);
em.persist(employee3);
em.persist(employee4);
em.getTransaction().commit();
em.close();
}
private static void findAllEmployees() {
System.out.println("-- All employees --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e FROM Employee e");
List<Employee> employees = query.getResultList();
for (Employee employee : employees) {
System.out.println("-------------------------------------------");
System.out.println("employee: " + employee);
System.out.println("phones: " + employee.getPhones());
System.out.println("tasks: " + employee.getTasks());
}
em.close();
}
private static void findEmployeeByPhone() {
System.out.println("\n-- Employees with phone 111-111-111 --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e FROM Employee e JOIN e.phones p WHERE p='111-111-111'");
List<Employee> employees = query.getResultList();
printEmployeesWithPhones(employees);
em.close();
}
private static void findEmployeeByPhones() {
System.out.println("\n-- Employees with phone 333-333-333 or 444-444-444 --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT DISTINCT e FROM Employee e JOIN e.phones p WHERE p IN "
+ "('333-333-333', '444-444-444')");
List<Employee> employees = query.getResultList();
printEmployeesWithPhones(employees);
em.close();
}
private static void findEmployeeByPhones2() {
System.out.println("\n-- Employees with phones 333-333-333 and 444-444-444 --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT DISTINCT e FROM Employee e WHERE "
+ " '333-333-333' MEMBER OF e.phones AND '444-444-444' MEMBER OF e.phones");
List<Employee> employees = query.getResultList();
printEmployeesWithPhones(employees);
em.close();
}
private static void findEmployeeByTask() {
System.out.println("\n-- Employees with tasks Designing --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT DISTINCT e FROM Employee e JOIN e.tasks t WHERE t.name='Designing'");
List<Employee> employees = query.getResultList();
printEmployeesWithTasks(employees);
em.close();
}
private static void findEmployeeByTasks() {
System.out.println("\n-- Employees with tasks 'Documentation' or 'Designing' --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT DISTINCT e FROM Employee e JOIN e.tasks t WHERE t.name "
+ " IN ('Documentation', 'Designing')");
List<Employee> employees = query.getResultList();
printEmployeesWithTasks(employees);
em.close();
}
private static void findEmployeeByTasks2() {
System.out.println("\n-- Employees with tasks 'Documentation' AND 'Designing' --");
EntityManager em = entityManagerFactory.createEntityManager();
Task documentationTask =
(Task) em.createQuery("SELECT t FROM Task t WHERE t.name= 'Documentation'").getSingleResult();
Task designingTask =
(Task) em.createQuery("SELECT t FROM Task t WHERE t.name= 'Designing'").getSingleResult();
Query query = em.createQuery(
"SELECT e FROM Employee e WHERE "
+ " :docTask MEMBER OF e.tasks AND :designTask MEMBER OF e.tasks");
query.setParameter("docTask", documentationTask);
query.setParameter("designTask", designingTask);
List<Employee> employees = query.getResultList();
printEmployeesWithTasks(employees);
em.close();
}
private static void printEmployeesWithPhones(List<Employee> employees) {
for (Employee employee : employees) {
System.out.println("-------------------------------------------");
System.out.println("employee: " + employee);
System.out.println("phones: " + employee.getPhones());
}
}
private static void printEmployeesWithTasks(List<Employee> employees) {
for (Employee employee : employees) {
System.out.println("-------------------------------------------");
System.out.println("employee: " + employee);
System.out.println("tasks: " + employee.getTasks());
}
}
}-- All employees --
-------------------------------------------
employee: Employee{id=1, name='Diana', dept='IT'}
phones: [111-111-111, 222-222-222]
tasks: [Task{id=2, name='Refactoring', supervisor='Rose'}, Task{id=3, name='Designing', supervisor='Denise'}]
-------------------------------------------
employee: Employee{id=4, name='Rose', dept='Sales'}
phones: [444-444-444, 333-333-333]
tasks: [Task{id=5, name='Documentation', supervisor='Jacky'}, Task{id=2, name='Refactoring', supervisor='Rose'}]
-------------------------------------------
employee: Employee{id=6, name='Denise', dept='Admin'}
phones: [555-555-555, 444-444-444]
tasks: [Task{id=3, name='Designing', supervisor='Denise'}, Task{id=5, name='Documentation', supervisor='Jacky'}]
-------------------------------------------
employee: Employee{id=7, name='Mike', dept='HR'}
phones: [777-777-777, 666-666-666]
tasks: [Task{id=2, name='Refactoring', supervisor='Rose'}]
-- Employees with phone 111-111-111 --
-------------------------------------------
employee: Employee{id=1, name='Diana', dept='IT'}
phones: [111-111-111, 222-222-222]
-- Employees with phone 333-333-333 or 444-444-444 --
-------------------------------------------
employee: Employee{id=6, name='Denise', dept='Admin'}
phones: [555-555-555, 444-444-444]
-------------------------------------------
employee: Employee{id=4, name='Rose', dept='Sales'}
phones: [444-444-444, 333-333-333]
-- Employees with phones 333-333-333 and 444-444-444 --
-------------------------------------------
employee: Employee{id=4, name='Rose', dept='Sales'}
phones: [444-444-444, 333-333-333]
-- Employees with tasks Designing --
-------------------------------------------
employee: Employee{id=1, name='Diana', dept='IT'}
tasks: [Task{id=2, name='Refactoring', supervisor='Rose'}, Task{id=3, name='Designing', supervisor='Denise'}]
-------------------------------------------
employee: Employee{id=6, name='Denise', dept='Admin'}
tasks: [Task{id=3, name='Designing', supervisor='Denise'}, Task{id=5, name='Documentation', supervisor='Jacky'}]
-- Employees with tasks 'Documentation' or 'Designing' --
-------------------------------------------
employee: Employee{id=1, name='Diana', dept='IT'}
tasks: [Task{id=2, name='Refactoring', supervisor='Rose'}, Task{id=3, name='Designing', supervisor='Denise'}]
-------------------------------------------
employee: Employee{id=6, name='Denise', dept='Admin'}
tasks: [Task{id=3, name='Designing', supervisor='Denise'}, Task{id=5, name='Documentation', supervisor='Jacky'}]
-------------------------------------------
employee: Employee{id=4, name='Rose', dept='Sales'}
tasks: [Task{id=5, name='Documentation', supervisor='Jacky'}, Task{id=2, name='Refactoring', supervisor='Rose'}]
-- Employees with tasks 'Documentation' AND 'Designing' --
-------------------------------------------
employee: Employee{id=6, name='Denise', dept='Admin'}
tasks: [Task{id=3, name='Designing', supervisor='Denise'}, Task{id=5, name='Documentation', supervisor='Jacky'}]
Example ProjectDependencies and Technologies Used: - h2 1.4.197: H2 Database Engine.
- hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
Implements javax.persistence:javax.persistence-api version 2.1 - JDK 1.8
- Maven 3.3.9
|
|
