Call Stored Procedures using Spring Boot JPA | Code Factory


Donate : Link

Medium Blog : Link

Applications : 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s