Call Stored Procedures using Spring Boot JPA | Code Factory

Reference Link : Link

Donate : Link

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>2.2.0.RELEASE</version>
  <relativePath/> <!-- lookup parent from repository -->
 </parent>
 <groupId>com.codeFactory</groupId>
 <artifactId>spring-boot-call-stored-procedures</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <name>spring-boot-call-stored-procedures</name>
 <description>Demo project for Spring Boot Call Stored Procedure</description>
<properties>
  <java.version>1.8</java.version>
 </properties>
<dependencies>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  
  <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
  
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-test</artifactId>
   <scope>test</scope>
   <exclusions>
    <exclusion>
     <groupId>org.junit.vintage</groupId>
     <artifactId>junit-vintage-engine</artifactId>
    </exclusion>
   </exclusions>
  </dependency>
 </dependencies>
<build>
  <plugins>
   <plugin>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-maven-plugin</artifactId>
   </plugin>
  </plugins>
 </build>
</project>

application.properties

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root

SpringBootCallStoredProceduresApplication.java

package com.codeFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
 * @author code.factory
 *
 */
@SpringBootApplication
public class SpringBootCallStoredProceduresApplication {
public static void main(String[] args) {
  SpringApplication.run(SpringBootCallStoredProceduresApplication.class, args);
 }
}

EmployeeController.java

package com.codeFactory.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
import com.codeFactory.model.Employee;
import com.codeFactory.service.EmployeeService;
/**
 * @author code.factory
 *
 */
@RestController
public class EmployeeController {
@Autowired
 EmployeeService employeeService;
 
 @GetMapping("getEmpById/{id}")
 public Employee getEmpById(@PathVariable("id") Integer id) {
  return employeeService.getEmpById(id);
 }
}

Employee.java

package com.codeFactory.model;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedStoredProcedureQueries;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureParameter;
import javax.persistence.Table;
/**
 * @author code.factory
 *
 */
@Entity
@Table(name = "employee")
@NamedStoredProcedureQueries({
 @NamedStoredProcedureQuery(
  name = "findEmpById",
  procedureName = "FIND_EMP_BY_ID",
  resultClasses = {Employee.class},
  parameters = {
   @StoredProcedureParameter(
    name = "p_id",
    type = Integer.class,
    mode = ParameterMode.IN
   )
  }
 )
})
public class Employee {
@Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 int id;
 String name;
 long salary;
public Employee() {
  
 }
 
 public Employee(int id, String name, long salary) {
  this.id = id;
  this.name = name;
  this.salary = salary;
 }
 
 public int getId() {
  return id;
 }
public void setId(int id) {
  this.id = id;
 }
public String getName() {
  return name;
 }
public void setName(String name) {
  this.name = name;
 }
public long getSalary() {
  return salary;
 }
public void setSalary(long salary) {
  this.salary = salary;
 }
@Override
 public String toString() {
  return "id=" + id + ", name=" + name + ", salary=" + salary;
 }
}

EmployeeRepository.java

package com.codeFactory.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.codeFactory.model.Employee;
/**
 * @author code.factory
 *
 */
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Integer>, EmployeeRepositoryCustom {
 
}

EmployeeRepositoryCustom.java

package com.codeFactory.repository;
import com.codeFactory.model.Employee;
/**
 * @author code.factory
 *
 */
public interface EmployeeRepositoryCustom {
public Employee findEmpById(int p_id);
}

EmployeeRepositoryImpl.java

package com.codeFactory.repository;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;
import com.codeFactory.model.Employee;
/**
 * @author code.factory
 *
 */
public class EmployeeRepositoryImpl implements EmployeeRepositoryCustom {
@PersistenceContext
 EntityManager em;
 
 @Override
 public Employee findEmpById(int p_id) {
  StoredProcedureQuery query = em.createNamedStoredProcedureQuery("findEmpById").setParameter("p_id", p_id);
  return (Employee) query.getResultList().get(0);
 }
}

EmployeeService.java

package com.codeFactory.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.codeFactory.model.Employee;
import com.codeFactory.repository.EmployeeRepository;
/**
 * @author code.factory
 *
 */
@Service
public class EmployeeService {
@Autowired
 EmployeeRepository employeeRepository;
 
 public Employee getEmpById(int id) {
  return employeeRepository.findEmpById(id);
 }
}

Create Table 'employee' :

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `salary` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
);

Procedure 'FIND_EMP_BY_ID' :

CREATE DEFINER=`root`@`localhost` PROCEDURE `FIND_EMP_BY_ID`(in p_id int)
BEGIN
 SELECT id, name, salary
    FROM employee
    WHERE id = p_id;
END