How to convert MySQL query into HQL query?

Question

I am new to HQL. This is a mysql Query. I need to convert it into HQL query. How to do that any suggestions please?

    `SELECT STUDENT.ID, STUDENT.NAME, STUDENT.GRADE_ID, STUDENT.CLASS, GRADE.NAME FROM
     STUDENT INNER JOIN GRADE ON STUDENT.GRADE_ID = GRADE.GRADE_ID`

STUDENT[ID, NAME, GRADE_ID, CLASS]

GRADE[GRADE_ID, GRADE_NAME]

The resultset of the above query will be something like ID, NAME, GRADE_NAME, CLASS.

---

Final Update:

I have 2 tables STUDENT[ID, NAME, GRADE_ID, CLASS] GRADE[GRADE_ID, GRADE_NAME]

With the SQL query SELECT STUDENT.ID, STUDENT.NAME, STUDENT.GRADE_ID, STUDENT.CLASS, GRADE.NAME FROM STUDENT INNER JOIN GRADE ON STUDENT.GRADE_ID = GRADE.GRADE_ID I used to show a New table STUDENT[ID, NAME, GRADE_NAME, CLASS] was working in SQL. For this function there is no input, return type is a list(all the records of the result table)

This is what i want to do in HQL or JPQL, How to get the List of Objects where, Properties of the Object were id, name, gradename, class.

How to do it with HQL.

Answer 1

You wouldn't do that in Hibernate. The whole point of using hibernate is that you are dealing with objects.

So I guess your Student class would have a List of type Grade

@Entity
public class Student{
    // accessors and id omitted
    @OneToMany(mappedBy="student")
    private List<Grade> grades;
}

@Entity
public class Grade{
    // accessors and id omitted
    @ManyToOne
    private Student student;
}

You would look up the grade via session.get() and then do grade.getStudent() to access the student:

Grade grade = (Grade) session.get(Grade.class, gradeId);
Student student = grade.getStudent();

HQL queries are designed for Scenarios that are too complex for these lookup methods.

Edit: I just realized that the question is tagged jpa. Then of course you would not be using HQL, but JPQL instead. And also you'd be using this code:

Grade grade = entityManager.find(Grade.class, gradeId); // no cast needed with JPA 2
Student student = grade.getStudent();

---

Edit: given the requirements you added in your comments I'd change the data model to something like this (ids omitted):

@Entity
public class Student{
    public List<Course> getCourses(){
        return courses;
    }
    public void setCourses(List<Course> courses){
        this.courses = courses;
    }
    @OneToMany(mappedBy="student")
    private List<Course> courses;
}

@Entity
public class Course{
    @ManyToOne(optional=false)
    private Student student;
    @ManyToOne(optional=false)
    private Grade grade;
    public void setStudent(Student student){
        this.student = student;
    }
    public Student getStudent(){
        return student;
    }
    public Grade getGrade(){
        return grade;
    }
    public void setGrade(Grade grade){
        this.grade = grade;
    }
}

@Entity
public class Grade{
    @OneToMany(mappedBy="grade")
    private Set<Course> courses;
    public void setCourses(Set<Course> courses){
        this.courses = courses;
    }
    public Set<Course> getCourses(){
        return courses;
    }
}

And I'd query like this:

Grade grade = entityManager.find(Grade.class, 1L);
List<Student> studentsWithThisGrade = new ArrayList<Student>();
for(Course course : grade.getCourses()){
    studentsWithThisGrade.add(course.getStudent());
}

(But Grade should probably not be an entity, but either a numeric value or an enum.)

---

It turns out that the OP uses plain hibernate after all, no JPA. So whenever you see entityManager.find() above, replace that in your mind with session.get() :-)

Answer 2

Try this.

public List<Student> getByGradeId(EntityManager entityManager, Grade grade) {
   Query query = entityManager.createQuery("from Student where grade=:grade");
   query.setParameter("grade", grade);
   return (List<Student>) query.getResultList();
}

You'll need a try/catch in there too.

Read section 3.4 of this!