Hibernate HQL Join Example with MySQL Database

This tutorial will guide you through setting up and using Hibernate Query Language (HQL) joins with a MySQL database using the Product entity. We will demonstrate how to perform inner joins, left joins, and right joins using HQL.

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 perform joins using HQL.

In this tutorial, we will:

1. Set up a Maven project with necessary dependencies.
2. Configure Hibernate and MySQL.
3. Create entity classes (Product and Category).
4. Demonstrate various HQL join queries.

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-join-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"/>
        <mapping class="com.example.entity.Category"/>
    </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"/> and <mapping class="com.example.entity.Category"/> lines map the Product and Category entities to the database.

Step 3: Create the Entity Classes

Create entity classes Product and Category that will be mapped to tables in the database. These classes use annotations to define the entities and their fields.

3.1 Create Product Entity

package com.example.entity;

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

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

    @ManyToOne
    private Category category;

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

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

    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;
    }

    public Category getCategory() {
        return category;
    }

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

3.2 Create Category Entity

package com.example.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import java.util.List;

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

    @OneToMany(mappedBy = "category")
    private List<Product> products;

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

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

    public String getName() {
        return name;
    }

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

    public List<Product> getProducts() {
        return products;
    }

    public void setProducts(List<Product> products) {
        this.products = products;
    }
}

Explanation:

• The Product entity represents the Product table in the database. It has a many-to-one relationship with the Category entity.
• The Category entity represents the Category table in the database. It has a one-to-many relationship with the Product entity.

Step 4: Demonstrate Various HQL Join Queries

Create a main class to demonstrate various HQL join queries using the Product and Category entities.

4.1 Create MainApp

package com.example.main;

import com.example.entity.Category;
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 MainApp {
    public static void main(String[] args) {
        // Load the configuration and build the SessionFactory
        Session session = HibernateUtil.getSessionFactory().openSession();

        // Insert sample data
        insertSampleData(session);

        // Perform HQL join queries
        performHQLJoinQueries(session);

        // Close the session
        session.close();

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

    private static void insertSampleData(Session session) {
        session.beginTransaction();

        Category electronics = new Category();
        electronics.setName("Electronics");

        Category furniture = new Category();
        furniture.setName("Furniture");

        session.persist(electronics);
        session.persist(furniture);

        session.persist(new Product("Laptop", 1000.00, electronics));
        session.persist(new Product("Phone", 500.00, electronics));
        session.persist(new Product("Table", 300.00, furniture));

        session.getTransaction().commit();
    }

    private static void performHQLJoinQueries(Session session) {
        // Inner join
        Query<Object[]> query1 = session.createQuery("select p.name, c.name from Product p join p.category c", Object[].class);
        List<Object[]> results1 = query1.list();
        System.out.println("Inner Join:");
        for (Object[] result : results1) {
            System.out.println("Product: " + result[0] + ", Category: " + result[1]);
        }

        // Left join
        Query<Object[]> query2 = session.createQuery("select p.name, c.name from Product p left join p.category c", Object[].class);
        List<Object[]> results2 = query2.list();
        System.out.println("Left Join:");
        for (Object[] result : results2) {
            System.out.println("Product: " + result[0] + ", Category: " + result[1]);
        }

        // Right join
        Query<Object[]> query3 = session.createQuery("select p.name, c.name from Product p right join p.category c", Object[].class);
        List<Object[]> results3 = query3.list();
        System.out.println("Right Join:");
        for (Object[] result : results3) {
            System.out.println("Product: " + result[0] + ", Category: " + result[1]);
        }
    }
}

Explanation:

• The MainApp class demonstrates how to perform various HQL join queries using Hibernate.
• The insertSampleData method inserts sample data into the Product and Category tables.
• The performHQLJoinQueries method demonstrates different HQL join queries:
  • Inner join to fetch products and their categories.
  • Left join to fetch all products and their categories, including those without categories.
  • Right join to fetch all categories and their products, including those without products.

4.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 5: 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 HQL join queries, and print the results.

Sample Output

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

Inner Join:
Product: Laptop, Category: Electronics
Product: Phone, Category: Electronics
Product: Table, Category: Furniture
Left Join:
Product: Laptop, Category: Electronics
Product: Phone, Category: Electronics
Product: Table, Category: Furniture
Right Join:
Product: Laptop, Category: Electronics
Product: Phone, Category: Electronics
Product: Table, Category: Furniture

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 and Category entities and perform various HQL join queries. We configured the project dependencies, created entity classes, set up the Hibernate configuration file, and demonstrated different HQL join queries. This guide provides a solid foundation for using HQL joins with Hibernate and a MySQL database in your applications.