Hibernate groupby criteria HQL query Example

By | 2019-03-10T09:34:47+05:30 March 10th, 2019|Hibernate|0 Comments

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:

Hi Folks, you have reach this so far, that shows you like what you are learning. Then why don't you support us to improve for bettor tutorials by leaving your valuable comments and why not you keep in touch with us for latest updates on your favorite blog @ facebook , twitter , Or Google+ ,

Leave A Comment