Hibernate Mapping Through Another Entity

Question

Consider this simple database schema:

 User                Course              StudentCourse       Student
+-------------+     +-------------+     +-------------+     +-------------+
| -user_id    |1   *| -course_id  |1   *| -course_id  |*   1| -student_id |
|             |---->| -user_id    |---->| -student_id |---->|             |
+-------------+     +-------------+     +-------------+     +-------------+

[1   *] = 1 to many relationship 

I have created entities for the User, Course, and Student objects, and set up the following mappings:

User   -> Course  - one to many
Course -> Student - many to many

In my Java classes I can access the Courses of a User by calling user.getCourses() and I can access all the Students in a Course by calling course.getStudents(). I want to be able to find all of the Students in all of the Courses taught by a specific User like user.getCourse().getStudents(), but because user.getCourses() returns Collection<Course> I cannot call course.getStudents() on the Collection. How should I implement this with Hibernate? Is a named query my only option?

Answer 1

i guess you have to define fetchType = EAGER in Course, which is actually not a good idea so HQL would be best and efficient.

Answer 2

You could do the following:

List<Student> students = new ArrayList<Student>();
for ( Course course : user.getCourses() )
{
    students.addAll( course.getStudents() );
}

However this would be very inefficient (see the "SELECT N+1" problem)

I haven't had a chance to test it but your named query should just be something like: "SELECT c.students FROM Course c WHERE c.user.name = 'username'"

Answer 3

You said

I want to be able to find all of the Students in all of the Courses taught by a specific User

Use HQL query

SELECT DISTINCT _student FROM User AS _user, IN ( _user.courses ) _course, IN( _course.students ) _student WHERE _user.id = :id

or

SELECT DISTINCT _student FROM User _user inner join _user.courses _course inner join _course.students _student WHERE _user.id = :id

regards,