Hibernate Query Language Tutorial

Author:
This hibernate query language tutorial shows how to use HQL in hibernate applications with examples.

Hibernate Query Language (HQL) is an object-oriented query language, similar to SQL, but instead of operating on tables and columns, HQL works with persistent objects and their properties. HQL queries are translated by Hibernate into conventional SQL queries, which in turn perform an action on a database.
Although you can use SQL statements directly with Hibernate using Native SQL, I would recommend using HQL whenever possible to avoid database portability hassles and to take advantage of Hibernate's SQL generation and caching strategies.
Keywords like SELECT, FROM, and WHERE, etc., are not case sensitive, but properties like table and column names are case sensitive in HQL.

Hibernate Query Language Basics

Before building a complete Hibernate application with HQL CRUD operations, let's first familiarize ourselves with HQL basics.

FROM Clause

You will use FROM clause if you want to load complete persistent objects into memory. Following is the simple syntax of using FROM clause −
String hql = "FROM Student";
Query query = session.createQuery(hql);
List results = query.list();
If you need to fully qualify a class name in HQL, just specify the package and class name as follows −
String hql = "FROM net.javaguides.hibernate.entity.Student";
Query query = session.createQuery(hql);
List results = query.list();

AS Clause

The AS clause can be used to assign aliases to the classes in your HQL queries, especially when you have long queries. For instance, our previous simple example would be the following −
String hql = "FROM Student AS S";
Query query = session.createQuery(hql);
List results = query.list();
The AS keyword is optional and you can also specify the alias directly after the class name, as follows −
String hql = "FROM Student S";
Query query = session.createQuery(hql);
List results = query.list();

SELECT Clause

The SELECT clause provides more control over the result set than the from clause. If you want to obtain a few properties of objects instead of the complete object, use the SELECT clause. 
Following is the simple syntax of using the SELECT clause to get a just first_name field of the Student object −
String hql = "SELECT S.firstName FROM Student S";
Query query = session.createQuery(hql);
List results = query.list();
It is notable here that S.firstName is a property of a Student object rather than a field of the Student table.

WHERE Clause

If you want to narrow the specific objects that are returned from storage, you use the WHERE clause. Following is the simple syntax of using WHERE clause −
String hql = "FROM Student S WHERE S.id = 10";
Query query = session.createQuery(hql);
List results = query.list();

ORDER BY Clause

To sort your HQL query's results, you will need to use the ORDER BY clause. You can order the results by any property on the objects in the result set either ascending (ASC) or descending (DESC). Following is the simple syntax of using ORDER BY clause −
String hql = "FROM Student S WHERE S.id > 10 ORDER BY S.email DESC";
Query query = session.createQuery(hql);
List results = query.list();
If you wanted to sort by more than one property, you would just add the additional properties to the end of the order by clause, separated by commas as follows −
String hql = "FROM Student S WHERE S.id > 10 " +
             "ORDER BY S.firstName DESC, S.lastName DESC ";
Query query = session.createQuery(hql);
List results = query.list();

GROUP BY Clause

This clause lets Hibernate pull information from the database and the group based on the value of an attribute and, typically, use the result to include an aggregate value. 
Following is the simple syntax of using GROUP BY clause −
String hql = "SELECT SUM(S.is), E.firtName FROM Student S " +
             "GROUP BY S.college";
Query query = session.createQuery(hql);
List results = query.list();

Using Named Parameters

Hibernate supports named parameters in its HQL queries. This makes writing HQL queries that accept input from the user easy and you do not have to defend against SQL injection attacks. 
Following is the simple syntax of using named parameters −
String hql = "FROM Student S WHERE S.id = :student_id";
Query query = session.createQuery(hql);
query.setParameter("student_id",10);
List results = query.list();

UPDATE Clause

Bulk updates are new to HQL with Hibernate 3 and delete work differently in Hibernate 3 than they did in Hibernate 2. The Query interface now contains a method called executeUpdate() for executing HQL UPDATE or DELETE statements.
The UPDATE clause can be used to update one or more properties of one or more objects. Following is the simple syntax of using UPDATE clause −
String hql = "UPDATE Student set firstName= :firstName "  + 
             "WHERE id = :student_id";
Query query = session.createQuery(hql);
query.setParameter("firstName", 1000);
query.setParameter("student_id", 10);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

DELETE Clause

The DELETE clause can be used to delete one or more objects. 
Following is the simple syntax of using DELETE clause −
String hql = "DELETE FROM Student "  + 
             "WHERE id = :student_id";
Query query = session.createQuery(hql);
query.setParameter("student_id", 10);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

