Hibernate join 3 tables

Question

I have 3 tables: Student, Course and Student_Course. Every student may have a few courses and every course may have a few student. (Many-To-Many). I need to write a method that will get all students of a course. In SQL it's 2 inner joins. I tried this:

Criteria criteria = session.createCriteria(Student.class, "s");
        criteria.createAlias("student_course", "s_c");
        criteria.createAlias("course", "c");
        criteria.add(Restrictions.eq("s.student_id", "s_c.student_id"));
        criteria.add(Restrictions.eq("s_c.course_id", "c.course_id"));
        criteria.add(Restrictions.eq("c.course_id", course.getId()));
        courses = criteria.list();

But I get a org.hibernate.QueryException: could not resolve property: student_course of: com.example.entity.Student

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

@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "STUDENT_ID", unique = true, nullable = false)
private long id;

@ManyToMany(targetEntity = Course.class, fetch = FetchType.EAGER)
@JoinTable(name = "STUDENT_COURSE", joinColumns = { @JoinColumn(name = "student_id") }, inverseJoinColumns = { @JoinColumn(name = "course_id") })
private Set<Course> courses = new HashSet<Course>();

and:

@Entity
@Table(name = "COURSE")
public class Course{
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "COURSE_ID", unique = true, nullable = false)
private long id;

How to write right code?

Answer 1

Can you ensure you have the correct hibernate mapping in this case? If correctly defined you don't need to use student_course mapping table in query.

Your entity classes should look like

class Student {

...
.
..
private Set<Course> courses= new HashSet<Course>(0);


}

class Course{

private Set<Student > categories = new HashSet<Student >(0);
}

Hibernate mapping Should have (For Course Entity it should have students set)

 <set name="courses" table="student_course" 
            inverse="false" lazy="true" fetch="select" cascade="all" >
            <key>
                <column name="STUDENT_ID" not-null="true" />
            </key>
            <many-to-many entity-name="com....Course">
                <column name="COURSE_ID" not-null="true" />
            </many-to-many>
        </set>

Then you retrieve the course and use set of students

session = HibernateUtil.getSessionFactory().openSession();
        course= (Course)session.load(Course.class, user_id);
        course.getStudents();

/Mukesh