How to Inner Join using Spring Boot JPA / Hibernate in Repository?

Question

I am currently learning spring boot , hibernate and Spring Boot JPA

I developing a Classroom App for coaching centers and institutes .

In it, students enrolled to multiple courses in a single institute

The Student Entity class :

@Entity
@Table(name = "student")
public class Student  {

    private String name;

    private String dob;

    private String gender;

    private String address;

    private String email;

    private Integer mobile;

    private String joined;

    private Integer instID;

    @Id
    private String studentid;

    getters and setters()....
}

Course Table Entity class

@Entity
@Table(name = "courses")
public class Course {

    private String name;

    private String description;

    private String logo;

    private String start;

    private String end;

    private Integer fee;
    @Id
    private String courseid;


    private Integer instID;

    getters and setters();
}

Enrolled Classes Table's Entity class

public class EnrolledCourses {

    @Id
    String enrollID;

    String courseid;
    String studentid;

    Date joined;

    getters  and setters()...

}

JPA Repository

 @Repository
    public interface StudentRepository extends CrudRepository<Student, String> {
        Student findTopByInstIDOrderByStudentidDesc(int instID);
    }

@Repository
public interface CourseRepository extends CrudRepository<Course,String> {
}

@Repository
public interface EnrolledRepository extends CrudRepository<Course,String> {
}

My Need

Now I am retrieving enrolled students for a given course in a given institute... by using this MySQL query

SELECT 
`enrolled_courses.enrollID`,
`student.name`, `student.studentid`
FROM `enrolled_courses`
INNER JOIN `student`
WHERE
`enrolled_courses.studentid` = `student.studentid`
AND
`student.instID` = 13
AND
`enrolled_courses.courseid` = '13I01C' ;

Now I need to implement this Inner join query in CourseRepository (or enrolledstudent repo)

How to achieve this ?

Please guide me

Nayan Nayan · Accepted Answer · 2020-06-18T18:03:30

If we use hibernate mapping in EnrolledCourses entity like

@Entity
public class EnrolledCourses {

    @Id
    String enrolledId;

    @ManyToOne
    Student student;

    @ManyToOne
    Course  course;

    Date joined;

    getters  and setters()...

}

from the above mappings without using any SQL queries, you can retrieve all student who comes under a particular course By using the method in the interface

@Repository
 public interface EnrolledRepository extends CrudRepository<EnrolledCourses,String> {
     List<EnrolledCourses> findByCourse_CourseId(String courseId);
 }

if we have some relations between the entities we can easily retrieve all the fields using Jpa.

How to Inner Join using Spring Boot JPA / Hibernate in Repository?

1 Answers