INSERT Clause

HQL supports INSERT INTO clause only where records can be inserted from one object to another object. 
Following is the simple syntax of using INSERT INTO clause −
String hql = "INSERT INTO Student(firstName, lastName, email)"  + 
             "SELECT firstName, lastName, email FROM old_student";
Query query = session.createQuery(hql);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

Aggregate Methods

HQL supports a range of aggregate methods, similar to SQL. They work the same way in HQL as in SQL and the following is the list of the available functions −
  1. avg(property name) - The average of a property's value
  2. count(property name or *) - The number of times a property occurs in the results
  3. max(property name) - The maximum value of the property values
  4. min(property name) - The minimum value of the property values
  5. sum(property name) - The sum total of the property values
The distinct keyword only counts the unique values in the row set. The following query will return only unique count −
String hql = "SELECT count(distinct E.firstName) FROM Student S";
Query query = session.createQuery(hql);
List results = query.list();

Pagination using Query

There are two methods of the Query interface for pagination.
  1. Query setFirstResult(int startPosition) - This method takes an integer that represents the first row in your result set, starting with row 0.
  2. Query setMaxResults(int maxResult) - This method tells Hibernate to retrieve a fixed number maxResults of objects.
Using the above two methods together, we can construct a paging component in our web or Swing application. Following is the example, which you can extend to fetch 10 rows at a time −
String hql = "FROM Student";
Query query = session.createQuery(hql);
query.setFirstResult(1);
query.setMaxResults(10);
List results = query.list();

Build a Hibernate Application with HQL CRUD Example

Let's develop a complete example to demonstrate INSERT, UPDATE, SELECT, and DELETE HQL operations with an example.

Technologies and tools used

  • Hibernate 6.1.7.Final
  • IDE - Eclipse
  • Maven 3.5.3
  • JavaSE 17
  • MySQL - 8.0.32
Let's start developing step by step Hibernate application using Maven as a project management and build tool.

Development Steps

  1. Create a Simple Maven Project
  2. Project Directory Structure
  3. Add jar Dependencies to pom.xml
  4. Creating the JPA Entity Class(Persistent class)
  5. Create a Hibernate configuration file - Java Configuration
  6. Create StudentDao Class - INSERT, UPDATE, SELECT, and DELETE HQL operations
  7. Create the Main class and Run an Application

1. Create a Simple Maven Project

Use the How to Create a Simple Maven Project in Eclipse article to create a simple Maven project in Eclipse IDE.

2. Project Directory Structure

The project directory structure for your reference - 

3. Add jar Dependencies to pom.xml

<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>
    <parent>
        <groupId>net.javaguides.hibernate</groupId>
        <artifactId>hibernate-tutorial</artifactId>
        <version>0.0.1-SNAPSHOT</version>
    </parent>
    <artifactId>hibernate-hql-crud-example</artifactId>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.32</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>6.1.7.Final</version>
        </dependency>
    </dependencies>
    <build>
        <sourceDirectory>src/main/java</sourceDirectory>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.5.1</version>
                <configuration>
                    <source>17</source>
                    <target>17</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

4. Creating the JPA Entity Class(Persistent class)

Let's create a Student entity class under net.javaguides.hibernate.entity package with the following code:
package net.javaguides.hibernate.entity;

import jakarta.persistence.*;

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

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "email")
    private String email;

    public Student() {

    }

    public Student(String firstName, String lastName, String email) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
    }

    public int getId() {
        return id;
    }

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

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "Student [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", email=" + email + "]";
    }
}

5. Create a Hibernate configuration file - Java Configuration

The HibernateUtil Java configuration file contains information about the database and mapping file.
Let's create a HibernateUtil file and write the following code in it.
package net.javaguides.hibernate.util;

import java.util.Properties;

import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.Environment;
import org.hibernate.service.ServiceRegistry;

import net.javaguides.hibernate.entity.Student;

