mysql stored procedure Example with Spring data jpa

Create the table with Some Records

CREATE TABLE `emp_tab` (
  `eno` int NOT NULL,
  `ename` varchar(45) DEFAULT NULL,
  `dept` varchar(45) DEFAULT NULL,
  `salary` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`eno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert Some data

INSERT INTO `new`.`emp_tab` (`eno`, `ename`, `dept`, `salary`) VALUES ('1', 'SAM', 'IT', '5000');
INSERT INTO `new`.`emp_tab` (`eno`, `ename`, `dept`, `salary`) VALUES ('2', 'RAM', 'IT', '6000');
INSERT INTO `new`.`emp_tab` (`eno`, `ename`, `dept`, `salary`) VALUES ('3', 'Shaym', 'HR', '3000');

Create the Procedure

CREATE PROCEDURE  `GET_EMP_BY_DEPT`(IN dept1 varchar(20),IN dept2 varchar(20))
BEGIN
 SELECT * FROM emp_tab where dept in(dept1,dept2) order by dept;
END

Create Spring Boot project with below dependencies

spring data JPA,lombok,mysql driver

Directory Structure

pom.xml

<?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.5.3</version>
  <relativePath/> <!-- lookup parent from repository -->
 </parent>
 <groupId>com.example</groupId>
 <artifactId>SBProj-1</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <name>SBProj-1</name>
 <description>Demo project for Spring Boot</description>
 <url/>
 <licenses>
  <license/>
 </licenses>
 <developers>
  <developer/>
 </developers>
 <scm>
  <connection/>
  <developerConnection/>
  <tag/>
  <url/>
 </scm>
 <properties>
  <java.version>17</java.version>
 </properties>
 <dependencies>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</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.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <configuration>
     <annotationProcessorPaths>
      <path>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
      </path>
     </annotationProcessorPaths>
    </configuration>
   </plugin>
   <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>

application.properties

spring.application.name=SBProj-1
spring.datasource.url=jdbc:mysql://localhost:3306/new
spring.datasource.username=root
spring.datasource.password=root


spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

Create the Entity

package com.example.demo.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.Data;

@Data
@Entity
@Table(name = "emp_tab")
public class Employee {

 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 private Integer eno;

 private String ename;

 private String dept;

 private String salary;
}

Create the Service

package com.example.demo.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.example.demo.entity.Employee;

import jakarta.persistence.EntityManager;
import jakarta.persistence.ParameterMode;
import jakarta.persistence.StoredProcedureQuery;

@Service("empService")
public class EmployeeMgmtService {

 @Autowired
 private EntityManager manager;


 public List<Object> fetchEmpDetailsByDesg(String dept1,String dept2){
  //create the SP Object
  StoredProcedureQuery query = manager.createStoredProcedureQuery("GET_EMP_BY_DEPT", Employee.class);
  //register the Parameters with JDBC types
  query.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
  query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
  //set values to IN Param
  query.setParameter(1, dept1);
  query.setParameter(2, dept2);
  //call the PL/SQL procedure and get the result
  List<Object> list = query.getResultList();
  return list;
 }

}

Create the Runner

package com.example.demo.runner;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import com.example.demo.service.EmployeeMgmtService;

@Component
public class CallingPLSQLProcedureTestRunner implements CommandLineRunner{

 @Autowired
 private EmployeeMgmtService employeeMgmtService;

 @Override
 public void run(String... args) throws Exception {
  employeeMgmtService.fetchEmpDetailsByDesg("IT", "HR").forEach(emp->{
   System.out.println(emp);
  });
 }

}