Configuring Store Procedure in Spring Boot Application

The Store Procedure is a prepared SQL statement that is used over and over again (Reusable code). So, if we have an SQL query that we need to write over and over again, we can save it as a Stored Procedure, and then just call it to execute it.

We can also pass parameters to a Stored Procedure so that the stored procedure can act based on the parameter values that has passed. In the spring boot application, we can store it in the repository layer and the procedure will be when needed.

Step-by-Step Implementation to Configure Store Procedure in Spring Boot Application

Below are the steps to configure the Store Procedure in a simple Spring Boot Application.

Step 1: We need a MySQL driver and JPA dependency.

Note: Here we are using MySQL 8 workbench.

Below is the XML file where we have added all the necessary dependencies. Here, we just need to configure JPA and MySQL driver dependency only in the pom.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>org.techous</groupId>
    <artifactId>procedure-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>procedure-demo</name>
    <description>master slave project for Spring Boot</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

Step 2: Now, configure properties in the application.properties file.

server.port=8081
# DataSource configuration
spring.datasource.url=jdbc:mysql://localhost:8084/master
spring.datasource.username=root
spring.datasource.password=tisha
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# JPA (Java Persistence API) configuration
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
# create,update,delete
spring.jpa.hibernate.ddl-auto=update

Step 3: Now, make Store Procedure. This is the basic type of procedure we can make according to our requirements.

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetEmployeeById`(IN empId INT)
BEGIN
    SELECT * FROM employee WHERE id = empId;
END

Below we can see the GetEmployeeById Store Procedure database in MYSQL workbench.

Step 4: Now implement an entity class named Employee.

Employee.java:

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.*;

// Declaring the Employee class
@Data
@Entity
public class Employee {

    // Declaring fields with annotations
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String department;
    private double salary;

}

This is an employee class with needed fields.

Step 5: Now, let's create a repository class for employee.

EmployeeRepository.java:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.techous.procedure.demo.model.Employee;

import java.util.List;

// Declaring the EmployeeRepository interface
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
  
    // Declaring a method to call a stored procedure named "GetEmployeeById"
    @Procedure(name = "GetEmployeeById")
    List<Employee> getEmployeeById(@Param("empId") Long empId);
}

In the repository layer, we need to provide the procedure name using @Procedure annotation with the respective database as we define in the properties file. Through @Procedure annotation, it will automatically call our store procedure when we call the methods.

Step 6: Now, after creating repository layer, create Service class for employee.

EmployeeService.java:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.techous.procedure.demo.model.Employee;
import org.techous.procedure.demo.repository.EmployeeRepository;

import java.util.List;

// Declaring the EmployeeService class
@Transactional
@Service
public class EmployeeService {

    // Autowiring the EmployeeRepository dependency
    @Autowired
    private EmployeeRepository employeeRepository;

    // Method to retrieve employee by ID using stored procedure
    public List<Employee> getEmployeeById(Long empId) {
        return employeeRepository.getEmployeeById(empId);
    }
    
    // Method to save an employee
    public Employee saveEmp(Employee employee){
        return employeeRepository.save(employee);
    }
}

This EmployeeService class describes the service methods for communicating with the Employee entity. The @Service annotation indicates that this class is a service component in the Spring application context, and the @Transactional annotation ensures that the methods are executed in the context of the @Autowired context inserts an EmployeeRepository instance into the service. The getEmployeeById method retrieves an employee by ID using a stored method defined in the repository, and the saveEmp method saves an employee using the repository's save method.

Step 7: Now, create Controller class

EmployeeController.java:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.techous.procedure.demo.model.Employee;
import org.techous.procedure.demo.service.EmployeeService;

import java.util.List;

@RestController
@RequestMapping("/api")
public class EmployeeController {

    @Autowired
    private EmployeeService employeeService;

    // Endpoint to retrieve employees by ID
    @GetMapping("/employees/{id}")
    public List<Employee> getEmployeeById(@PathVariable Long id) {
        return employeeService.getEmployeeById(id);
    }

    // Endpoint to save an employee
    @PostMapping("/saveEmp")
    public Employee save(@RequestBody Employee employee){
        return employeeService.saveEmp(employee);
    }
}

This controller class defines RESTful endpoints to control administrator-related functions. Uses EmployeeService to provide business logic. The @RestController statement specifies that this class is a RESTful controller, and the @RequestMapping statement specifies the base URL mapping for all endpoints defined in this class. The @GetMapping and @PostMapping annotations define HTTP GET and POST endpoints, respectively, that are retrieved and stored with an operator ID. These endpoints map to corresponding methods in the EmployeeService class.

Output: Now run this code See output here.

Add Employee:

Get employee by their ID using store procedure:

We can make any type of procedure and we can call using the Spring boot application. We need not write a long query here.