Hibernate HQL Group By Tutorial

Author:
This tutorial will guide you through setting up and using the Hibernate Query Language (HQL) GROUP BY clause with a MySQL database using the Product entity. We will demonstrate how to perform group by queries to aggregate data.

Introduction

Hibernate Query Language (HQL) is a powerful query language similar to SQL but designed for Hibernate. It allows you to perform database operations using the object-oriented paradigm. In this tutorial, we will show you how to use the GROUP BY clause in HQL to group and aggregate data.

In this tutorial, we will:

  1. Set up a Maven project with necessary dependencies.
  2. Configure Hibernate and MySQL.
  3. Create an entity class (Product).
  4. Separate the Hibernate code into a DAO class.
  5. Demonstrate the use of HQL GROUP BY clause.

Step 1: Set Up Your Project

1.1 Create a Maven Project

Open your IDE and create a new Maven project.

1.2 Add Dependencies

Update your pom.xml file to include dependencies for Hibernate and MySQL.

<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>hibernate-hql-group-by-example</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!-- Hibernate ORM -->
        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>6.4.0.Final</version>
        </dependency>

        <!-- MySQL Connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.10.1</version>
                <configuration>
                    <source>21</source>
                    <target>21</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Step 2: Configure Hibernate

2.1 Create hibernate.cfg.xml

Create a hibernate.cfg.xml file in the src/main/resources directory to configure database connection settings and Hibernate properties.

<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <!-- JDBC Database connection settings -->
        <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/hibernate_db</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">password</property>

        <!-- JDBC connection pool settings -->
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.max_size">20</property>
        <property name="hibernate.c3p0.timeout">300</property>
        <property name="hibernate.c3p0.max_statements">50</property>
        <property name="hibernate.c3p0.idle_test_period">3000</property>

        <!-- SQL dialect -->
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.format_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hibernate.hbm2ddl.auto">update</property>

        <!-- Entities -->
        <mapping class="com.example.entity.Product"/>
    </session-factory>
</hibernate-configuration>

Replace hibernate_db, root, and password with your MySQL database name and credentials.

Explanation:

  • hibernate.connection.driver_class specifies the JDBC driver class for MySQL.
  • hibernate.connection.url specifies the JDBC URL for the database connection.
  • hibernate.connection.username and hibernate.connection.password specify the database credentials.
  • hibernate.c3p0 properties configure the connection pool settings using C3P0.
  • hibernate.dialect specifies the SQL dialect to be used.
  • hibernate.show_sql and hibernate.format_sql properties are used to display and format the generated SQL statements.
  • hibernate.hbm2ddl.auto specifies the schema generation strategy.
  • The <mapping class="com.example.entity.Product"/> line maps the Product entity to the database.

Step 3: Create the Entity Class

Create an entity class Product that will be mapped to a table in the database. This class uses annotations to define the entity and its fields.

package com.example.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String category;
    private String name;
    private double price;

    // Getters and setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCategory() {
        return category;
    }

    public void setCategory(String category) {
        this.category = category;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }
}

Explanation:

  • The @Entity annotation specifies that the class is an entity and is mapped to a database table.
  • The @Id annotation specifies the primary key of the entity.
  • The @GeneratedValue(strategy = GenerationType.IDENTITY) annotation specifies that the primary key is auto-incremented.
  • The category field will be used for grouping in the HQL queries.

Step 4: Create the DAO Class

Create a DAO class to manage database operations using Hibernate.

4.1 Create ProductDAO

package com.example.dao;

import com.example.entity.Product;
import com.example.util.HibernateUtil;
import org.hibernate.Session;
import org.hibernate.query.Query;

import java.util.List;

public class ProductDAO {
    public List<Object[]> getProductCountByCategory() {
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            String hql = "select p.category, count(p) from Product p group by p.category";
            Query<Object[]> query = session.createQuery(hql, Object[].class);
            return query.list();
        }
    }

    public void insertSampleData() {
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            session.beginTransaction();
            session.persist(new Product("Electronics", "Laptop", 1000.00));
            session.persist(new Product("Electronics", "Phone", 500.00));
            session.persist(new Product("Furniture", "Table", 300.00));
            session.persist(new Product("Furniture", "Chair", 100.00));
            session.getTransaction().commit();
        }
    }

    public List<Product> getAllProducts() {
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            return session.createQuery("from Product", Product.class).list();
        }
    }
}

