Andrea Marcolin Andrea Marcolin - 3 months ago 18
Java Question

Hibernate query on many to many relationship with extra column

I am new to Hibernate and I am trying to figure out how to query a many to many relationship mapped as an entity due to the need of an extra column.

In particular, following the example I found at codejava.net (http://www.codejava.net/frameworks/hibernate/hibernate-many-to-many-association-with-extra-columns-in-join-table-example) I mapped the relation like this:

Student.java

@Entity
public class Student implements Serializable {

@Id
@Column
private String email;

@Column(nullable = false)
private String password;

@Column(nullable = false)
private String name;

@Column(nullable = false)
private String surname;

// Constructor, getters, setters, hashcode, equals
}


Course.java

@Entity
@Table(
uniqueConstraints = @UniqueConstraint(
columnNames {"name","year"})
)
public class Course implements Serializable {

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

@Column
private String name;

@Column
private String year;

@OneToMany(mappedBy = "course")
private Set<Student_Course> students = new LinkedHashSet<>();

// Constructor, getters, setters, hashcode, equals
}


Student_Course.java

@Entity
public class Student_Course implements Serializable {

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

@ManyToOne
@JoinColumn(name = "student_email")
private Student student;

@ManyToOne
@JoinColumn(name = "course_id")
private Course course;

@Column(nullable = false,
columnDefinition = "int default 0")
private int score;

// Constructor, getters, setters, hashcode, equals
}


Now what I want to achieve is to find out, with an hql query, the names and surnames of students enrolled in a given course (I know the name and the year of the course).
I know this is probably easy, but I can't produce a working query in HQL.

Thank you in advance.

Answer
SELECT sc.student.name, sc.student.surname
FROM Course c JOIN c.students sc 
WHERE c.name = :name AND c.year = :year
Comments