Spring Boot Stored Procedure Example (2024) | TechGeekNext


Spring Boot Stored Procedure Example (2024)

In this tutorial, we'll demonstrate how to call Stored Procedure with and without parameter in Spring Boot + JPA with MySQL database.

When using Spring Boot JPA, there are several ways to call stored procedures. A helpful set of annotations from Spring are available for working with stored procedures.

In this tutorial, it will be demonstrated how to resolve that issue or come up with a solution after figuring out that utilising particular annotations gives exception to moving forward.

  1. Referencing a Stored Procedure in Entity
    @Entity
@Table(name = "employees")
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(name = "getAllEmployees",
				procedureName = "get_employees",
				resultClasses = Employee.class)
})

public class Employee {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private long id;

    ......
}
    Call Stored Procedure from JPA Repository using @Procedure

    @Procedure annotation help to map the Stored Procedure defined in entity class.

    When I attempted to utilize the @Procedure annotation, I got exception org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.

    Further investigation of the problem directs me to another approach to use the @Query from JPA repository.

Let's start implementing the spring boot application to fetch data using stored procedure.

Create Stored Procedure in MySQL database

Create Stored Procedure in MySQL by right click on Stored Procedures -> Select Create Stored Procedure. Spring Boot Stored Procedure Example

Get Employees
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_employees`()
BEGIN
select * FROM employees;
END
Get Employee by id
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_employee_id`(emp_id BIGINT(20))
BEGIN
select * from employees where id=emp_id;
END
Delete Employee by id
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_emp_id`(emp_id BIGINT(20))
BEGIN
DELETE FROM employees where id=emp_id;
END

Create Spring Boot application

Create Spring Boot application from Spring Initializr.

Project Structure

Spring Boot Stored Procedure Example

Add Dependencies

Add below dependencies for Web - spring-boot-starter-web , JPA - spring-boot-starter-data-jpa and MYSQL - mysql-connector-java in 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.3.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.techgeeknext</groupId>
	<artifactId>spring-boot-stored-proc</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>spring-boot-stored-proc</name>
	<description>Spring Boot + JPA + Stored Procedure Example</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>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Take a look at our suggested posts:
Apache Storm Tutorial
Spring Boot - Transaction Management
Spring Boot - JWT Authentication Example
Spring Boot - JWT + Angular 8 Authentication Example
Spring Boot - JWT + MYSQL Authentication Example
Spring Boot - Session Management using Redis
OAuth2.0 Tutorial
Spring Boot - Hazelcast
Spring Boot - JPA + REST + Swagger + MYSQL Example
Java 15
Java 14
Java 8 Interview Questions and Answers
Angular 8 Spring Boot Example

Application Properties

Add database connection details in application.properties file. 

spring.datasource.url=jdbc:mysql://localhost/employeetestdb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.platform=mysql
spring.datasource.initialization-mode=always

## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
# spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update

Employee Data Model

Create an entity class called Employee. As stated above, I commented out the code that was causing the issue. 

package com.techgeeknext.model;

import lombok.*;

import javax.persistence.*;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@ToString
@Entity
@Table(name = "employees")
/*
@NamedStoredProcedureQueries({
		@NamedStoredProcedureQuery(name = "getAllEmployees",
				procedureName = "get_employees",
				resultClasses = Employee.class)
})
*/
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "role")
    private String role;
}

Employees Repository

Create a class called Employee Repository. As indicated above, I commented out the @Procedure code that was causing the error. Use @Query annotation to map the stored procedure. 

package com.techgeeknext.repository;

import com.techgeeknext.model.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.util.List;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    //    @Procedure(procedureName = "get_employees")
    //     List<Employee> getAllEmployees();

    @Query(nativeQuery = true, value = "call get_employees")
    List<Employee> getAllEmployees();

    @Query(nativeQuery = true, value = "call get_employee_id(:emp_id)")
    Employee getEmployeeById(@Param("emp_id") Long emp_id);

    @Transactional
    @Modifying
    @Query(nativeQuery = true, value = "call delete_emp_id(:emp_id)")
    void deleteEmployeeById(@Param("emp_id") Long emp_id);

}

Employee Controller

Create rest endpoints to retrieve employee information and delete employees using stored procedures. 

package com.techgeeknext.controller;

import com.techgeeknext.model.Employee;
import com.techgeeknext.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Optional;

//@CrossOrigin(origins = "http://localhost:8081")
@RestController
public class EmployeeController {

    @Autowired
    EmployeeRepository employeeRepository;

    /**
     * Get all the employees
     *
     * @return ResponseEntity
     */
    @GetMapping("/employees")
    public ResponseEntity<List> getEmployees() {
        try {
            return new ResponseEntity<>(employeeRepository.getAllEmployees(), HttpStatus.OK);
        } catch (Exception e) {
            e.printStackTrace();
            return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

    /**
     * Get the employee by id
     *
     * @param id
     * @return ResponseEntity
     */
    @GetMapping("/employee/{id}")
    public ResponseEntity<Employee> getEmployeeById(@PathVariable("id") long id) {
        try {
            //check if employee exist in database
            Employee empObj = getEmpRec(id);

            if (empObj != null) {
                return new ResponseEntity<>(empObj, HttpStatus.OK);
            }

            return new ResponseEntity<>(HttpStatus.NOT_FOUND);

        } catch (Exception e) {
            return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
        }

    }



    /**
     * Delete Employee by Id
     *
     * @param id
     * @return ResponseEntity
     */
    @DeleteMapping("/employee/{id}")
    public ResponseEntity<HttpStatus> deleteEmployeeById(@PathVariable("id") long id) {
        try {
            //check if employee exist in database
            Employee emp = getEmpRec(id);

            if (emp != null) {
                employeeRepository.deleteEmployeeById(id);
                return new ResponseEntity<>(HttpStatus.OK);
            }

            return new ResponseEntity<>(HttpStatus.NOT_FOUND);

        } catch (Exception e) {
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }



    /**
     * Method to get the employee record by id
     *
     * @param id
     * @return Employee
     */
    private Employee getEmpRec(long id) {
        Optional<Employee> empObj = Optional
                .ofNullable
                        (employeeRepository.getEmployeeById(id));

        if (empObj.isPresent()) {
            return empObj.get();
        }
        return null;
    }


    /**
     * Create new employee
     *
     * @param employee
     * @return ResponseEntity
     */
    @PostMapping("/employee")
    public ResponseEntity<Employee> newEmployee(@RequestBody Employee employee) {
        Employee newEmployee = employeeRepository
                .save(Employee.builder()
                        .name(employee.getName())
                        .role(employee.getRole())
                        .build());
        return new ResponseEntity<>(newEmployee, HttpStatus.OK);
    }
}

Test Spring Boot Stored Procedure Example

  1. Start the Spring Boot Application by running spring-boot:run or by running main class.
  2. Create New Employee
    Open Postman, use POST method with end point http://localhost:8080/employee and provide Employee details to create new employee record. Spring Boot CRUD Example - Post Output
  3. List Employees
    Use GET method with end point http://localhost:8080/employees to get all employees. Spring Boot CRUD Example - List Employee Output
  4. Get Employee
    Use GET method with end point http://localhost:8080/employee/1 to get employee by id. Spring Boot CRUD Example - Get Output
  5. Delete Employee
    Use DELETE method with end point http://localhost:8080/employee/1 where 1 is the id of the employee. Spring Boot CRUD Example - Delete Output

Refer Spring Boot JPA CRUD Example to implement all the CRUD rest endpoints with this example.

Download Source Code

The full source code for this article can be found below.

  1. Download it here - Spring Boot Stored Procedure Example
Recommendation for Top Popular Post :

Spring Boot - Session Management
Spring Boot - Transaction Management
Spring Boot - Security Tutorial
Java Lombok Tutorial
PCF Tutorial
Spring Cloud Tutorial
RxJS Tutorial
Spring Boot - JWT Example
Spring Boot Complete CRUD example
Spring Boot - RabbitMQ Example
Angular Spring Boot Example
Spring Boot JPA Rest
Java Z Garbage Collector (ZGC)
Java 8 Programming Interview Questions
Angular - RxJS Interview Questions
RxJS switchMap
CI CD DevOps Interview Questions
Top Spring Batch Interview Questions
RxJS Higher-Order Observable Mapping