JPA Criteria Queries allow us to create complex queries in a object-oriented manner. In this post, I’ll provide an example where we create a query that filters an entity that has a many-to-many relationship with another entity.
The Example
In this example, we’ll be using Hibernate as our JPA implementation. We will have two entities, Student and Course. To keep things simple, both the entities will just have a name and id field. Additionally, Students and Courses will share a many-to-many relationship. A Student can be taking multiple Courses and a Course can have multiple Students.
Below is our implementation of the Student and Course entities.
@Entity
public class Student {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
@Column(name="student_name")
private String studentName;
@ManyToMany
@JoinTable(name = "student_course", joinColumns = @JoinColumn(name = "student_id"),
inverseJoinColumns = @JoinColumn(name = "course_id"))
private Set<Course> courses;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public Set<Course> getCourses() {
return courses;
}
public void setCourses(Set<Course> courses) {
this.courses = courses;
}
@Override
public String toString() {
return "Student [id=" + id + ", studentName=" + studentName + "]";
}
}@Entity
public class Course {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
@Column(name="course_name")
private String courseName;
@JsonIgnore
@ManyToMany(mappedBy = "courses")
private Set<Student> students;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getCourseName() {
return courseName;
}
public void setCourseName(String courseName) {
this.courseName = courseName;
}
public Set<Student> getStudents() {
return students;
}
public void setStudents(Set<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Course [id=" + id + ", courseName=" + courseName + "]";
}
}The resulting database will look like the following. Students and Courses are linked by a Join Table that maps the a Student ID with a Course ID.
The Criteria Query
In this example, we’ll be creating a findByCourseNames method that returns students that are taking all the courses in the coursesNames argument list. To create our query, the first thing we need to do is initialize the CriteriaBuilder and CriteriaQuery. As we’re querying for students, the query root will be using the Student entity class.
The predicates list will contain all the criteria that will be used to create our query. We will be adding criteria to this list to ensure our query only returns Student results taking the required Courses.
To do this, we will need to iterate through the courseNames list. For each course name in the list, a subquery will be created that selects all student IDs for students that are taking that course.
The next step is to add criteria to our predicates list. For each subquery, we’ll add a predicate that checks that the student is one of the students selected by the subquery. The final result after adding predicates for each course is that we ensure we only find students that are present in each subquery, hence fulfilling the requirement of taking every course in the argument list.
Below is the implementation of the findByCourseNames method.
public List<Student> findByCourseNames(List<String> courseNames) {
// Initialize the query
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Student> cq = cb.createQuery(Student.class);
Root<Student> student = cq.from(Student.class);
List<Predicate> predicates = new ArrayList<>();
// Iterate over each courseName we want to match with
for(String courseName : courseNames) {
// Initialize the subquery
Subquery<Long> subquery = cq.subquery(Long.class);
Root<Student> subqueryStudent = subquery.from(Student.class);
Join<Course, Student> subqueryCourse = subqueryStudent.join("courses");
// Select the Student ID where one of their courses matches
subquery.select(subqueryStudent.get("id")).where(
cb.equal(subqueryCourse.get("courseName"), courseName));
// Filter by Students that match one of the Students found in the subquery
predicates.add(cb.in(student.get("id")).value(subquery));
}
// Use all predicates above to query
cq.where(cb.and(predicates.toArray(new Predicate[predicates.size()])));
TypedQuery<Student> query = entityManager.createQuery(cq);
return query.getResultList();
}Below is the SQL generated by the Criteria Query when calling the findByCourseNames method with two courseNames. As expected, this will create two subqueries, one for each course. The main query will then find students that match the IDs found by the subqueries.
select
student0_.id as id1_1_,
student0_.student_name as student_2_1_
from
student student0_
where
(
student0_.id in (
select
student1_.id
from
student student1_
inner join
student_course courses2_
on student1_.id=courses2_.student_id
inner join
course course3_
on courses2_.course_id=course3_.id
where
course3_.course_name=?
)
)
and (
student0_.id in (
select
student4_.id
from
student student4_
inner join
student_course courses5_
on student4_.id=courses5_.student_id
inner join
course course6_
on courses5_.course_id=course6_.id
where
course6_.course_name=?
)
)Example with Data
Let’s populate our tables with some data and verify that the query works as expected.
| id | student_name |
|---|---|
| 1 | Bob |
| 10 | Steve |
| 11 | Amy |
| id | course_name |
|---|---|
| 2 | Math |
| 3 | Chemistry |
| 4 | Physics |
| 5 | Biology |
| 6 | English |
| 7 | French |
| 8 | Computer Science |
| 9 | History |
| student_id | course_id |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 10 | 2 |
| 10 | 9 |
| 11 | 4 |
| 11 | 5 |
| 11 | 8 |
And the output of calling findByCourseNames with different inputs:
Input: ['Math']
Output:
[
{
"id": 10,
"studentName": "Steve",
"courses": [
{
"id": 2,
"courseName": "Math"
},
{
"id": 9,
"courseName": "History"
}
]
},
{
"id": 1,
"studentName": "Bob",
"courses": [
{
"id": 2,
"courseName": "Math"
},
{
"id": 5,
"courseName": "Biology"
},
{
"id": 4,
"courseName": "Physics"
},
{
"id": 3,
"courseName": "Chemistry"
}
]
}
]Input: ['Biology', 'Physics']
Output:
[
{
"id": 1,
"studentName": "Bob",
"courses": [
{
"id": 2,
"courseName": "Math"
},
{
"id": 5,
"courseName": "Biology"
},
{
"id": 4,
"courseName": "Physics"
},
{
"id": 3,
"courseName": "Chemistry"
}
]
},
{
"id": 11,
"studentName": "Amy",
"courses": [
{
"id": 5,
"courseName": "Biology"
},
{
"id": 4,
"courseName": "Physics"
},
{
"id": 8,
"courseName": "Computer Science"
}
]
}
]Input: ['Computer Science', 'Chemistry']
Output:
// no results
[]