[SOLVED] How we query one to many relation in Spring JPA using Criteria Query

How we query one to many relation in Spring JPA using Criteria Query

The scenario is: Entity Student ID Name List Courses

Entity Course ID Name Student

Now I need the list of students who are studying course 'Programming'

How can I achieve this using Criteria Query.

Solution

try this:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Student> cq = cb.createQuery(Student.class);

Root<Student> rootStudent = cq.from(Student.class);
Join<Student,Course> joinCourse = rootStudent.join("courses",JoinType.INNER);

cq.where(cb.equals(joinCourse.get("Name"),"Proggraming"));
cq.select(rootStudent);

List<Student> res = entityManager.createQuery(cq).getResultList();

In this example I assume that you have the JPA entities well modeled, including bidirectional relationships. It would also be recommended if it is for an API that you use pojos instead of returning a JPA entity to the web part, for this you should use multiselect instead of select and specify each of the fields you want to obtain, but as a first approximation it would be valid.

It would also be advisable to use metamodels for JPA entities instead of accessing the properties through a string with the name (join, get methods ..)

In this scenario, it does not make sense to make a subquery, I change the query to find the students of the courses Proggraming1 or Proggraming2 through a subquery(I would still prefer to do it by filtering the joins without using subquery), it would be something like this:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Student> cq = cb.createQuery(Student.class);

Root<Student> rootStudent = cq.from(Student.class);
Join<Student,Course> joinCourse = rootStudent.join("courses",JoinType.INNER);

Subquery<Long> subqueryIdCourse = cq.subquery(Long.class);
Root<Course> rootCourseSq = subqueryIdCourse.from(Course.class);
subqueryIdCourse.where(
    cb.or(
        cb.equals(rootCourseSq.get("Name"),"Proggraming1"),
        cb.equals(rootCourseSq.get("Name"),"Proggraming2")))
subqueryIdCourse.select(rootCourseSq.get("ID"))

cq.where(joinCourse.get("ID").in(subqueryIdCourse.getSelection()));
cq.select(rootStudent);

List<Student> res = entityManager.createQuery(cq).getResultList();