Call Stored Procedures using Spring Boot JPA | Code Factory

<?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>
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
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);
}
}
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);
}
}
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;
}
}
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 {

}
package com.codeFactory.repository;import com.codeFactory.model.Employee;/**
* @author code.factory
*
*/
public interface EmployeeRepositoryCustom {
public Employee findEmpById(int p_id);}
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);
}
}
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` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`salary` bigint(20) NOT NULL,
PRIMARY KEY (`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

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How I created my first interpreter

Common errors encounters in App Development in Flutter!

Turn a Developer to an Architect

Using LLVM from Rust to generate WebAssembly binaries

Partitioning tables in dedicated SQL pool

What is enduring software?

Tutorial 4: Using Firefox & Disconnect

Ensuring Natural-Scrolling Mouse and Touchpad Settings Are Set Correctly When Reconnecting Devices

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Code Factory

Code Factory

More from Medium

AWS Lambda to invoke other lambda function asynchronously in same region using Java

2 Ways to Upload Files to Amazon S3 In Your Spring Boot Project

Dockercon 2022 — Java Microservices

Criteria API (Introduction)