Home Hibernate Hibernate4 Hibernate Criteria Detached Subquery Example


Share on Google+Share on Google+

Hibernate Criteria Detached Subquery Example

In this tutorial you will learn how to express a subquery using DetachedCriteria.

Hibernate Criteria Detached Subquery Example

In this tutorial you will learn how to express a subquery using DetachedCriteria.

As you have been read in the previous example Hibernate Criteria Detached Query Subquery Hibernate provides the facility to write the query aside from the session scope and get executed it in an arbitrary Session. You can write the subquery also using the DetachedCriteria.

for example :

DetachedCriteria avgAge = DetachedCriteria.forClass(Employee1.class)
session = sessionFactory.openSession(); 
List ls = session.createCriteria(Employee1.class)

(Click Here for Criteria Detached Subquery using "Subqueries" example).


Below I am giving an example which will demonstrate you how to write a subquery using DetachedCriteria. In this example basic works are done as we were doing in our previous examples such as creation of table in the database where the data has to be persisted then creation of POJO/ Persistent class for persisting the persistent object a hibernate mapping file that maps the class properties with the corresponding table fields. A configuration mapping file creation that provides the information to the Hibernate to create the connection pool and their respective required environment set up. And finally creation of a main class where we will write the query to fetch data from the table. After execution of this example will return the data from employeename1 table whose age is greater than the average age of all employeename1 table.

Table employeename1

create table employeename1 (
id int (10) not null,
name varchar (15) default null,
gender varchar (5) default null,
age int (10) default 0,
primary key (id)


package roseindia;

public class Employee1 {
private int id; 
private String name;
private String gender;
private int age;

public Employee1()

public Employee1(String name, String gender, int age)
this.name = name;
this.gender = gender;
this.age = age;
public int getId()
return id;
public void setId(int id)
this.id = id;
public String getName()
return name;
public void setName(String name)
this.name = name;
public String getGender()
return gender;
public void setGender(String gender)
this.gender = gender;
public int getAge()
return age;
public void setAge(int age)
this.age = age;


<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate mapping DTD//EN"

<class name= "roseindia.Employee1" table="employeename1">
<id name= "id" type="int" >
<generator class="native" />
<property name="name" />
<property name="gender" />
<property name="age" type="int" />




<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"

<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">root</property>
<property name="hibernate.connection.pool_size">10</property>
<property name="show_sql">true</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.current_session_context_class">thread</property>



package roseindia;

import java.util.List;
import java.util.Iterator;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Subqueries;
import org.hibernate.SessionFactory;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

public class HibernateDetachedSubqueryExample {
private static SessionFactory sessionFactory= null;
private static ServiceRegistry serviceRegistry= null;

public static void main(String args[])
Session session= null;

Configuration cfg = new 
serviceRegistry = new 
sessionFactory = cfg.buildSessionFactory(serviceRegistry);
catch(Throwable th)
System.err.println("Failed to create sessionFactory object."
+ th);
throw new ExceptionInInitializerError(th);

DetachedCriteria avgAge = DetachedCriteria.forClass(Employee1.class)
session = sessionFactory.openSession(); 
List ls = session.createCriteria(Employee1.class)
Iterator it = ls.iterator();
System.out.println("Id \t name \t gender age");
Employee1 emp1 = (Employee1)it.next();
System.out.println(emp1.getId()+"\t "+emp1.getName()+"\t 
"+emp1.getGender()+"\t "+emp1.getAge());


Output :

table employeename1

When you will execute the java file HibernateDetachedSubqueryExample.java (RightClick -> RunAs -> JavaApplication) the query and the output will be displayed on your console as :

Query on console will be as :

INFO: HHH000399: Using default transaction strategy (direct JDBC transactions)
Mar 22, 2012 6:22:43 PM org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory <init>
INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select this_.id as id0_0_, this_.name as name0_0_, this_.gender as gender0_0_, this_.age as age0_0_ from employeename1 this_ where this_.age > (select avg(this_.age) as y0_ from employeename1 this_)

An output will be displayed on your console as :

Download Source Code


Liked it!  Share this Tutorial

Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: March 23, 2012

Ask Questions?    Discuss: Hibernate Criteria Detached Subquery Example  

Post your Comment

Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image