Hibernate Group by Clause

Hibernate Group by Clause

In this tutorial we will discuss about Hibernate group by with example.

Hibernate group by :

Hibernate supports concept of group by. In general ,Group by clause Takes information from database ant group it based on a value of an attribute and typically, use the result to include an aggregate value.
It returns aggregate values which is grouped by any property of a returned class or component.

In Hibernate you can write group by query by using HQL ,Criteria or native SQL.

UsingHQL -

String hql="SELECT designation ,sum(salary) FROM Employee emp GROUP BY emp.designation";
Query query=session.createQuery(hql);

Using Criteria -

Criteria criteria=session.createCriteria(Employee.class);
ProjectionList projectionList=Projections.projectionList();
projectionList.add(Projections.sum("salary"));

Example : In this example Employee is our persistent class mapping employee_detail table. We are calculating sum of salary grouping by designation using HQL .

package net.roseindia.application;

import java.util.Iterator;
import java.util.List;
import net.roseindia.util.HibernateUtil;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

public class HibernateGroupBy {
public static void main(String []args){
SessionFactory sessionFactory=HibernateUtil.getSessionFactory();
try{
Session session=sessionFactory.openSession();
String hql="SELECT designation ,sum(salary) FROM Employee emp GROUP BY emp.designation";
Query query=session.createQuery(hql);
List<Object> list=query.list();
Iterator iterator=list.iterator();
System.out.println("Designation\tTotal Salary");
while(iterator.hasNext()){
Object []obj = (Object[])iterator.next();
System.out.print(obj[0]);
System.out.print("\t\t"+obj[1]);
System.out.println();
}
}catch(HibernateException e){
e.printStackTrace();
}
}
}

Output :

Hibernate: select employee0_.designatiom as col_0_0_, sum(employee0_.salary) as col_1_0_ from employee_record employee0_ group by employee0_.designatiom
Designation    Total Salary
JSE            10000.0
PM             50000.0
SE             35000.0
SSE            25000.0
TL             30000.0

Click here to download complete source code