Here I m going to show how to read the data from database using hibernate groupby expression.
Hibernate groupby Example:
I have a simple Item class which represents different items with categories. I am going to read data with a count of items for each category using groupby expression.
Sample Data:

Versions:
- Hibernate core 5.2.2
- Java 8
Dependencies:
pom.xml
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.2.12.Final</version>
</dependency>
Item.class, which represents an Item.
Item.java
package com.onlinetutorialspoint.entity;
import javax.persistence.*;
@Entity
@Table(name="item")
public class Item {
@Id
@GeneratedValue(strategy= GenerationType.AUTO)
@Column(name="id")
private int itemId;
@Column(name="item_name",length=30)
private String itemName;
@Column(name="category",length=30)
private String itemCategory;
@Column(name="item_price")
private double itemPrice;
public Item() {
}
public Item(String itemName, String itemCategory, double itemPrice) {
this.itemName = itemName;
this.itemCategory = itemCategory;
this.itemPrice = itemPrice;
}
public int getItemId() {
return itemId;
}
public void setItemId(int itemId) {
this.itemId = itemId;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public String getItemCategory() {
return itemCategory;
}
public void setItemCategory(String itemCategory) {
this.itemCategory = itemCategory;
}
public double getItemPrice() {
return itemPrice;
}
public void setItemPrice(double itemPrice) {
this.itemPrice = itemPrice;
}
}
HibernateUtil.java responsible to provide Hibernate Session Factory object.
HibernateUtil.java
package com.onlinetutorialspoint.util;
import org.hibernate.SessionFactory;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
public class HibernateUtil {
private static StandardServiceRegistry standardServiceRegistry;
private static SessionFactory sessionFactory;
static{
if (sessionFactory == null) try {
standardServiceRegistry = new StandardServiceRegistryBuilder()
.configure()
.build();
MetadataSources metadataSources = new MetadataSources(standardServiceRegistry);
Metadata metadata = metadataSources.getMetadataBuilder().build();
sessionFactory = metadata.getSessionFactoryBuilder().build();
} catch (Exception e) {
e.printStackTrace();
if (standardServiceRegistry != null) {
StandardServiceRegistryBuilder.destroy(standardServiceRegistry);
}
}
}
public static SessionFactory getSessionFactory() {
return sessionFactory;
}
}
1. Hibernate groupby Criteria:
Here we are using CriteriaQuery Interface to make the group by functionality.
Hibernate CriteriaQuery:
HibernateGroupby.java
public static void groupByCriteria(){
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = builder.createQuery(Object[].class);
Root<Item> root = criteriaQuery.from(Item.class);
criteriaQuery.multiselect(root.get("itemCategory"),builder.count(root.get("itemCategory")));
criteriaQuery.groupBy(root.get("itemCategory"));
Query<Object[]> query = session.createQuery(criteriaQuery);
List<Object[]> resultList = query.getResultList();
resultList.forEach(item-> System.out.println("Category : "+item[0]+"\t count : "+item[1]));
} catch (Exception e) {
e.printStackTrace();
}
}
Similar SQL query:
SQL
mysql> select category, count(category) from item group by category;
Output:
Terminal
Hibernate: select item0_.category as col_0_0_, count(item0_.category) as col_1_0_ from item item0_ group by item0_.category
Category : Books count : 4
Category : Laptop count : 5
Category : Mobiles count : 6
1.1 groupby Criteria with having clause:
We can even apply having clause on criteria query on top of groupby function like below.
HibernateGroupby.java
public static void groupByCriteria(){
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = builder.createQuery(Object[].class);
Root<Item> root = criteriaQuery.from(Item.class);
criteriaQuery.multiselect(root.get("itemCategory"),builder.count(root.get("itemCategory")));
criteriaQuery.groupBy(root.get("itemCategory"));
criteriaQuery.having(builder.greaterThan(builder.sum(root.get("itemPrice")), 70000.00));
Query<Object[]> query = session.createQuery(criteriaQuery);
List<Object[]> resultList = query.getResultList();
resultList.forEach(item-> System.out.println("Category : "+item[0]+"\t count : "+item[1]));
} catch (Exception e) {
e.printStackTrace();
}
}
Output:
Terminal
Hibernate: select item0_.category as col_0_0_, count(item0_.category) as col_1_0_ from item item0_ group by item0_.category having sum(item0_.item_price)>70000.0
Category : Laptop count : 5
Category : Mobiles count : 6
2. Hibernate groupby using HQL:
HibernateGroupby.java
public static void groupByHQL(){
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
Query query = session.createQuery("SELECT i.itemCategory, COUNT(i.itemCategory) " +
"FROM Item i GROUP BY i.itemCategory");
List<Object[]> list = query.getResultList();
list.forEach(item-> System.out.println("Category : "+item[0]+"\t count : "+item[1]));
} catch (Exception e) {
e.printStackTrace();
}
}
Output:
Terminal
Hibernate: select item0_.category as col_0_0_, count(item0_.category) as col_1_0_ from item item0_ group by item0_.category
Category : Books count : 4
Category : Laptop count : 5
Category : Mobiles count : 6
2.1 groupby HQL with having clause:
HibernateGroupby.java
public static void groupByWithHavingHQL(){
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
Query query = session.createQuery("SELECT i.itemCategory, COUNT(i.itemCategory), SUM(i.itemPrice) " +
"FROM Item i GROUP BY i.itemCategory HAVING SUM(i.itemPrice) > 70000");
List<Object[]> list = query.getResultList();
list.forEach(item-> System.out.println("Category : "+item[0]+"\t count : "+item[1]));
} catch (Exception e) {
e.printStackTrace();
}
}
Output:
Terminal
Hibernate: select item0_.category as col_0_0_, count(item0_.category) as col_1_0_, sum(item0_.item_price) as col_2_0_ from item item0_ group by item0_.category having sum(item0_.item_price)>70000
Category : Laptop count : 5
Category : Mobiles count : 6
References:
Happy Learning 🙂