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:

Hibernate GroupBy Example-min

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 🙂

About the Author:

Founder of onlinetutorialspoint.com Love Java, Python, Shell and opensource frameworks. Follow him on twitter and facebook for latest updates.

Leave A Comment