Hibernate Criteria Projections, Aggregation and Grouping
In this tutorial you will learn about the Projections, Aggregation and Grouping in Criteria Query.
In Criteria query a query result set projection is represented as object oriented by the Projection interface (org.hibernate.criterion interface Projection). Projection interface may be implemented by the class which want to define the custom projections. A class Projections (org.hibernate.criterion.Projections) is a factory for instantiating the Projection instances, a projection can be applied into query by addressing setProjection() method.
NOTE : Projections can also be expressed as Property.forName()
Click Here for Hibernate Criteria Grouping tutorial
Example
An example is being given below that will demonstrate you how to set the projection in your code. Method setProjection() of Criteria specified that the results of query will be a projection. In example I have used the three types of query to count the rows by calling the methods Projections.rowCount() to find the number of rows in goodsdealer table, Projections.countDistinct() to find the rows that contains the unique value, Projections.rowCount().add(Restrictions.between()) to find the number of rows between a specified range.
Table goodsdealer
CREATE TABLE `goodsdealer` ( `goodsDealerId` int(15) NOT NULL, `orderedGoodsName` varchar(15) default NULL, `numOfGoodsOrder` int(20) default NULL, `priceOfGoods` int(10) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1
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;
}
}
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"> </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>
HibernateCriteriaProjectionExample.java
package roseindia;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;
public class HibernateCriteriaProjectionExample {
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();
Criteria criteria = session.createCriteria(GoodsDealer.class)
// To count the no of rows in table goodsdealer
.setProjection(Projections.rowCount());
List totalRowCount = criteria.list();
System.out.println("Number of rows in goodsdealer table"+totalRowCount);
// To count the distinct (unique) number of rows
criteria.setProjection(Projections.countDistinct("numOfGoodsOrder"));
List distinctRowCount = criteria.list();
System.out.println("Number of Distinct rows are : "+distinctRowCount);
// To count the number of rows using the Restrictions
criteria.setProjection(Projections.rowCount()).add(Restrictions.between("numOfGoodsOrder", 110, 300));
List restrictionsRowCount = criteria.list();
System.out.println("Number of rows between 110 - 300 : "+restrictionsRowCount);
}
catch(Exception e)
{
System.out.println(e.getMessage());
}
finally
{
session.close();
}
}
}
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>
Output :
1. table goodsdealer
2. When you will execute the java file HibernateCriteriaProjectionExample.java (RightClick -> RunAs -> JavaApplication) you will get the result on your console as :
2.1. When in the configuration xml file the property "show sql" is not used or is set to false
2.2. When in the configuration xml file the property "show sql" is used or is set to true
Hibernate: select count(*) as y0_ from goodsdealer this_ Number of rows in goodsdealer table : [7] Hibernate: select count(distinct this_.numOfGoodsOrder) as y0_ from goodsdealer this_ Number of Distinct rows are : [5] Hibernate: select count(*) as y0_ from goodsdealer this_ where this_.numOfGoodsOrder between ? and ? Number of rows between 110 - 300 : [3]