Hibernate inner join using hql

Question

I am new to Hibernate. I have two tables, e.g., student and phone number and these two tables have a common column, student id. I want to do an inner join with these two tables using Hibernate hql.

student.java

{
   private int id;   
   private String name;   
}

phone.java

{
   private int pid;
   private int sid;  //same id in student.java 
   private int phone_number;
}

score 6 · Accepted Answer · answered Sep 13 '12 at 10:15

6

Read the documentation again. You're not supposed to have the ID of the student in the Phone entity. Rather, you're supposed to have an association between both entities: a Phone belongs to a Student:

public class Phone {
    @Id
    private Integer id;

    private String phoneNumber;

    @ManyToOne
    private Student owner;
}

Only then will you be able to use joins:

// selects all the phones belonging to the students named 'John'
select phone from Phone phone where phone.owner.name = 'John'

answered Sep 13 '12 at 10:15

JB Nizet

678,734
91
1,224
1,255

ok thanks..how can i will perform the same thing more than two tables ?? – Livin As Sep 13 '12 at 10:24
Of course you can: where phone.owner.school.country.name = 'USA' for example. This is explained in the excellent Hibernate documentation. Read it. – JB Nizet Sep 13 '12 at 12:05
ok thanks..then how can we iterate the query results for a many to many relationships in hibernate ..I will tried a lot.But I can't achieving it. – Livin As Sep 20 '12 at 09:12
this is my query String hql = "select e.employee_id,m.meeting_id from Employee e join e.meetings m"; query = session.createQuery(hql); – Livin As Sep 20 '12 at 09:15
This query returns a `List – JB Nizet Sep 20 '12 at 09:18
Thank you JB Nizet...can you please provide an example this query: String hql = "select e.employee_id,m.meeting_id from Employee e join e.meetings m" – Livin As Sep 20 '12 at 09:39
You don't know how to iterate a List? And you're using Hibernate? Anyway, here it goes: `List – JB Nizet Sep 20 '12 at 09:49
Hi JB Nizet I have one more doubt.How can I iterate the same List – Livin As Sep 24 '12 at 06:26
This is the code am tryed.but i can't achieved that. – Livin As Sep 24 '12 at 06:36
This is the code am tryed.but I can't achieved that. – Livin As Sep 24 '12 at 06:42

score 3 · Answer 2 · answered Sep 13 '12 at 10:15

With the two classes like that hibernate is unaware of the association that makes life difficult. What would be normal is to make the sid in the phone class an actual Student object so hibernate is aware of the association. e.g.

class Phone {
    @ManyToOne
    @Column(name = "sid")
    private Student student;
}

having done this then you can do a simple HQL join e.g.

FROM Phone p
JOIN p.student s

Alternatively if there is some reason you want the raw ID in the object then you can use a "theta join" where you explicitly specify the association like a normal SQL Join. E.g.

FROM Phone p, Student s
WHERE p.sid = s.id

score -3 · Answer 3 · answered Sep 13 '12 at 10:28

-3

Please find below the HQL:

SELECT * FROM student ST INNER JOIN phonenumber PN ON ST.id = PN.id where ST.Name='XXXX'

answered Sep 13 '12 at 10:28

Sudhir Mane

308
1
9

i ma getting this error :org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ON near line 1, column 91 – kozla13 May 17 '13 at 12:53

Hibernate inner join using hql

3 Answers3