Query for tables with @ManyToMany relation(Hibernate)

Question

I rewrite my SpringMVC app with using Hibernate.I try to make query for selecting lectures for group of students by id of group.with using SQL query(before I started rewrite it with using Hibernate)this query was:

"SELECT * FROM lectures WHERE id IN (SELECT lecture_id FROM lectures_groups WHERE group_id =?) ORDER BY date_of_lecture"

I have Lecture and Group etities:

@Entity
@Table(name = "lectures")
public class Lecture {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "lectures_groups", joinColumns = @JoinColumn(name = "lecture_id"), inverseJoinColumns = @JoinColumn(name = "group_id"))
    private List<Group> groups = new ArrayList<>();
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "teacher_id")
    private Teacher teacher;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "subject_id")
    private Subject subject;
    @Column(name = "date_of_lecture")
    private LocalDateTime date;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "audience")
    private Audience audience;

    public Lecture() {

    }
//getters setters
}

and:

@Entity
@Table(name = "groups")
public class Group {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    @Column(name = "group_name")
    private String name;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "cathedra_id", referencedColumnName = "id")
    private Cathedra cathedra;
    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "lectures_groups", joinColumns = @JoinColumn(name = "group_id"), inverseJoinColumns = @JoinColumn(name = "lecture_id"))
    private List<Lecture> lectures = new ArrayList<>();

    public Group() {

    }
//getters setters
}

I tried somthing like:

List<Lecture> lectures = session.createQuery("select l from lectures l join l.groups g where g.id=:groupId")
                .setParameter("groupId", group.getId())
                .list();

but I get Exception: org.hibernate.hql.internal.ast.QuerySyntaxException: lectures is not mapped So how can i do it?

Answer 1

In hql query you need to provide the name of the entity in the query instead of the table name. So in your case, you should replace lectures with Lecture in the query.

List<Lecture> lectures = session.createQuery("select l from Lecture l join l.groups g where g.id=:groupId")
                .setParameter("groupId", group.getId())
                .list();