CODEWITHSUNDEEP
hibernateinner-join
Livin As
Livin As

Reputation: 143

Hibernate inner join using hql

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;
}

Upvotes: 5

Views: 13810

Answers (3)

Sudhir Mane
Sudhir Mane

Reputation: 306

Please find below the HQL:

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

Upvotes: -3

JB Nizet
JB Nizet

Reputation: 692151

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'

Upvotes: 6

EdC
EdC

Reputation: 2349

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

Upvotes: 3

Related Questions