I have a web application that stores information about students, teachers, courses and grades. I have run into a problem while adding/editing students grades. For some reason the built in findById() method by the JPA Repository is returning a course with double the students (even though there is clearly only one student in that class in the database). I have attached images of my models and method also my attempt at debugging the problem.
Here is where the method is being called, my listStudentsByCourse method just returns a list of students:
Here is the listStudentsByCourse methods body:
Here are my models:
Student:
@NoArgsConstructor
@Getter
@Setter
@Entity
public class Student {
@Id
private String username;
private String password;
private String name;
private String surname;
@OneToMany(mappedBy = "student", fetch = FetchType.EAGER)
private List<Grade> grades;
public Student(String username, String password, String name, String surname) {
this.username = username;
this.password = password;
this.name = name;
this.surname = surname;
this.grades = new ArrayList<>();
}
public Character getGradeForCourse (Course c) {
return this.grades.stream()
.filter(i -> i.getCourse().getCourseId().equals(c.getCourseId()))
.findFirst().orElse(new Grade()).getGrade();
}
}
Course:
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@Entity
public class Course {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long courseId;
private String name;
private String description;
@ManyToOne
private Teacher teacher;
@ManyToMany(fetch = FetchType.EAGER)
private List<Student> students;
@Enumerated(value = EnumType.STRING)
private Type type;
public Course(String name, String description, Teacher teacher, Type type) {
this.name = name;
this.description = description;
this.teacher = teacher;
this.type = type;
this.students = new ArrayList<>();
}
}
Grade:
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@Entity
public class Grade {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private Character grade;
@ManyToOne
private Course course;
@ManyToOne
private Student student;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME)
private LocalDateTime timestamp;
public Grade(Character grade, Course course, Student student, LocalDateTime timestamp) {
this.grade = grade;
this.course = course;
this.student = student;
this.timestamp = timestamp;
}
}
My database schema:
Image of the problem:
This is the course that is returned by courseRepository.findById()
I have the same student added in one other course and I wonder if they are getting mixed up somehow, but that shouldn't be possible because the course is different (and so is the ID).
This problem is really giving me a hard time and I really don't understand how this is happening. Any info would be helpful, thanks!
Edit 1: I read all of the comments and added them to my code, but the problem persists. I have now noticed that the data doubles when I have one student with a grade already in the course, and if I add another new student without a grade the data doubles not only on the front-end but also in the database.
Here is how I add new students to an existing course, is there anything wrong in the code?:
@Override
public Course addStudentInCourse(String username, Long courseId) {
if (this.studentRepository.findByUsername(username).isEmpty()) {
throw new StudentNotFoundException();
}
if (this.courseRepository.findById(courseId).isEmpty()) {
throw new CourseNotFoundException();
}
Student s = this.studentRepository.findByUsername(username).get();
Course c = this.courseRepository.findById(courseId).get();
if (!c.getStudents().contains(s)){
c.getStudents().add(s);
return this.courseRepository.save(c);
} else {
return c;
}
}
Edit 2: I have come to a breakthrough, sort of.
First of all, here is a picture of my course_student table:
I realized that courseService.findById(courseId), if I have the same student in different courses, it retrieves that student as well (when it shouldn't). For example I have markos in course 5 and course 7.
If I use the following query method to return a list of the students in a certain course, it retrieves the students correctly.
studentRepository.findAllByCourses(c);
Here is a picture from my debugging:
I should probably note that the courses for a Student are Lazy Loaded.
Why is the query method findById returning a Course with students that are not related to that course?
Edit 3: All of the tables and code is the same from edit number 2 (I just kept refreshing the page where I fetch the data). I logged the SQL as many of you suggested. The following SQL queries are from the courseService.findById(courseId) method. I understand the first part, it fetches the course with the correct id (7) from the Course table. Then it selects a student from the course_student table where the id is 5. Where did this 5 come from?
Hibernate:
select
course0_.course_id as course_i1_0_0_,
course0_.description as descript2_0_0_,
course0_.name as name3_0_0_,
course0_.teacher_id as teacher_5_0_0_,
course0_.type as type4_0_0_,
students1_.fk_course as fk_cours1_1_1_,
student2_.username as fk_stude2_1_1_,
student2_.username as username1_3_2_,
student2_.name as name2_3_2_,
student2_.password as password3_3_2_,
student2_.surname as surname4_3_2_,
grades3_.fk_student as fk_stude5_2_3_,
grades3_.id as id1_2_3_,
grades3_.id as id1_2_4_,
grades3_.fk_course as fk_cours4_2_4_,
grades3_.grade as grade2_2_4_,
grades3_.fk_student as fk_stude5_2_4_,
grades3_.timestamp as timestam3_2_4_,
course4_.course_id as course_i1_0_5_,
course4_.description as descript2_0_5_,
course4_.name as name3_0_5_,
course4_.teacher_id as teacher_5_0_5_,
course4_.type as type4_0_5_,
teacher5_.id as id1_4_6_,
teacher5_.date_of_employment as date_of_2_4_6_,
teacher5_.name as name3_4_6_,
teacher5_.surname as surname4_4_6_
from
course course0_
left outer join
course_student students1_
on course0_.course_id=students1_.fk_course
left outer join
student student2_
on students1_.fk_student=student2_.username
left outer join
grade grades3_
on student2_.username=grades3_.fk_student
left outer join
course course4_
on grades3_.fk_course=course4_.course_id
left outer join
teacher teacher5_
on course4_.teacher_id=teacher5_.id
where
course0_.course_id=?
2022-01-06 00:56:38.869 TRACE 15804 --- [nio-8080-exec-4] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BIGINT] - [7]
Hibernate:
select
students0_.fk_course as fk_cours1_1_0_,
students0_.fk_student as fk_stude2_1_0_,
student1_.username as username1_3_1_,
student1_.name as name2_3_1_,
student1_.password as password3_3_1_,
student1_.surname as surname4_3_1_
from
course_student students0_
inner join
student student1_
on students0_.fk_student=student1_.username
where
students0_.fk_course=?
2022-01-06 00:56:38.881 TRACE 15804 --- [nio-8080-exec-4] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BIGINT] - [5]
Hibernate:
select
course0_.course_id as course_i1_0_0_,
course0_.description as descript2_0_0_,
course0_.name as name3_0_0_,
course0_.teacher_id as teacher_5_0_0_,
course0_.type as type4_0_0_,
students1_.fk_course as fk_cours1_1_1_,
student2_.username as fk_stude2_1_1_,
student2_.username as username1_3_2_,
student2_.name as name2_3_2_,
student2_.password as password3_3_2_,
student2_.surname as surname4_3_2_,
grades3_.fk_student as fk_stude5_2_3_,
grades3_.id as id1_2_3_,
grades3_.id as id1_2_4_,
grades3_.fk_course as fk_cours4_2_4_,
grades3_.grade as grade2_2_4_,
grades3_.fk_student as fk_stude5_2_4_,
grades3_.timestamp as timestam3_2_4_,
course4_.course_id as course_i1_0_5_,
course4_.description as descript2_0_5_,
course4_.name as name3_0_5_,
course4_.teacher_id as teacher_5_0_5_,
course4_.type as type4_0_5_,
teacher5_.id as id1_4_6_,
teacher5_.date_of_employment as date_of_2_4_6_,
teacher5_.name as name3_4_6_,
teacher5_.surname as surname4_4_6_
from
course course0_
left outer join
course_student students1_
on course0_.course_id=students1_.fk_course
left outer join
student student2_
on students1_.fk_student=student2_.username
left outer join
grade grades3_
on student2_.username=grades3_.fk_student
left outer join
course course4_
on grades3_.fk_course=course4_.course_id
left outer join
teacher teacher5_
on course4_.teacher_id=teacher5_.id
where
course0_.course_id=?
2022-01-06 00:56:38.893 TRACE 15804 --- [nio-8080-exec-4] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BIGINT] - [7]
Hibernate:
select
students0_.fk_course as fk_cours1_1_0_,
students0_.fk_student as fk_stude2_1_0_,
student1_.username as username1_3_1_,
student1_.name as name2_3_1_,
student1_.password as password3_3_1_,
student1_.surname as surname4_3_1_
from
course_student students0_
inner join
student student1_
on students0_.fk_student=student1_.username
where
students0_.fk_course=?
2022-01-06 00:56:38.909 TRACE 15804 --- [nio-8080-exec-4] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BIGINT] - [5]
CodePudding user response：
Your entity Course relationship with entity Student is Many-to-Many. That means rows of Course that have same id can hold many Students. I suspected that when you call findById() the generated SQL doesn't distinctively select the student given the courseId.
There's a catch in your schemas, which is the course_students that is not yet implemented in your code. This is a try suggestion, add @JoinTable(...) in your Course entity:
@Entity
public class Course {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long course_id;
.
.
// your course fields
.
.
/*** Here you add the many to many table ***/
@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(name = "course_students",
joinColumns = {
@JoinColumn(name = "course_course_id", referencedColumnName = "course_id")},
inverseJoinColumns = {
@JoinColumn(name = "students_username", referencedColumnName = "username")})
private List<Student> students;
... //more fields
}
And then in your Student entity, specify your mapped attribute from the join table:
@Entity
public class Student {
@Id
private String username;
.. // student fields
@ManyToMany(mappedBy = "students")
List<Course> courses;
.. //more..
}
CodePudding user response：
Thank you all for your suggestions, I managed to figure it out.
It was the way I mapped my relationships all along. When I mapped the courses in my Grade entity as a ManyToOne, I had forgotten to map the grades in my Course entity with a OneToMany annotation. This resulted in Hibernate getting confused and fetching the same student for the course from two tables.
This is a lesson to always map your relationships correctly and don't lean on Hibernate to "figure it out".