Understanding left outer join using Hibernate Mapping and fetching strategy

Following example has the software environment as follows:

1. JDK 5.0  (Java Platform)
2. Eclipse 3.2.0 (IDE)
3. Hibernate 3.2 (Hibernate Framework)
4. HSQLDB 1.7.3  (Database)

Let us take an example case study :
Take for example that there are three Hibernate Entities such as Employee,
Department and Skillset. For the sake of this example, a department can have
many employees and each employee can have zero or more skill set.
So Employee Persistent Entity is mapped to Department persistent Entity in an
many-to-one mapping strategy. Department and Skillset persistent Entities are
mapped to Employee Entity in a one-to-many type of Hibernate mapping strategy.


Domain model for this example is as follows:


This is represented in form of the following HBM file (of this example):
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="demo">
  <class name="Dept" table="Dept">
    <id name="deptId" column="dept_id">
      <generator class="assigned"/>
    </id>
    <property name="deptName" column="dept_name" type="java.lang.String"/>
    <set name="employees">
      <key column="dept_id"/>
      <one-to-many class="Employee"/>
    </set>
  </class>
  <class name="Skillset" table="Skillset">
    <id name="skillsetId" column="skillset_id">
      <generator class="assigned"/>
    </id>
    <property name="skillSetDesc" column="skillset_desc"
                                                 type="java.lang.String"/>
    <set name="employees">
      <key column="skillset_id"/>
      <one-to-many class="Employee"/>
   </set>
  </class>

  <class name="Employee" table="employee">
    <id name="employeeId" column="employee_id">
      <generator class="assigned"/>
    </id>
    <property name="employeeName" column="employee_name"
                                                type="java.lang.String"/>
    <many-to-one name="dept" column="dept_id" class="Dept" fetch="join"/>
    <many-to-one name="skillSet" column="skillset_id" class="Skillset"
                                                           fetch="join"/>
  </class>
</hibernate-mapping>


Objective of this example is to explain OUTER JOIN capability of while using
Hibernate, just by setting fetch as "join" in many-to-one type of Hibernate Mapping.
So let us come up with some sample test data for validating these objectives as
against SQL query result.

Structure of Employee Table with test data, are as follows:

All DML in this example are for HSQLDB database only

create table Employee
(employee_id integer, employee_name varchar(100), skillset_id integer, dept_id integer,
 primary key(employee_id),
 foreign key(skillset_id) references skillset(skillset_id),
 foreign key (dept_id) references Dept(dept_id));

employee_id
employee_name
skillset_id
dept_id
3001
girish
2002
1002
3002
steve
2002
1003
3003
crish
2001
1001
3004
suresh
 
 
3005
guddu
 
 
3006
Tom
 
 


Structure of Dept table along with test data as follows:

create table Dept
(dept_id integer, dept_name varchar(50), primary key(dept_id));

dept_id
dept_name
1001
HR
1002
Finance
1003
Stores


Structure of Skill Set table along with test data as follows:

create table Skillset
(skillset_id integer, skillset_desc varchar(50), primary key(skillset_id));

skillset_id 
skillset_desc
2001
Java
2002
J2EE
2003
Struts


Corresponding INSERT statements are as follows:
insert into Dept values(1001, 'HR');
insert into Dept values(1002, 'Finance');
insert into Dept values(1003, 'Stores');

insert into skillset values(2001, 'Java');
insert into skillset values(2002, 'J2EE');
insert into skillset values(2003, 'Struts');

insert into Employee values(3001,'girish',2002,1002);
insert into Employee values(3002,'steve',2002,1003);
insert into Employee values(3003,'crish',2001,1001);

insert into Employee(employee_id,employee_name) values(3004,'suresh');
insert into Employee(employee_id,employee_name) values(3005,'guddu');
insert into Employee(employee_id,employee_name) values(3006,'Tom');


Suppose in this example, one had to write a SQL query in order to create
a report where by all employee names along with their corresponding skill sets
and department names are to be shown, then the SQL statement will be
something like as follows:
(This SQL can be optimized even better, but I would try to make it simple as shown below)
select e.employee_name, s.skillset_desc, d.dept_name from Employee e
left outer join Dept d on e.dept_id = d.dept_id left outer join
Skillset s on s.skillset_id = e.skillset_id


And the result will be as shown below:


employee_name
skillset_desc
dept_name
girish
J2EE
Finance
steve
J2EE
Stores
crish
Java
HR
suresh
 
 
guddu
 
 
Tom
 
 


