HQL Group By Clause Example

Group by clause is used to return the aggregate values by grouping on returned component. HQL supports Group By Clause. In our example we will calculate the sum of invested amount in each insurance type. Here is the java code for calculating the invested amount insurance wise:

package roseindia.tutorial.hibernate; import org.hibernate.Session; import org.hibernate.*; import org.hibernate.cfg.*; import java.util.*; /**  * @author Deepak Kumar  *   * http://www.roseindia.net  HQL Group by Clause Example  *    */ public class HQLGroupByExample {   public static void main(String[] args) {   Session session = null;   try {   // This step will read  hibernate.cfg.xml and prepare hibernate for   // use   SessionFactory sessionFactory =  new Configuration().configure()   .buildSessionFactory();   session = sessionFactory.openSession();   //Group By Clause Example   String SQL_QUERY = "select sum (insurance.investementAmount), insurance.insuranceName "   + "from Insurance insurance  group by insurance.insuranceName";  Query query = session.createQuery(SQL_QUERY);  for (Iterator it =  query.iterate(); it.hasNext();) {  Object[] row = (Object[]) it.next();  System.out.println(" Invested Amount: " + row[0]);  System.out.println(" Insurance Name: " + row[1]);   }   session.close();   } catch (Exception e) {   System.out.println(e.getMessage());   } finally {   }   } }

To run the example select Run-> Run As -> Java Application from the menu bar. Following out is displayed in the Eclipse console:

Hibernate: select sum(insurance0_.invested_amount) as col_0_0_, insurance0_.insurance_name as col_1_0_ from insurance insurance0_ group by insurance0_.insurance_name Invested Amount: 3500 Insurance Name: Car Insurance Invested Amount: 500 Insurance Name: Dental Insurance Invested Amount: 1550 Insurance Name: Home Insurance Invested Amount: 1500 Insurance Name: Life Insurance Invested Amount: 1600 Insurance Name: Medical Insurance Invested Amount: 1680 Insurance Name: Motorcycle Insurance Invested Amount: 2600 Insurance Name: Travel Insurance