Hibernate Stored Procedure Example with CRUD Operations

📘 Premium Read: Access my best content on Medium member-only articles — deep dives into Java, Spring Boot, Microservices, backend architecture, interview preparation, career advice, and industry-standard best practices.

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (176K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

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.