1

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.

Improve this question
8
  • Are you aware that there's a typo in the table name? Commented Sep 22, 2010 at 12:11
  • I personally have never made a copy/paste error. Wait, what if that really is the name of the table! Commented Sep 22, 2010 at 12:21
  • @BALUSC,@TONY its "student" i typed it wrongly. sorry. Commented Sep 22, 2010 at 12:28
  • 3
    Too much Weed maybe? (sorry :-)) Commented Sep 22, 2010 at 12:35
  • Maybe you should clarify once and for all: do you use plain hibernate (Query language: HQL, Query interface: Session) or JPA with hibernate as provider (Query language: JPQL, Query interface: EntityManager). Commented Sep 22, 2010 at 13:56

2 Answers 2

Reset to default
4

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() :-)

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

I rather thought that many Students could shared a Grade. So he could be trying to find all students that got a "B" for example. IN which case, the query is appropriate.
@SEANIZER I want to get the whole table using a join between the two tables, where GRADE_ID replaced by GRADE_NAME. the resultset wont be having the GRADE_ID column.
The problem is that you are still thinking in tables, whereas you should be thinking in objects if you use hibernate and / or jpa. Read this: en.wikipedia.org/wiki/Object-relational_impedance_mismatch
@ Code N Weed - when switching from pure SQL to HSQL, you must remember you have to do things the HSQL way. While technically interesting, it is detrimental to try to mimic SQL. Instead, we do things the HSQL way since HSQL works easier with less code that way. In my query below, note I didn't specify the column I wanted back - Hibernate wants to give me collections of models. That's good stuff. There's no analog in SQL which returns little more than columns of data.
@seanizer. Ya i accept. What about this, Now I need a list of objects which have the properties like id, name, gradeName, class.
|
0

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!

Improve this answer

5 Comments

@ tony, ya u r right. my requirement to find all students sharing the particular Grade. I want to show all the student details in the student table.
Cool. Then you'd call the method above and iterate over the resulting List. Now, note you have to pass it a Grade instance. I'd add a method to my GradeDAO called Grade getByGrade(EntityManager em, String gradeName) which has a similar query that returns the row with a grade of "B" or whatever. That method would use query.getSingleResult() instead of getResultList() however.
@Tony Apart from the fact that EntityManager isn't used (OP finally said he used hibernate), I disagree with your design. DAO methods are business methods, EntityManagers (or hibernate sessions) are implementation details. The dao should have a field of type entityManager / entityManagerFactory / session / sessionFactory
Hmmm We used Hibernate. For whatever reason, we get EntityManagers out of it. I'm not at work now (contract has ended) so I can't give any particulars. Let me see if I can dig up some code... The dao should have a field of type entityManager / entityManagerFactory / session / sessionFactory Ah, do you mean you wouldn't pass the EntityManager into the method? I agree. I was mimicking what we did. But yes I'd have liked using the DAOs more if the EntityManager was once upon creation. That being said, the way it is has a certain simplicity to it.
See section 2.4 for an example of Hibernate using an EntityManager. docs.jboss.org/hibernate/stable/entitymanager/reference/en/html/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.