Monday, March 8, 2010

Joins using HQL

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.

4 comments:

cssbalaji said...

Blog is good. But need more information on HQL. Hope HQL stands for Hibernate Qurey Language.

Shafi said...

Yes, HQL stands for Hibernate Qurey Language.HQL is similar in appearance to SQL. Compared with SQL, however, HQL is fully object-oriented and understands notions like inheritance, polymorphism and association. In HQL, queries are framed on the objects which is indirectly mapped to the tables in database.

Wild Net Media Technology said...

Wild Net Media Technology, website design and SEO Company Delhi provides website design and seo services in Delhi, India and the firm has a creative website designer and SEO Expert in India.

disk doctors digital media recovery said...

orepen 15Thank you for influencing so many readers with your great information. Your article has sparked a lot of thought for me. I will share your views and info with my friend and class fellows. Keep it up.