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

� View all related tutorials

Leave your comment:
Name:
Email:
URL:
Title:
Comments:
Enter Code:
	Note: Emails will not be visible or used in any way, and are not required. Please keep comments relevant. Any content deemed inappropriate or offensive may be edited and/or deleted. No HTML code is allowed. Line breaks will be converted automatically. URLs will be auto-linked. Please use BBCode to format your text.