This example will try to create appropriate mapping using Hibernate mapping,
for fetching similar set of data, using fetch="join" in a many-to-one
mapping tag used with Employee persistent Entity.

Test client for this example is very simple, it is just creating
Hibernate criteria by using Hibernate Session instance from SessionFactory, and
the code snippet is as follows:

List list = session.createCriteria(Employee.class).list();

Reference:
Remaining Entities for this example are as follows:

Employee.java
/**
*  This source is provided as is, without any warranty
*  and /or guaranty of any kind.
*  Copyright (C) 2008, ISHTIAK, All Rights Reserved.
*  You can use it for Personal Learning purpose only.
*  E-mail: usingframeworks@gmail.com
*/

package demo;

public class Employee {
	private int employeeId;
	private String employeeName;
	private Dept dept;
	private Skillset skillSet;
	public Dept getDept() {
		return dept;
	}
	public void setDept(Dept dept) {
		this.dept = dept;
	}
	public int getEmployeeId() {
		return employeeId;
	}
	public void setEmployeeId(int employeeId) {
		this.employeeId = employeeId;
	}
	public String getEmployeeName() {
		return employeeName;
	}
	public void setEmployeeName(String employeeName) {
		this.employeeName = employeeName;
	}
	public Skillset getSkillSet() {
		return skillSet;
	}
	public void setSkillSet(Skillset skillSet) {
		this.skillSet = skillSet;
	}
}


Dept.java

/**
*  This source is provided as is, without any warranty
*  and /or guarantee of any kind.
*  Copyright (C) 2008, ISHTIAK, All Rights Reserved.
*  You can use it for Personal Learning purpose only.
*  E-mail: usingframeworks@gmail.com
*/

package demo;

import java.util.Set;

public class Dept {
	private int deptId;
	private String deptName;
	private Set employees;
	public int getDeptId() {
		return deptId;
	}
	public void setDeptId(int deptId) {
		this.deptId = deptId;
	}
	public String getDeptName() {
		return deptName;
	}
	public void setDeptName(String deptName) {
		this.deptName = deptName;
	}
	public Set getEmployees() {
		return employees;
	}
	public void setEmployees(Set employees) {
		this.employees = employees;
	}
}



Skillset.java
/**
*  This source is provided as is, without any warranty
*  and /or guarantee of any kind.
*  Copyright (C) 2008, ISHTIAK, All Rights Reserved.
*  You can use it for Personal Learning purpose only.
*  E-mail: usingframeworks@gmail.com
*/

package demo;

import java.util.Set;

public class Skillset {
	private int skillsetId;
    private String skillSetDesc;
    private Set employees;
	public Set getEmployees() {
		return employees;
	}
	public void setEmployees(Set employees) {
		this.employees = employees;
	}
	public String getSkillSetDesc() {
		return skillSetDesc;
	}
	public void setSkillSetDesc(String skillSetDesc) {
		this.skillSetDesc = skillSetDesc;
	}
	public int getSkillsetId() {
		return skillsetId;
	}
	public void setSkillsetId(int skillsetId) {
		this.skillsetId = skillsetId;
	}
}


After running thie example client, following is the SQL (auto generated by Hibernate)
shown in on command prompt as console:

select this_.employee_id as employee1_2_2_, this_.employee_name as
employee2_2_2_, this_.dept_id as dept3_2_2_, this_.skillset_id as
skillset4_2_2_, dept2_.dept_id as dept1_0_0_, dept2_.dept_name as dept2_0_0_,
skillset3_.skillset_id as skillset1_1_1_, skillset3_.skillset_desc as
skillset2_1_1_ from employee this_ left outer join Dept dept2_ on
this_.dept_id=dept2_.dept_id left outer join Skillset skillset3_ on
this_.skillset_id=skillset3_.skillset_id


If you like to share your comment/suggestions/feedback relating to this Page,
you can do so by droping us an email at usingframeworks @ gmail . com
with the subject line mentioning URL for this Page (i.e, /Hibernate-join-example.php) or use this
LINK.
As per this website's privacy policy, we never disclose your email id,
though we shall post your comments/suggestions/feedback with
your name (optional) and date on this Page. If you don't want your
comments/suggestions/feedback to be shared in this Page, please
mention so in your email to us. Thank you very much.....
If anything missed out , please let me know at
techienjoy at yahoo . com