Home >> Hibernate
Applying Filter programmatically using HQL from Hibernate Framework.
In order to explain Hibernate Filter, I would use the same example of
Employee and Department, as already used in many such writings of mine,
in this site.
In this example of Hibernate Filter, we have two entities, such as Employee
and Dept, with two database tables namely, employee and dept.
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)
Dept and Employee entities share one-to-many type of Hibernate Mapping,
and the HBM mapping files details as follows: (One can use this for
personal and learning purpose only, no commercial use allowed)
|
|  |
|
<?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" table="employee" cascade="persist,delete">
<key column="dept_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"/>
<one-to-one name="dept" class="Dept" />
</class>
</hibernate-mapping>
|
Looking at the collection of employees configured as SET with name employees
in the HBM file, shows that Dept entity has a variable employees with setter and
getter methods for holding corresponding Employees records from database.
HSQLDB database related SQL script for creation of these two tables, as follows
create table dept
(dept_id integer, dept_name varchar(100), primary key (dept_id));
create table employee
(employee_id integer, employee_name varchar(100), dept_id integer,
primary key (employee_id), foreign key (dept_id) references dept(dept_id));
insert into dept
values('1001', 'example Department');
insert into employee
values('2001','employee name 1','1001');
insert into employee
values('2002','employee name 2','1001');
insert into employee
values('2003','employee name 3','1001');
|
Hibernate Entities for this example, includes two POJO, such as Employee
and Dept, as shown below:
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;
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;
}
}
Dept.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;
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;
}
}
Hibernate configuration file for this Hibernate Filter example is as follows:
This file will used by this example Client program to create Hibernate
SessionFactory instance:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- properties -->
<property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
<property name="connection.url">jdbc:hsqldb:hsql://localhost/</property>
<property name="connection.username">sa</property>
<property name="connection.password"></property>
<property name="dialect">org.hibernate.dialect.HSQLDialect</property>
<property name="show_sql">true</property>
<property name="current_session_context_class">thread</property>
<!-- mapping files -->
<mapping resource="demo/DeptsEmployee.hbm.xml"/>
</session-factory>
</hibernate-configuration>
As you can see, I have used HSQLDB database server, and if anyone not used
this database server before, just for information, you can start this HSQLDB
database server by using a script file at %HSQLDB%\demo\runServer.bat .
This script file will start HSQLDB database server.
In order to run all the SQL (create table, and insert SQL scripts) from this
example, you have to run the HSQLDB manager from %HSQLDB%\demo\runManager.bat
script file.
From the HSQLDB manager GUI confihuration dialog box, you have to choose
"HSQL Database Engine Server" from the drop down menu for "TYPE".
After the default HSQLDB database setup for this example is done, once can
write the test client for this Hibernate Filter Example.
In the test client, objective is to test the Filter criteria being set
programmatically and based on the filter criteria, one should be able to
get appropriate list of employee records/Entities.
Client.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;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Restrictions;
public class Client {
// Hibernate SessionFactory
SessionFactory sessionFactory;
public Client() {
//Hibernate Configuration
Configuration conf = new Configuration();
//Loading configuration properties file and building Hibernate
SessionFactory.
sessionFactory = conf.configure("hibernate.cfg.xml")
.buildSessionFactory();
if(sessionFactory != null) {
Session session = sessionFactory.getCurrentSession();
Transaction trans = session.getTransaction();
trans.begin();
Dept dept = (Dept)session.get(Dept.class, new Integer(1001));
Query query = session.createFilter(dept.getEmployees(),
"where employeeName like :employeeName1");
query.setParameter("employeeName1", "%employee name%");
List list = query.list();
System.out.println("Number of employee records fetched from database : "
+list.size());
trans.commit();
}
}
/**
* Client main method
* @param args
*/
public static void main(String[] args) {
new Client();
}
}
This test client will show the output as
Number of employee records fetched from database : 3
As there are total three Employee records exists for the criteria as
where employeeName like '%employee name%'
createFilter method of Hibernate Session instance will accept a persistent
entity (in this case it is Dept instance retrieved from database for dept_id
as 1001), and the query string (HQL criteria, such as where employeeName like
:employeeName1, employeeName1 being the parameter with value set as "%employee name%" ).
To this point we have seen how to use Hibernate Filter at runtime, using createFilter
method from Hibernate Session instance. Now we shall use this example with Hibernate
Filter definition in Hibernate HBM configuration.
"In this example, we are having department and employee tables, and we have to query
those employees of all the departments with salary greater than average salary of all
the departments."
(This is just a hypothetical case study)
Following DDL statements are to be written for creating appropriate tables:
create table Employee_Details
(
emp_id varchar(5) not null,
dept_id varchar(5),
emp_salary decimal(5,2),
primary key (emp_id), foreign key (dept_id) references Department_Details(dept_id)
)
create table Department_Details
(
dept_id varchar(5) not null,
dept_name varchar(5),
primary key(dept_id)
)
Following DML statements are to be written for populating corresponding tables:
insert into Department_Details values('D001','TEST_DEPT_A');
insert into Department_Details values('D002','TEST_DEPT_B');
insert into Department_Details values('D003','TEST_DEPT_C');
insert into Department_Details values('D004','TEST_DEPT_D');
insert into Employee_Details values('E001', 'D001', 20500);
insert into Employee_Details values('E002', 'D001', 21500);
insert into Employee_Details values('E003', 'D002', 23500);
insert into Employee_Details values('E004', 'D002', 21600);
insert into Employee_Details values('E005', 'D003', 20500);
insert into Employee_Details values('E006', 'D001', 21500);
insert into Employee_Details values('E007', 'D003', 23500);
insert into Employee_Details values('E008', 'D004', 21600);
As per the case study, let us try to write a SQL for creating a view:
create view
V_emp_dept as
select e.emp_id, e.emp_salary from employee_details e, department_details d where
e.emp_salary >= select avg(emp_salary) from employee_details and
e.dept_id = d.dept_id;
When I executed this DDL, a view with name V_emp_dept is created with following
records:
EMP_ID EMP_SALARY
---------------------
E003 23500
E007 23500
After the above step, objective of this example will be to be able to query following
set of rows/records from these two tables such as "Employee_Details" and "Department_Details"
by using Hibernate mapping configuration using respective class/POJO Java files such as
Employee and Department class files.
Employee.java file should have three instance level variables such as empId, dept (of type
Department instance) and empSalary.
Department.java file should have two instance level variables such as deptId, deptName.
Employee class should have one-to-one mapping with Department class, and a Filter definition
with parameter and condition for the where clause empSalary >= avg(empSalary) from Employee,
something like this.
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-Filter-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
