Hibernate Stored Procedure Example with CRUD Operations

Author:
In this tutorial, we will demonstrate how to use Hibernate to call stored procedures in a MySQL database using the Employee entity. We will create a simple application to manage Employee entities and perform CRUD operations through stored procedures.

Prerequisites

Before we start, ensure you have the following:

  • Java Development Kit (JDK) installed
  • Apache Maven installed
  • MySQL database installed and running
  • An IDE (such as IntelliJ IDEA, Eclipse, or VS Code) installed

Step 1: Setting Up the Hibernate Project

1.1 Create a Maven Project

  1. Open your IDE and create a new Maven project.

  2. Configure the pom.xml file:

Add the following dependencies to your 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>

    <groupId>com.example</groupId>
    <artifactId>hibernate-stored-procedure</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>6.4.0.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-hikaricp</artifactId>
            <version>6.4.0.Final</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.32</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>1.7.32</version>
        </dependency>
    </dependencies>

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

1.2 Configure Hibernate

Create a file named hibernate.cfg.xml in the src/main/resources directory with the following content:

<!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/your_database_name</property>
        <property name="hibernate.connection.username">your_username</property>
        <property name="hibernate.connection.password">your_password</property>
        <property name="hibernate.hbm2ddl.auto">update</property>
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.format_sql">true</property>
    </session-factory>
</hibernate-configuration>

Replace your_database_name, your_username, and your_password with your MySQL database credentials.

1.3 Create the Employee Entity

Create an Employee class in the com.example.hibernateexamples.model package:

package com.example.hibernateexamples.model;

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

1.4 Create the Hibernate Utility Class

Create a HibernateUtil class in the com.example.hibernateexamples.util package:

package com.example.hibernateexamples.util;

import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;

public class HibernateUtil {

    private static final SessionFactory sessionFactory = buildSessionFactory();

    private static SessionFactory buildSessionFactory() {
        try {
            Configuration configuration = new Configuration();
            configuration.configure("hibernate.cfg.xml");
            return configuration.buildSessionFactory(new StandardServiceRegistryBuilder()
                    .applySettings(configuration.getProperties()).build());
        } 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();
    }
}

Step 2: Creating Stored Procedures in MySQL

2.1 Create Stored Procedures

Connect to your MySQL database and create the following stored procedures:

DELIMITER //

CREATE PROCEDURE insert_employee(IN emp_name VARCHAR(50), IN emp_dept VARCHAR(50), IN emp_salary DOUBLE)
BEGIN
    INSERT INTO Employee(name, department, salary) VALUES (emp_name, emp_dept, emp_salary);
END //

CREATE PROCEDURE get_all_employees()
BEGIN
    SELECT * FROM Employee;
END //

CREATE PROCEDURE update_employee(IN emp_id BIGINT, IN emp_name VARCHAR(50), IN emp_dept VARCHAR(50), IN emp_salary DOUBLE)
BEGIN
    UPDATE Employee SET name = emp_name, department = emp_dept, salary = emp_salary WHERE id = emp_id;
END //

CREATE PROCEDURE delete_employee(IN emp_id BIGINT)
BEGIN
    DELETE FROM Employee WHERE id = emp_id;
END //

DELIMITER ;

Step 3: Performing CRUD Operations with Stored Procedures

3.1 INSERT Operation

Create an EmployeeDao class in the com.example.hibernateexamples.dao package to handle database operations:

package com.example.hibernateexamples.dao;

import com.example.hibernateexamples.model.Employee;
import com.example.hibernateexamples.util.HibernateUtil;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.query.Query;

import java.util.List;

public class EmployeeDao {

    public void insertEmployee(Employee employee) {
        Transaction transaction = null;
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            transaction = session.beginTransaction();
            Query query = session.createNativeQuery("CALL insert_employee(:name, :department, :salary)");
            query.setParameter("name", employee.getName());
            query.setParameter("department", employee.getDepartment());
            query.setParameter("salary", employee.getSalary());
            query.executeUpdate();
            transaction.commit();
        } catch (Exception e) {
            if (transaction != null) {
                transaction.rollback();
            }
            e.printStackTrace();
        }
    }
}

