In section we'll show how to use Group by clause in criteria.

We are grouping here by deptno of emp table

These are the steps

 

  1. Write down the Employee bean classs.
    package com.bullraider.criteria;
    import java.io.Serializable;
    public class Employee implements Serializable{
    	private long empno;
    	private String ename;
    	private int sal;
    	private String job;
    	private int deptno ;
    	
    	public long getEmpno() {
    		return empno;
    	}
    	public void setEmpno(long empno) {
    		this.empno = empno;
    	}
    	public String getEname() {
    		return ename;
    	}
    	public void setEname(String ename) {
    		this.ename = ename;
    	}
    	public int getSal() {
    		return sal;
    	}
    	public void setSal(int sal) {
    		this.sal = sal;
    	}
    	public String getJob() {
    		return job;
    	}
    	public void setJob(String job) {
    		this.job = job;
    	}
    	public int getDeptno() {
    		return deptno;
    	}
    	public void setDeptno(int deptno) {
    		this.deptno = deptno;
    	}
    	
    
    }
    
    
  2. This is the employee.hbm.xml
    <?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>
    	<class name="com.bullraider.criteria.Employee" table="emp1000">
    		<meta attribute="class-description"> This class contains employee details. </meta>
    		<id name="empno" type="long" column="empno">
    			<generator class="native" />
    		</id>
    		<property name="ename" type="string" column="ename" not-null="true" />
    		<property name="job" type="string" column="job" not-null="true" />
    		<property name="sal" type="integer" column="sal" not-null="true" />
    		<property name="deptno" type="integer" column="deptno"
    			not-null="true" />
    	</class>
    </hibernate-mapping>
    
  3. This is the HibernateUtil Class to get specially SessionFactory object
    package com.bullraider.criteria;
    
    import org.hibernate.SessionFactory;
    import org.hibernate.cfg.Configuration;
    
    public class HibernateUtil {
    	private static final SessionFactory sessionFactory;
    	static {
    		try {
    			sessionFactory = new Configuration().configure()
    					.buildSessionFactory();
    		} catch (Throwable ex) {
    			System.err.println("SessionFactory creation failed" + ex);
    			throw new ExceptionInInitializerError(ex);
    		}
    	}
    
    	public static SessionFactory getSessionFactory() {
    		return sessionFactory;
    	}
    }
    
    
  4. This is the main class
    package com.bullraider.criteria;
    import java.util.Iterator;
    import java.util.List;
    
    import org.hibernate.Criteria;
    import org.hibernate.HibernateException;
    import org.hibernate.Session;
    import org.hibernate.Transaction;
    import org.hibernate.criterion.Expression;
    import org.hibernate.criterion.Order;
    import org.hibernate.criterion.Projections;
    
    public class GroupByInCritaria {
    	public static void main(String[] args) {
    		GroupByInCritaria dao=new GroupByInCritaria();
    		dao.saveEmployee("Rex", "MGR", 30000, 40);
    		dao.saveEmployee("Raj", "CLERK", 1000, 30);
    		dao.saveEmployee("Mike", "SALESMAN", 8000, 10);
    		dao.saveEmployee("Reheman", "MGR", 30000, 10);
    		dao.saveEmployee("Raja", "CLERK", 10000, 30);
    		dao.saveEmployee("Mittu", "SALESMAN", 8000, 20);
    		dao.saveEmployee("Srinivas", "CLERK", 10000, 30);
    		dao.saveEmployee("Amit", "SALESMAN", 8000, 20);
    		dao.retriveEmployee();
    
    	}
    	public Long saveEmployee(String ename,String job,int sal,int deptno)
    	{
    		Session session = HibernateUtil.getSessionFactory().openSession();
    		Transaction transaction = null;
    		Long empId = null;
    		try {
    			transaction = session.beginTransaction();
    			Employee emp=new Employee();
    			emp.setEname(ename);
    			emp.setJob(job);
    			emp.setSal(sal);
    			emp.setDeptno(deptno);
    			session.persist(emp);
    			transaction.commit();
    			System.out.println("Records inserted sucessessfully");
    		} catch (HibernateException e) {
    			transaction.rollback();
    			e.printStackTrace();
    		} finally {
    			session.close();
    		}
    		return empId;
    	}
    
    	@SuppressWarnings("unchecked")
    	public void retriveEmployee() 
    
    	{ 
    		final int MINSAL=1000;
    		final int MAXSAL=3000;
    		Session session = HibernateUtil.getSessionFactory().openSession(); 
    		Transaction transaction = null; 
    		try { 
    			transaction = session.beginTransaction(); 
    			System.out.println("HI");
    			List employee = 
    				session.createCriteria(Employee.class).setProjection( 
    						Projections.projectionList().add( 
    								Projections.max("sal") ).add (Projections.property("deptno") ).
    								add( 
    										Projections.groupProperty("deptno"))).list();
    
    			List results = session.createCriteria(Employee.class)
    			.setProjection( Projections.projectionList().add( 
    					Projections.avg("sal"), "avgsal" )
    					.add( Projections.max("sal"), "maxsal" )
    					.add( Projections.groupProperty("deptno"), "deptno" )
    			)
    			
    			.addOrder( Order.desc("avgsal") )
    			.list();
    			System.out.println("HI");
    			for (Iterator iterator = employee.iterator(); iterator.hasNext();) 
    			{ 
    				Employee employee1 = (Employee) iterator.next(); 
    				System.out.println(employee1.getEmpno()+"  "+employee1.getEname()+"  "+
    						employee1.getJob()+"   "+employee1.getSal()+"   "+employee1.getDeptno()); 
    			}           
    			transaction.commit(); 
    
    		} catch (HibernateException e) { 
    
    			transaction.rollback(); 
    
    			e.printStackTrace(); 
    
    		} finally { 
    
    			session.close(); 
    
    		} 
    	}
    
    }
    
    
  5. Design the hibernate.cfg.xml
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE hibernate-configuration PUBLIC
    		"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    		"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
    <hibernate-configuration>
        <session-factory>
            <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
            <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:ORCL</property>
            <property name="hibernate.connection.username">scott</property>
            <property name="connection.password">cat</property>
            <property name="connection.pool_size">1</property>
            <property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>
            <property name="show_sql">true</property>
            <property name="hbm2ddl.auto">create</property>
            <mapping resource="com/bullraider/criteria/Employee.hbm.xml"/>
        </session-factory>
    </hibernate-configuration>
    
    

 

download Criteria_groupby