Hibernate Subqueries Example

Hibernate Subqueries Example

In this tutorial you will learn about HQL subqueries.

A query that is written within a databases supported query is called subquery. A subquery is surrounded by parentheses. Subqueries are generally written for solving the query result grouping, ordering, narrowing, and aggregating, that are used by where clause. Hibernate HQL also supports, if database supports subselect, subqueries within queries. for example :

from GoodsDealer gd where gd.numOfGoodsOrder =
(select max(gd.numOfGoodsOrder) from gd )

NOTE : Subselect / Subqueries are executed before the main query.

Example :

Here an example is being given below which will demonstrate you how to write HQL subquery within query. To do so at first we will required a database that supports the subselect query I have selected MySQL (you can choose as your choice) and then created a table named goodsdealer(goodsDealerId(int), orderedGoodsName(varchar), numOfGoodsOrder(int), priceOfGoods(int) ) then created a POJO / Persistent class GoodsDealer.java class to fetch the persistent object, next created a hibernate.cfg.xml file that provides the information to the Hibernate to create connection pool and the required environment setup, then created a GoodsDealer.hbm.xml file to map GoodsDealer.java class object with table goodsdealer. And finally create a simple java file into which we will write subquery within a query.

goodsdealer table

CREATE TABLE `goodsdealer` ( 
`goodsDealerId` int(15) NOT NULL, 
`orderedGoodsName` varchar(15) default NULL, 
`numOfGoodsOrder` int(20) default NULL, 
`priceOfGoods` int(10) default NULL 
)

GoodsDealer.java

package roseindia;

public class GoodsDealer {
private int goodsDealerId;
private int numOfGoodsOrder;
private int priceOfGoods;
private String orderedGoodsName;


public GoodsDealer()
{
super();
}
public GoodsDealer(int priceOfGoods, int goodsDealerId, int numOfGoodsOrder, String 
orderedGoodsName)
{
this.priceOfGoods = priceOfGoods;
this.goodsDealerId = goodsDealerId;
this.numOfGoodsOrder = numOfGoodsOrder;
this.orderedGoodsName = orderedGoodsName;
}
public int getPriceOfGoods() {
return priceOfGoods;
}
public void setPriceOfGoods(int priceOfGoods) {
this.priceOfGoods = priceOfGoods;
}
public int getGoodsDealerId() {
return goodsDealerId;
}
public void setGoodsDealerId(int goodsDealerId) {
this.goodsDealerId = goodsDealerId;
}
public int getNumOfGoodsOrder() {
return numOfGoodsOrder;
}
public void setNumOfGoodsOrder(int numOfGoodsOrder) {
this.numOfGoodsOrder = numOfGoodsOrder;
}
public String getOrderedGoodsName() {
return orderedGoodsName;
}
public void setOrderedGoodsName(String orderedGoodsName) {
this.orderedGoodsName = orderedGoodsName;
}
}

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>

GoodsDealer.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 package="roseindia">
<class name= "GoodsDealer" table="goodsdealer">
<id name= "goodsDealerId" type="int" column="goodsDealerId">
<!-- <generator class="increement"/>-->
</id>
<property name="numOfGoodsOrder">
<column name="numOfGoodsOrder"/>
</property>
<property name="orderedGoodsName">
<column name="orderedGoodsName"/>
</property>
<property name="priceOfGoods">
<column name="priceOfGoods"/>
</property>
</class>

</hibernate-mapping>

HibernateSubqueryExample.java

package roseindia;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

public class HibernateSubqueryExample {
private static SessionFactory sessionFactory;
private static ServiceRegistry serviceRegistry;

public static void main(String args[])
{
Session session = null;
try
{
try
{
Configuration cfg= new Configuration().addResource("roseindia/GoodsDealer.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);
}
session = sessionFactory.openSession();
Query query = session.createQuery("from GoodsDealer gd where gd.numOfGoodsOrder = (select max(gd.numOfGoodsOrder) from gd )");
List<GoodsDealer> subQuery = query.list(); 
Iterator<GoodsDealer> it = subQuery.iterator();
System.out.println("goodsDealerId \t numOfGoodsOrder \t orderedGoodsName \t priceOfGoods");
while(it.hasNext())
{
GoodsDealer gd = it.next();
System.out.println("\t"+gd.getGoodsDealerId()+" \t\t "+gd.getNumOfGoodsOrder()+" \t\t\t "+gd.getOrderedGoodsName()+" \t\t "+gd.getPriceOfGoods()); 
}

}
catch(Exception e)
{
System.out.println(e.getMessage());
} 
finally
{
session.close();
}
}
}

Output :

Mar 5, 2012 4:49:45 PM org.hibernate.engine.transaction.internal.TransactionFactoryInitiator initiateService
INFO: HHH000399: Using default transaction strategy (direct JDBC transactions)
Mar 5, 2012 4:49:45 PM org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory <init>
INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select goodsdeale0_.goodsDealerId as goodsDea1_0_, goodsdeale0_.numOfGoodsOrder as 
numOfGoo2_0_, goodsdeale0_.orderedGoodsName as orderedG3_0_, goodsdeale0_.priceOfGoods as priceOfG4_0_ from 
goodsdealer goodsdeale0_ where goodsdeale0_.numOfGoodsOrder=(select max(goodsdeale0_.numOfGoodsOrder) from 
goodsdealer goodsdeale0_)

1. goodsdealer table

2. When you will execute the java file HibernateSubqueryExample.java (RightClick -> Run As -> Java Application an output will be displayed on your console as :

Download Source Code