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