HQL - Implicit Join
Implicit Join
As we know there are three types of joins - Left, Right and Inner Joins. These joins can be written in HQL in a shorter and convenient way.
Say there are two tables, EMPLOYEE and ADDRESS created by Hibernate.
EMPLOYEE
ADDRESS
| ID |
ADDRESS_ID |
STREET_NAME |
CITY |
PIN_CODE |
| 1 |
1 |
Walls street |
Delhi |
110012 |
| 1 |
2 |
Murugeshpalya |
Bangalore |
560019 |
| 2 |
3 |
Trisar |
Hydrabad |
500003 |
| 2 |
4 |
Dumdum |
Kolkata |
700028 |
So, from the above table we can see that 'ID' is the primary key for the 'EMPLOYEE' table and the same 'ID' is the foreign key for the 'ADDRESS' table.
Now, if you are asked to get the Names of the Employees who are from 'Bangalore', your SQL query would be:
SELECT A.NAME FROM EMPLOYEE A INNER JOIN ADDRESS B ON A.ID = B.ID WHERE B.CITY='Bangalore';
The HQL query for the same would be :
select employee.name from Employee employee WHERE employee.addressSet.city='Bangalore'
Let us see the detail explanation below:
Example in detail
Assuming that we have an Employee class
Employee
class Employee {
int id;
String name;
Set <Address> addressSet = new HashSet <Address>();
-- getters and setters --
}
Address
class Address{
int addressId;
String streetName;
String city;
String pinCode;
---Getters & Setters---
}
And with the mapping files Employee.hbm.xml and Address.hbm.xml right in place, the main() class would be :
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class HibernateSave {
public static void main(String[] args) {
static SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();
session.beginTransaction();
Query query = session.createQuery("select employee.name from Employee employee WHERE employee.addressSet.city='Bangalore'");
session.getTransaction().commit();
session.close();
sessionFactory.close();
}
}
select employee.name from Employee employee WHERE employee.addressSet.city='Bangalore'
Instead of writing a long join query we have written a short statement in WHERE clause
employee.addressSet.city='Bangalore'
which says the 'employee' object has an 'addressSet'('addressSet' contains the Set of 'Address' objects) and it should search for 'city' as 'Bangalore' in that 'addressSet'.
Finally, Hibernate takes care of converting it to the actual SQL query.
Note :Hibernate also gives us the flexibility to write the actual SQL join query in HQL.
