Get the correct query to get student courses from many to many spring jpa relation

Question

Can anyone help with the correct JPQL query to get the courses of a student?

@Entity
@Table(name = "students")
public class Student implements Serializable {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

...

@ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.PERSIST)
@JoinTable(name = "students_courses",
        joinColumns = {
                @JoinColumn(name = "student_id", referencedColumnName = "id",
                        nullable = false, updatable = false)},
        inverseJoinColumns = {
                @JoinColumn(name = "course_id", referencedColumnName = "id",
                        nullable = false, updatable = false)})
private Set<Course> courses = new HashSet<>();

...

and

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

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

...

@ManyToMany(mappedBy = "courses", fetch = FetchType.LAZY)
private Set<Student> students = new HashSet<>();

...

In the student repository, I created the following JPQL query :

public interface StudentRepository extends CrudRepository<Student, Long> {

    /*THE QUERY SHOULD GO HERE */
    @Query("")
    List<Course> getStudentCourses(Long studentId);
}

That I call from StudentController:

@RestController
@RequestMapping("/student")
public class StudentController {

    @Autowired
    public StudentRepository studentRepository;

    @GetMapping("/{id}/courses")
    public List<Course> getStudentCourses(@PathVariable("id") Long id){
       return studentRepository.getStudentCourses(id);
    }

I tried many queries but, I'm getting the following postman response:

[
    {
        "id": 1,
        "title": "Machine Learning",
        "abbreviation": "ML",
        "modules": 12,
        "fee": 1500.0,
        "students": [
            {
                "id": 1,
                "name": "John Doe",
                "age": 15,
                "grade": "8th",
                "courses": [
                    {
                        "id": 1,
                        "title": "Machine Learning",
                        "abbreviation": "ML",
                        "modules": 12,
                        "fee": 1500.0,
                        "students": [
                            {
                                "id": 1,
                                "name": "John Doe",
                                "age": 15,
                                "grade": "8th",
                                "courses": [
                                    {
                                      .......

I'm guessing I have the wrong query, any help will be much appreciated.

Answer 1

It could be happening because of the bi directional mapping which results in infinite recursion. You can use @JsonManagedReference and @JsonBackReference with the entities In your case use @JsonManagedReference with the student entity and @JsonBackReference with the course entity which will prevent looping when the result is tried to serialise

The query will be something like this

"select s.courses from Student s join s.courses where s.id = :id "