Explanation:

  • The ProductDAO class contains methods to interact with the database using Hibernate.
  • The getProductCountByCategory method uses the GROUP BY clause to get the count of products in each category.
  • The insertSampleData method inserts sample data into the Product table.
  • The getAllProducts method retrieves all products from the database.

Step 5: Demonstrate the Use of HQL GROUP BY Clause

Create a main class to demonstrate the use of the HQL GROUP BY clause using the Product entity and ProductDAO class.

5.1 Create MainApp

package com.example.main;

import com.example.dao.ProductDAO;
import com.example.entity.Product;
import com.example.util.HibernateUtil;

import java.util.List;

public class MainApp {
    public static void main(String[] args) {
        ProductDAO productDAO = new ProductDAO();

        // Insert sample data
        productDAO.insertSampleData();

        // Display all products
        System.out.println("All Products:");
        List<Product> products = productDAO.getAllProducts();
        products.forEach(product -> System.out.println(product.getCategory() + " - " + product.getName() + " - " + product.getPrice()));

        // Perform HQL GROUP BY clause query
        List<Object[]> productCounts = productDAO.getProductCountByCategory();

        System.out.println("Product count by category:");
        productCounts.forEach(row -> System.out.println("Category: " + row[0] + ", Count: " + row[1]));

        // Shut down the SessionFactory
        HibernateUtil.shutdown();
    }
}

Explanation:

  • The MainApp class demonstrates how to perform an HQL GROUP BY clause query using the ProductDAO class.
  • The insertSampleData method is called to insert sample data into the Product table.
  • The getAllProducts method is called to retrieve all products from the database and display them.
  • The getProductCountByCategory method is called to retrieve the count of products in each category using the GROUP BY clause, and the results are printed to the console.

5.2 Create HibernateUtil Class

Create a utility class HibernateUtil to manage the Hibernate SessionFactory.

package com.example.util;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateUtil {
    private static final SessionFactory sessionFactory = buildSessionFactory();

    private static SessionFactory buildSessionFactory() {
        try {
            // Load the configuration and build the SessionFactory
            return new Configuration().configure().buildSessionFactory();
        } catch (Throwable ex) {
            System.err.println("Initial SessionFactory creation failed." + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }

    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    public static void shutdown() {
        getSessionFactory().close();
    }
}

Explanation:

  • The HibernateUtil class provides a singleton SessionFactory and a method to shut it down.
  • The buildSessionFactory method loads the Hibernate configuration from hibernate.cfg.xml and builds the SessionFactory.

Step 6: Run the Application

  1. Ensure your MySQL database is running and the connection details in hibernate.cfg.xml are correct.
  2. Run the MainApp class to load the Hibernate configuration, build the SessionFactory, insert sample data, perform the HQL GROUP BY clause query, and print the results.

Sample Output

If everything is set up correctly, running the MainApp class should produce output similar to the following:

All Products:
Electronics - Laptop - 1000.0
Electronics - Phone - 500.0
Furniture - Table - 300.0
Furniture - Chair - 100.0
Product count by category:
Category: Electronics, Count: 2
Category: Furniture, Count: 2

Conclusion

In this tutorial, we have successfully demonstrated how to create and configure a Hibernate configuration file (hibernate.cfg.xml) to connect to a MySQL database using the Product entity and perform an HQL GROUP BY clause query. We separated the Hibernate code into a DAO class, configured the project dependencies, created an entity class, set up the Hibernate configuration file, and demonstrated the use of the HQL GROUP BY clause. This guide provides a solid foundation for using HQL GROUP BY clause with Hibernate and a MySQL database in your applications.

Comments

Post a Comment

Leave Comment