public class HibernateUtil {
    private static SessionFactory sessionFactory;
    public static SessionFactory getSessionFactory() {
        if (sessionFactory == null) {
            try {
                Configuration configuration = new Configuration();

                // Hibernate settings equivalent to hibernate.cfg.xml's properties
                Properties settings = new Properties();
                settings.put(Environment.DRIVER, "com.mysql.cj.jdbc.Driver");
                settings.put(Environment.URL, "jdbc:mysql://localhost:3306/hibernate_db?useSSL=false");
                settings.put(Environment.USER, "root");
                settings.put(Environment.PASS, "root");
                settings.put(Environment.SHOW_SQL, "true");

                settings.put(Environment.CURRENT_SESSION_CONTEXT_CLASS, "thread");

                settings.put(Environment.HBM2DDL_AUTO, "create-drop");

                configuration.setProperties(settings);

                configuration.addAnnotatedClass(Student.class);

                ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
                    .applySettings(configuration.getProperties()).build();

                sessionFactory = configuration.buildSessionFactory(serviceRegistry);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return sessionFactory;
    }
}

Create StudentDao Class - INSERT, UPDATE, SELECT, and DELETE HQL operations

Let's create a separate StudentDao class with the following code:
package net.javaguides.hibernate.dao;

import java.util.List;

import jakarta.persistence.Query;

import org.hibernate.Session;
import org.hibernate.Transaction;

import net.javaguides.hibernate.entity.Student;
import net.javaguides.hibernate.util.HibernateUtil;

public class StudentDao {

    public void insertStudent() {
        Transaction transaction = null;
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            // start a transaction
            transaction = session.beginTransaction();

            String hql = "INSERT INTO Student (firstName, lastName, email) " +
                "SELECT firstName, lastName, email FROM Student";
            Query query = session.createQuery(hql);
            int result = query.executeUpdate();
            System.out.println("Rows affected: " + result);

            // commit transaction
            transaction.commit();
        } catch (Exception e) {
            if (transaction != null) {
                transaction.rollback();
            }
            e.printStackTrace();
        }
    }

    public void updateStudent(Student student) {
        Transaction transaction = null;
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            // start a transaction
            transaction = session.beginTransaction();

            // save the student object
            String hql = "UPDATE Student set firstName = :firstName " + "WHERE id = :studentId";
            Query query = session.createQuery(hql);
            query.setParameter("firstName", student.getFirstName());
            query.setParameter("studentId", 1);
            int result = query.executeUpdate();
            System.out.println("Rows affected: " + result);

            // commit transaction
            transaction.commit();
        } catch (Exception e) {
            if (transaction != null) {
                transaction.rollback();
            }
            e.printStackTrace();
        }
    }

    public void deleteStudent(int id) {

        Transaction transaction = null;
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            // start a transaction
            transaction = session.beginTransaction();

            // Delete a student object
            Student student = session.get(Student.class, id);
            if (student != null) {
                String hql = "DELETE FROM Student " + "WHERE id = :studentId";
                Query query = session.createQuery(hql);
                query.setParameter("studentId", id);
                int result = query.executeUpdate();
                System.out.println("Rows affected: " + result);
            }

            // commit transaction
            transaction.commit();
        } catch (Exception e) {
            if (transaction != null) {
                transaction.rollback();
            }
            e.printStackTrace();
        }
    }

    public Student getStudent(int id) {

        Transaction transaction = null;
        Student student = null;
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            // start a transaction
            transaction = session.beginTransaction();

            // get an student object
            String hql = " FROM Student S WHERE S.id = :studentId";
            Query query = session.createQuery(hql);
            query.setParameter("studentId", id);
            List results = query.getResultList();

            if (results != null && !results.isEmpty()) {
                student = (Student) results.get(0);
            }
            // commit transaction
            transaction.commit();
        } catch (Exception e) {
            if (transaction != null) {
                transaction.rollback();
            }
            e.printStackTrace();
        }
        return student;
    }

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

7. Create the main App class and Run an Application

Let's test Hibernate application to connect to the MySQL database.
package net.javaguides.hibernate;

import java.util.List;

import net.javaguides.hibernate.dao.StudentDao;
import net.javaguides.hibernate.entity.Student;

public class App {
    public static void main(String[] args) {
        StudentDao studentDao = new StudentDao();
        Student student = new Student("Ramesh", "Fadatare", "[email protected]");
        studentDao.saveStudent(student);

        studentDao.insertStudent();

        // update student
        Student student1 = new Student("Ram", "Fadatare", "[email protected]");
        studentDao.updateStudent(student1);

        // get students
        List < Student > students = studentDao.getStudents();
        students.forEach(s - > System.out.println(s.getFirstName()));

        // get single student
        Student student2 = studentDao.getStudent(1);
        System.out.println(student2.getFirstName());

        // delete student
        studentDao.deleteStudent(1);
    }
}

Output


GitHub Repository

The complete source code of this article is available on my GitHub Repository - https://github.com/RameshMF/Hibernate-ORM-Tutorials

Conclusion

In this tutorial, we have discussed what is HQL, HQL basics, and how to create Hibernate application with Hibernate Query Language INSERT, UPDATE, SELECT, and DELETE statements.
You can learn more about Hibernate ORM Framework at Hibernate Tutorial.

Comments

Post a Comment

Leave Comment