Joins using HQL

         We use hibernate framework in our projects for the ORM (Object Relational Mapping) technology. In the beginning we were facing some problem to associate two or more tables through joins by using hibernate. But after getting clear picture on ORM model and how it (ORM) can be achieved using Hibernate framework made the joins usage simpler.

         In this blog we will explain how to execute join using HQL. Here we will take a simple example of mapping a department to multiple employees in an organization.

Create tables:

        The following tables need to be defined in the database

/*Table structure for `Department` */   CREATE TABLE `Department` (   `dept_id` int(4) NOT NULL,   `dept_name` varchar(30) NOT NULL,   PRIMARY KEY (`dept_id`)   )

/*Table structure for `Employee` */   CREATE TABLE `Employee` (   `emp_no` int(4) NOT NULL,   `emp_name` varchar(30) NOT NULL,   `dept_id` int(4) NOT NULL,   `email` varchar(30) default NULL,   PRIMARY KEY (`emp_no`),    KEY `FK_employee` (`dept_id`),   CONSTRAINT `FK_employee` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`)   )

POJO file for Department table

package com.css.labs.blog.pojo;     import java.util.Set;   /** * @author CSS Labs */   public class Department {         private int deptId;         private String deptName;         private Set emp;       /** * @return the deptId */               public  int  getDeptId  ()  {                             return deptId;                             }       /** * @param deptId the deptId to set */               public    void  setDeptId  (int  deptId)  {                             this.deptId = deptId;                             }       /** * @return the deptName */                 public String getDeptName () {                             return deptName;                             }       /** * @param deptName the deptName to set */               public void setDeptName (String deptName) {                             this.deptName = deptName;                             }       /** * @return the emp */               public Set<Employee> getEmp () {                             return emp;                             }       /** * @param emp the emp to set */               public void setEmp (Set<Employee> emp) {                             this.emp = emp;                             }   }

Mapping file of Department table

<?xml version="1.0" encoding="UTF-8"?>   <!DOCTYPE hibernate - mapping PUBLIC "-//Hibernate /Hibernate Mapping DTD 3.0//EN"   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >>     <hibernate-mapping>        <class name="com.css.labs.blog.pojo.Department"table="Department">              <id name="deptId" column="dept_id"    type="java.lang.Integer">                 <generator class="increment" />            </id>              <property name="deptName"  column="dept_name" type="java.lang.String" >            <set name="emp" cascade="all" lazy="true">                <key column="dept_id" />                <one-to-many class="com.css.labs.blog.pojo.Employee" />            </set>        </class>   </hibernate-mapping>

POJO file for Employee table

package com.css.labs.blog.pojo;   /** * @author CSS Labs */   public class Employee {         private int empNo;         private String empName;         private int deptId;         private String email;       /** * @return the empNo */               public int getEmpNo () {                              return empNo;                             }        /** * @param empNo * the empNo to set */               public void setEmpNo (int empNo) {                             this.empNo = empNo;                             }       /** * @return the empName */                 public String getEmpName () {                             return empName;                             }       /** * @param empName * the empName to set */               public void setEmpName (String empName) {                             this.empName = empName;                             }        /** * @return the deptId */               public int getDeptId () {                             return deptId;                             }        /** * @param deptId * the deptId to set */                public void setDeptId (int deptId) {                             this.deptId = deptId;                             }        /** * @return the email */               public String getEmail () {                              return email;                             }        /** * @param email * the email to set */               public void setEmail (String email) {                             this.email = email;                             }   }

Mapping file of Employee table

<?xml version="1.0" encoding="UTF-8"?>   <!DOCTYPE hibernate - mapping PUBLIC "-//Hibernate /Hibernate Mapping DTD 3.0//EN"   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >>    <hibernate-mapping>        <class name="com.css.labs.blog.pojo.Employee"table="Employee">              <id name="empNo" column="emp_no" type="java.lang.Integer">                 <generator class="increment" />            </id>              <property name="deptId" column="dept_id" type="java.lang.Integer" >              <property name="empName" column="emp_name" type="java.lang.String" >              <property name="email" column="email" ttype="java.lang.String" >        </class>   </hibernate-mapping>

Configuration File [hibernate.cfg.xml]

<?xml version="1.0" encoding="UTF-8"?>   <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate /Hibernate Configuration DTD 3.0//EN"   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >>    <hibernate-configuration>        <session-factory>              <property name="connection.url">jdbc:mysql://<<server ip>>:3306 /databasename</property>              <property name="connection.username">username</property>                          <property name="connection.password">password</property>              <property name="connection.driver_class">com.mysql.jdbc.Driver</property>              <property name="dialect">org.hibernate.dialect.MySQLDialect</property>              <!-- mapping files -->              <mapping resource="com/css/labs/blog/map/employee.hbm.xml" />              <mapping resource="com/css/labs/blog/map/department.hbm.xml" />   </session-factory>   </hibernate-configuration>

Query

The join query for getting all the employees mapped to one particular department will be

“select emp.empName from Department dept join dept.emp emp where dept.deptId = 2”

Hope this blog helped you to understand how to excecute JOIN's using HQL.