errror in converting SQL query to HQL, Java Hibernate

Question

I have a Spring MVC + JDBC application. I have lectures and groups tables in a database of a university and GroupDao with LectureDao classes. Now I'm trying to replace JDBC with Hibernate. I had an SQL query

private static final String GET_ALL_GROUPS_FOR_LECTURE = "SELECT * FROM GROUPS " +
            "INNER JOIN LECTUREGROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID " +
            "WHERE LECTUREID = ?";

And now I've converted it into HQL like this

private static final String GET_ALL_GROUPS_FOR_LECTURE = "FROM Group " +
            "JOIN LectureGroup ON Group.id = LectureGroup.groupId " +
            "WHERE LectureGroup.lectureId = :lectureId";

And I have method what uses this query

public List<Group> getGroupsOnLecture(int lectureId) {
    try (Session session = sessionFactory.openSession()) {
        Transaction transaction = session.beginTransaction();
        Query<Group> query = session.createQuery(GET_ALL_GROUPS_FOR_LECTURE, Group.class);
        query.setParameter("lectureId", lectureId);
        List<Group> groupsOnLecture = query.list();
        transaction.commit();
        return groupsOnLecture;
    }
}

I get an exception when I run my application. The mistake is shown in this raw with queryCreation. So the mistake in HQL syntax. What am I doing wrong?

Here is Lecture and Group and LectureGroup Entites

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

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;
    @Column(name = "name")
    private String name;
    @Column(name = "streamId")
    private int streamId;

    public Group(String name, int streamId) {
        this.name = name;
        this.streamId = streamId;
    }

    public Group() {
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getId() {
        return id;
    }

    public String getStringId() {
        return String.valueOf(id);
    }

    public int getStreamId() {
        return streamId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setStreamId(int streamId) {
        this.streamId = streamId;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Group group = (Group) o;
        return id == group.id &&
                streamId == group.streamId &&
                name.equals(group.name);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, name, streamId);
    }

    @Override
    public String toString() {
        return "Group{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", streamId=" + streamId +
                '}';
    }

}

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

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;
    @Column(name = "subjectId")
    private int subjectId;
    @Column(name = "professorId")
    private int professorId;
    @Column(name = "date")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private LocalDate date;
    @Column(name = "time")
    @DateTimeFormat(pattern = "HH:mm")
    private LocalTime time;
    @Column(name = "classroomId")
    private int classroomId;

    public Lecture(int subjectId, int professorId, LocalDate date, LocalTime time, int classroomId) {
        this.subjectId = subjectId;
        this.professorId = professorId;
        this.date = date;
        this.time = time;
        this.classroomId = classroomId;
    }

    public Lecture() {
    }

    public int getId() {
        return id;
    }

    public int getSubjectId() {
        return subjectId;
    }

    public int getProfessorId() {
        return professorId;
    }

    public LocalDate getDate() {
        return date;
    }

    public String getDateString() {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-uuuu");
        return date.format(formatter);
    }

    public LocalDate setDateFromString(String dateString) {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-uuuu");
        return LocalDate.parse(dateString, formatter);
    }

    public LocalTime getTime() {
        return time;
    }

    public int getClassroomId() {
        return classroomId;
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setSubjectId(int subjectId) {
        this.subjectId = subjectId;
    }

    public void setProfessorId(int professorId) {
        this.professorId = professorId;
    }

    public void setDate(LocalDate date) {
        this.date = date;
    }

    public void setTime(LocalTime time) {
        this.time = time;
    }

    public void setClassroomId(int classroomId) {
        this.classroomId = classroomId;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Lecture lecture = (Lecture) o;
        return id == lecture.id &&
                subjectId == lecture.subjectId &&
                professorId == lecture.professorId &&
                classroomId == lecture.classroomId &&
                date.equals(lecture.date) &&
                time.equals(lecture.time);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, subjectId, professorId, date, time, classroomId);
    }

    @Override
    public String toString() {
        return "Lecture{" +
                "id=" + id +
                ", subjectId=" + subjectId +
                ", professorId=" + professorId +
                ", date=" + date +
                ", time=" + time +
                ", classroomId=" + classroomId +
                '}';
    }

}

@Entity
@Table(name = "lecturegroups")
public class LectureGroup {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;
    @Column(name = "lectureId")
    private int lectureId;
    @Column(name = "groupId")
    private int groupId;

    public LectureGroup(int lectureId, int groupId) {
        this.lectureId = lectureId;
        this.groupId = groupId;
    }

    public LectureGroup() {
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setLectureId(int lectureId) {
        this.lectureId = lectureId;
    }

    public void setGroupId(int groupId) {
        this.groupId = groupId;
    }

    public int getId() {
        return id;
    }

    public int getLectureId() {
        return lectureId;
    }

    public int getGroupId() {
        return groupId;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        LectureGroup that = (LectureGroup) o;
        return id == that.id &&
                lectureId == that.lectureId &&
                groupId == that.groupId;
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, lectureId, groupId);
    }

    @Override
    public String toString() {
        return "LectureGroup{" +
                "id=" + id +
                ", lectureId=" + lectureId +
                ", groupId=" + groupId +
                '}';
    }
}

Answer 1

Try changing to below. The reason is you can only join (Group join LectureGroup ) if there is a relation present where as your Classes are not mapped with relation like ManyToOne. You have to use syntax like below or you can change your Classes with proper relation mappings to use the syntax you are trying.

private static final String GET_ALL_GROUPS_FOR_LECTURE = "select distinct g FROM Group g, LectureGroup lg WHERE g.id = lg.groupId and lg.lectureId = :lectureId";