Hibernate Native Query Pagination using MySQL Database

Author:
This tutorial will guide you through setting up and demonstrating the use of native SQL queries with pagination in Hibernate using a MySQL database. We will use the Employee entity for this example.

Introduction

Pagination in Hibernate allows you to retrieve a subset of results from a large dataset, which is useful for implementing features like displaying data in pages. Native SQL queries in Hibernate can be used to perform pagination queries that return specific subsets of data based on page size and page number.

In this tutorial, we will:

  1. Set up a Maven project with Hibernate and MySQL dependencies.
  2. Configure Hibernate.
  3. Create an entity class (Employee).
  4. Implement examples of native SQL queries with pagination.
  5. Demonstrate native SQL queries with pagination using a sample application.

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 the necessary 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://www.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>hibernate-native-query-pagination</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>

1.3 Configure Hibernate

Create a file named hibernate.cfg.xml in the src/main/resources directory to configure Hibernate. This file contains the 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>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <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>
        <property name="hibernate.hbm2ddl.auto">update</property>
        <property name="hibernate.show_sql">true</property>
    </session-factory>
</hibernate-configuration>

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

Explanation:

  • hibernate.dialect specifies the SQL dialect to be used.
  • hibernate.connection.driver_class specifies the JDBC driver class.
  • hibernate.connection.url specifies the JDBC URL for the database connection.
  • hibernate.connection.username and hibernate.connection.password specify the database credentials.
  • hibernate.hbm2ddl.auto specifies the schema generation strategy.
  • hibernate.show_sql specifies whether to show SQL statements in the logs.

Step 2: Create the Entity Class

Create an entity class Employee 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 Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String department;
    private double salary;

    // 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 String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }
}

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.

Step 3: Implement Native SQL Query with Pagination

Create a class EmployeeService to handle database operations using native SQL queries with pagination. This class includes methods to demonstrate native SQL queries with pagination.

Get Paginated Employees

package com.example.service;

import com.example.entity.Employee;
import com.example.util.HibernateUtil;
import org.hibernate.Session;
import org.hibernate.query.NativeQuery;

import java.util.List;

public class EmployeeService {

    public List<Employee> getPaginatedEmployees(int pageNumber, int pageSize) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        List<Employee> employees = null;

        try {
            String sql = "SELECT * FROM Employee";
            NativeQuery<Employee> query = session.createNativeQuery(sql, Employee.class);
            query.setFirstResult((pageNumber - 1) * pageSize);
            query.setMaxResults(pageSize);
            employees = query.getResultList();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }

        return employees;
    }
}

Explanation:

  • The getPaginatedEmployees method uses a native SQL SELECT statement to retrieve employees with pagination.
  • The setFirstResult method sets the offset of the first result to retrieve.
  • The setMaxResults method sets the maximum number of results to retrieve.

Step 4: Demonstrate Native SQL Query with Pagination

Create a MainApp class to demonstrate native SQL queries with pagination. This class calls the getPaginatedEmployees method of EmployeeService.

package com.example.main;

import com.example.entity.Employee;
import com.example.service.EmployeeService;

import java.util.List;

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

        // Get first page of employees with page size 5
        List<Employee> employeesPage1 = employeeService.getPaginatedEmployees(1, 5);
        System.out.println("Page 1:");
        for (Employee employee : employeesPage1) {
            System.out.println("ID: " + employee.getId() + ", Name: " + employee.getName() + ", Department: " + employee.getDepartment() + ", Salary: " + employee.getSalary());
        }

        // Get second page of employees with page size 5
        List<Employee> employeesPage2 = employeeService.getPaginatedEmployees(2, 5);
        System.out.println("Page 2:");
        for (Employee employee : employeesPage2) {
            System.out.println("ID: " + employee.getId() + ", Name: " + employee.getName() + ", Department: " + employee.getDepartment() + ", Salary: " + employee.getSalary());
        }
    }
}

Explanation:

  1. Create a EmployeeService Instance:

    EmployeeService employeeService = new EmployeeService();

    An instance of EmployeeService is created to call its methods for performing database operations.

  2. Get First Page of Employees:

    List<Employee> employeesPage1 = employeeService.getPaginatedEmployees(1, 5);

    The getPaginatedEmployees method is called to retrieve the first page of employees with a page size of 5.

  3. Print the First Page of Employees:

    System.out.println("Page 1:");
for (Employee employee : employeesPage1) {
    System.out.println("ID: " + employee.getId() + ", Name: " + employee.getName() + ", Department: " + employee.getDepartment() + ", Salary: " + employee.getSalary());
}

    The retrieved employees for the first page are printed to the console.

  4. Get Second Page of Employees:

    List<Employee> employeesPage2 = employeeService.getPaginatedEmployees(2, 5);

    The getPaginatedEmployees method is called to retrieve the second page of employees with a page size of 5.

  1. Print the Second Page of Employees:

    System.out.println("Page 2:");
for (Employee employee : employeesPage2) {
    System.out.println("ID: " + employee.getId() + ", Name: " + employee.getName() + ", Department: " + employee.getDepartment() + ", Salary: " + employee.getSalary());
}

    The retrieved employees for the second page are printed to the console.

Sample Output

When you run the MainApp class, you should see the following output (assuming there are enough employees in the database):

Page 1:
ID: 1, Name: John Doe, Department: IT, Salary: 5000.0
ID: 2, Name: Jane Smith, Department: HR, Salary: 6000.0
ID: 3, Name: Robert Brown, Department: Sales, Salary: 5500.0
ID: 4, Name: Emily White, Department: IT, Salary: 5200.0
ID: 5, Name: Michael Green, Department: Marketing, Salary: 4900.0

Page 2:
ID: 6, Name: Laura Black, Department: Finance, Salary: 5300.0
ID: 7, Name: David King, Department: IT, Salary: 5800.0
ID: 8, Name: Susan Wright, Department: HR, Salary: 5700.0
ID: 9, Name: James Hall, Department: Sales, Salary: 5400.0
ID: 10, Name: Linda Scott, Department: Marketing, Salary: 5100.0

This output indicates that the employees were successfully retrieved using a native SQL query with pagination.

Conclusion

In this tutorial, we have successfully demonstrated how to perform pagination using native SQL queries in Hibernate. We set up a Hibernate project, configured Hibernate, created an entity class, implemented a native SQL query with pagination, and demonstrated the query with a sample application. This guide provides a solid foundation for using native SQL queries to perform pagination in your Hibernate-based applications.

Comments

Post a Comment

Leave Comment