3.2 SELECT Operation

Add a method to the EmployeeDao class to retrieve all employees:

@SuppressWarnings("unchecked")
public List<Employee> getAllEmployees() {
    try (Session session = HibernateUtil.getSessionFactory().openSession()) {
        Query<Employee> query = session.createNativeQuery("CALL get_all_employees()", Employee.class);
        return query.getResultList();
    }
}

3.3 UPDATE Operation

Add a method to the EmployeeDao class to update an employee:

public void updateEmployee(Employee employee) {
    Transaction transaction = null;
    try (Session session = HibernateUtil.getSessionFactory().openSession()) {
        transaction = session.beginTransaction();
        Query query = session.createNativeQuery("CALL update_employee(:id, :name, :department, :salary)");
        query.setParameter("id", employee.getId());
        query.setParameter("name", employee.getName());
        query.setParameter("department", employee.getDepartment());
        query.setParameter("salary", employee.getSalary());
        query.executeUpdate();
        transaction.commit();
    } catch (Exception e) {
        if (transaction != null) {
            transaction.rollback();
        }
        e.printStackTrace();
    }
}

3.4 DELETE Operation

Add a method to the EmployeeDao class to delete an employee:

public void deleteEmployee(Long id) {
    Transaction transaction =

 null;
    try (Session session = HibernateUtil.getSessionFactory().openSession()) {
        transaction = session.beginTransaction();
        Query query = session.createNativeQuery("CALL delete_employee(:id)");
        query.setParameter("id", id);
        query.executeUpdate();
        transaction.commit();
    } catch (Exception e) {
        if (transaction != null) {
            transaction.rollback();
        }
        e.printStackTrace();
    }
}

Step 4: Testing the CRUD Operations

Create a Main class in the com.example.hibernateexamples package to test the CRUD operations:

package com.example.hibernateexamples;

import com.example.hibernateexamples.dao.EmployeeDao;
import com.example.hibernateexamples.model.Employee;

import java.util.List;

public class Main {
    public static void main(String[] args) {
        EmployeeDao employeeDao = new EmployeeDao();

        // Insert an employee
        Employee employee1 = new Employee();
        employee1.setName("John Doe");
        employee1.setDepartment("IT");
        employee1.setSalary(70000.00);
        employeeDao.insertEmployee(employee1);

        // Insert another employee
        Employee employee2 = new Employee();
        employee2.setName("Jane Smith");
        employee2.setDepartment("HR");
        employee2.setSalary(60000.00);
        employeeDao.insertEmployee(employee2);

        // Select all employees
        List<Employee> employees = employeeDao.getAllEmployees();
        employees.forEach(e -> System.out.println(e.getName() + " - " + e.getDepartment() + " - " + e.getSalary()));

        // Update an employee
        employee1.setSalary(75000.00);
        employeeDao.updateEmployee(employee1);

        // Select all employees again
        employees = employeeDao.getAllEmployees();
        employees.forEach(e -> System.out.println(e.getName() + " - " + e.getDepartment() + " - " + e.getSalary()));

        // Delete an employee
        employeeDao.deleteEmployee(employee2.getId());

        // Select all employees again
        employees = employeeDao.getAllEmployees();
        employees.forEach(e -> System.out.println(e.getName() + " - " + e.getDepartment() + " - " + e.getSalary()));
    }
}

Conclusion

In this tutorial, we set up a simple Hibernate project using Maven and demonstrated how to perform basic CRUD operations using stored procedures in a MySQL database. We created an Employee entity and an EmployeeDao class to handle database operations using stored procedures. We tested the CRUD operations in a Main class. By following this structure, you can extend and customize the application as needed.

Comments

Post a Comment

Leave Comment