Guide to Hibernate Query Language (HQL)

Author:
Hibernate Query Language (HQL) is a powerful query language similar to SQL but operates on entity objects rather than tables and columns. This guide will provide an in-depth explanation of HQL, its syntax, and examples using Hibernate 6.4.

Introduction to HQL

HQL is designed to query the object model and not the relational model, making it database-independent and more aligned with the application's object-oriented nature.

Key Features of HQL

  • Object-Oriented: Queries are written in terms of the entity model, not the database schema.
  • Database Independent: Queries are independent of the underlying database.
  • Powerful and Flexible: Supports CRUD operations, joins, aggregations, and subqueries.

Setting Up Hibernate 6.4

1. Add Dependencies

First, add Hibernate, MySQL, and JPA dependencies to your pom.xml file:

<dependencies>
    <dependency>
        <groupId>org.hibernate.orm</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>6.4.0.Final</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.32</version>
    </dependency>
    <dependency>
        <groupId>jakarta.persistence</groupId>
        <artifactId>jakarta.persistence-api</artifactId>
        <version>3.0.0</version>
    </dependency>
</dependencies>

2. Configure Hibernate

Create a hibernate.cfg.xml file under the resources directory to configure Hibernate:

<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/hibernate_db?useSSL=false</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">root</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQL8Dialect</property>
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.hbm2ddl.auto">update</property>
        <mapping class="com.example.entity.Student"/>
    </session-factory>
</hibernate-configuration>

3. Create Entity Class

Define a Student entity class to represent a table in the database:

package com.example.entity;

import jakarta.persistence.*;

@Entity
@Table(name = "student")
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String firstName;
    private String lastName;
    private String email;

    // Constructors, getters, setters, and toString() method
}

4. Hibernate Utility Class

Create a utility class to manage Hibernate sessions:

package com.example.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 registry;
    private static SessionFactory sessionFactory;

    public static SessionFactory getSessionFactory() {
        if (sessionFactory == null) {
            try {
                registry = new StandardServiceRegistryBuilder().configure().build();
                MetadataSources sources = new MetadataSources(registry);
                Metadata metadata = sources.getMetadataBuilder().build();
                sessionFactory = metadata.getSessionFactoryBuilder().build();
            } catch (Exception e) {
                e.printStackTrace();
                if (registry != null) {
                    StandardServiceRegistryBuilder.destroy(registry);
                }
            }
        }
        return sessionFactory;
    }

    public static void shutdown() {
        if (registry != null) {
            StandardServiceRegistryBuilder.destroy(registry);
        }
    }
}

Basic HQL Syntax

HQL syntax is similar to SQL but operates on the entity objects. Here are the basic components of HQL:

  • SELECT Clause: Retrieves data from the database.
  • FROM Clause: Specifies the entity to query.
  • WHERE Clause: Applies filters to the query.
  • ORDER BY Clause: Sorts the results.
  • JOIN Clause: Performs joins between entities.

SELECT Query

String hql = "FROM Student";
Session session = HibernateUtil.getSessionFactory().openSession();
List<Student> students = session.createQuery(hql, Student.class).getResultList();
students.forEach(System.out::println);
session.close();

This query retrieves all Student entities from the database.

WHERE Clause

String hql = "FROM Student S WHERE S.firstName = :firstName";
Session session = HibernateUtil.getSessionFactory().openSession();
Query<Student> query = session.createQuery(hql, Student.class);
query.setParameter("firstName", "John");
List<Student> students = query.getResultList();
students.forEach(System.out::println);
session.close();

This query retrieves Student entities with a specific first name.

ORDER BY Clause

String hql = "FROM Student S ORDER BY S.lastName ASC";
Session session = HibernateUtil.getSessionFactory().openSession();
List<Student> students = session.createQuery(hql, Student.class).getResultList();
students.forEach(System.out::println);
session.close();

This query retrieves all Student entities and sorts them by last name in ascending order.

JOIN Clause

