Hibernate Criteria Detached Subquery Using "Subqueries"

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

Hibernate Criteria Detached Subquery Using "Subqueries"

Hibernate Criteria Detached Subquery Using "Subqueries"

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

In Hibernate Criteria with the help of DetachedCriteria a query can be written aside from the scope of a Session. Using DetachedCriteria we can also write the subqueries. Writing method of subquery using DetachedCriteria is just had been given in the previous example ( if didn't read Click here Hibernate Criteria Detached Subquery Example ) now I am going to give an another method to write the subquery using DetachedCriteria. In this example I will use the org.hibernate.criterion.Subqueries class. This is a factory class for instantiating the criterion which represents the expressions that are involved in subqueries.

for example

DetachedCriteria avgAge = DetachedCriteria.forClass(Employee1.class)
.setProjection(Property.forName("age").avg());
session = sessionFactory.openSession();
List ls = session.createCriteria(Employee1.class)
.add(Subqueries.propertyGt("age", avgAge)).list();

Example

Below I am giving an example which will demonstrate you how to write a subquery using DetachedCriteria and Subqueries. In this example the 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)
)

Employee1.java

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;
}
}

Employee1.hbm.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate mapping DTD//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

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

</class>

</hibernate-mapping>

hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://192.168.10.13:3306/data
</property>
<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>
</session-factory>

</hibernate-configuration>

HibernateCriteriaDetachedSubQueriesExample.java

package roseindia;

import java.util.List;
import java.util.Iterator;
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 HibernateCriteriaDetachedSubQueriesExample {
private static SessionFactory sessionFactory= null;
private static ServiceRegistry serviceRegistry= null;

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

try 
{
Configuration cfg = new Configuration().addResource("roseindia/Employee1.hbm.xml");
cfg.configure();
serviceRegistry = new ServiceRegistryBuilder().applySettings(cfg.getProperties()).buildServiceRegistry();
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)
.setProjection(Property.forName("age").avg());
session = sessionFactory.openSession();
List ls = session.createCriteria(Employee1.class)
.add(Subqueries.propertyGt("age", avgAge)).list();
Iterator it = ls.iterator();
System.out.println("Id \t name \t gender age");
while(it.hasNext())
{
Employee1 emp1 = (Employee1)it.next();
System.out.println(emp1.getId()+"\t "+emp1.getName()+"\t "+emp1.getGender()+"\t "+emp1.getAge());
}
session.close();
}

}

Output :

table employeename1

When you will execute the java file HibernateCriteriaDetachedSubQueriesExample.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 7:12:40 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