Assume we have another entity Course and a ManyToMany relationship with Student.

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

    @ManyToMany(mappedBy = "courses")
    private List<Student> students;

    // Constructors, getters, setters, and toString() method
}

@Entity
@Table(name = "student")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String firstName;
    private String lastName;
    private String email;

    @ManyToMany
    @JoinTable(
        name = "student_course",
        joinColumns = @JoinColumn(name = "student_id"),
        inverseJoinColumns = @JoinColumn(name = "course_id")
    )
    private List<Course> courses;

    // Constructors, getters, setters, and toString() method
}

String hql = "SELECT S FROM Student S JOIN S.courses C WHERE C.name = :courseName";
Session session = HibernateUtil.getSessionFactory().openSession();
Query<Student> query = session.createQuery(hql, Student.class);
query.setParameter("courseName", "Mathematics");
List<Student> students = query.getResultList();
students.forEach(System.out::println);
session.close();

This query retrieves Student entities enrolled in a specific course.

Advanced HQL Features

Aggregations

HQL supports aggregate functions like COUNT, SUM, AVG, MAX, and MIN.

String hql = "SELECT COUNT(S.id) FROM Student S";
Session session = HibernateUtil.getSessionFactory().openSession();
Long count = (Long) session.createQuery(hql).getSingleResult();
System.out.println("Total Students: " + count);
session.close();

This query counts the total number of Student entities.

Group By and Having

String hql = "SELECT S.lastName, COUNT(S.id) FROM Student S GROUP BY S.lastName HAVING COUNT(S.id) > 1";
Session session = HibernateUtil.getSessionFactory().openSession();
List<Object[]> results = session.createQuery(hql).getResultList();
for (Object[] result : results) {
    System.out.println("Last Name: " + result[0] + ", Count: " + result[1]);
}
session.close();

This query groups Student entities by last name and retrieves the count of students with the same last name, only if the count is greater than one.

Subqueries

String hql = "FROM Student S WHERE S.id IN (SELECT C.student.id FROM Course C WHERE C.name = :courseName)";
Session session = HibernateUtil.getSessionFactory().openSession();
Query<Student> query = session.createQuery(hql, Student.class);
query.setParameter("courseName", "Mathematics");
List<Student> students = query.getResultList();
students.forEach(System.out::println);
session.close();

This query retrieves Student entities enrolled in a specific course using a subquery.

HQL Functions and Operators

String Functions

  • concat()
  • length()
  • substring()
  • lower()
  • upper()
  • trim()

Example:

String hql = "SELECT CONCAT(S.firstName, ' ', S.lastName) FROM Student S";
Session session = HibernateUtil.getSessionFactory().openSession();
List<String> names = session.createQuery(hql).getResultList();
names.forEach(System.out::println);
session.close();

This query concatenates the first and last names of Student entities.

Arithmetic Operators

  • +, -, *, `/

,%`

Example:

String hql = "SELECT S FROM Student S WHERE S.id % 2 = 0";
Session session = HibernateUtil.getSessionFactory().openSession();
List<Student> students = session.createQuery(hql, Student.class).getResultList();
students.forEach(System.out::println);
session.close();

This query retrieves Student entities with even IDs.

Date Functions

  • current_date()
  • current_time()
  • current_timestamp()

Example:

String hql = "SELECT S FROM Student S WHERE S.registrationDate > current_date()";
Session session = HibernateUtil.getSessionFactory().openSession();
List<Student> students = session.createQuery(hql, Student.class).getResultList();
students.forEach(System.out::println);
session.close();

This query retrieves Student entities registered after the current date.

Conclusion

Hibernate Query Language (HQL) is a powerful and flexible query language that interacts with your database in an object-oriented manner. With Hibernate 6.4, you can efficiently manage and query your data using HQL. This guide covered the basics and some advanced features of HQL to help you get started and make the most of Hibernate in your applications. Happy coding!

Comments

Post a Comment

Leave Comment