Calling a Stored Procedure in Spring Boot REST with JPA

In this tutorial, you will learn how to call stored procedures in a Spring Boot RESTful API project that uses Spring Data JPA.

Using a stored procedure to execute parts of the application logic in the database is a widely used approach in huge, data-heavy applications.

A stored procedure is a group of predefined SQL statements stored in the database. In Java, there are several ways to access stored procedures. I’ll show you how to call a stored procedure from Spring Data JPA Repositories in Spring Boot REST project.

I’ve created a database with the name “jp_database“. Inside this database, I’ve created one stored procedure with the name “INSERT_JP_USERS”. If you want to create a stored procedure in your own MySQL then you can use the query tab of MySQL and execute the SQL script in order to create a stored procedure which is mentioned below.

MySQL Database and Stored Procedure

HeidiSQL tool for MySQL interface

In the tutorial, I’m using the HeidiSQL tool for creating the stored procedure parameters and SQL script inside the routine body. The below screenshot will cover the IN and OUT parameters and the second screenshot will cover the rest of the SQL script in the routine body section.


Parameter Modes

Before I show you how to call the stored procedure with JPA, I have to tell you something about the different parameter modes. You can use 4 different modes of parameters to implement a stored procedure:

IN: for input parameters

OUT: for output parameters

INOUT: for parameters that are used for input and output

REF_CURSOR: for cursors on a result set

I will use IN and OUT parameters in the example. INOUT parameters are used similarly to the IN and OUT parameters.

Stored Procedures with IN and OUT Parameters

Let’s start with a simple stored procedure which takes 9 IN parameters, insert and update user via query and returns the result as an OUT parameter. The below SQL script you can use for creating the stored procedure.

Creating a Store Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERT_JP_USERS`(
    IN `Email_Param` VARCHAR(50),
    IN `First_Name_Param` VARCHAR(50),
    IN `Middle_Name_Param` VARCHAR(50),
    IN `Last_Name_Param` VARCHAR(50),
    IN `Gender_Param` VARCHAR(50),
    IN `Phone_Number_Param` VARCHAR(50),
    IN `Summary_Param` VARCHAR(50),
    IN `Experience_Param` INT,
    IN `Profile_Img_Param` VARCHAR(50),
    OUT `Result_Param` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE CheckExists int;

SELECT count(*) INTO CheckExists FROM jp_users WHERE email = Email_Param;

IF (CheckExists > 0) THEN

UPDATE jp_users SET first_name = First_Name_Param, middle_name = Middle_Name_Param, last_name = Last_Name_Param,
gender = Gender_Param, phone_number = Phone_Number_Param, summary = Summary_Param, experience = Experience_Param, profile_img = Profile_Img_Param WHERE email = Email_Param;

SET Result_Param = 0;

SELECT Result_Param;

ELSE 

INSERT INTO jp_users (email, first_name, middle_name, last_name, gender, phone_number, summary, experience, profile_img)
 
VALUES (Email_Param, First_Name_Param, Middle_Name_Param, Last_Name_Param, Gender_Param, Phone_Number_Param, Summary_Param, Experience_Param, Profile_Img_Param);

SELECT COUNT(*) INTO Result_Param FROM jp_users WHERE email = Email_Param;

SELECT Result_Param;
 
END IF;

END

Database Configuration

Open application. properties file in src/main/resources folder and add configurations connect to the database as below:

spring.jpa.hibernate.ddl-auto=none
spring.datasource.url=jdbc:mysql://localhost:3306/jp_database
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.properties.hibernate.default_schema=jp_database

Calling Stored Procedure

Create an AddUserRepositoryImpl class and provides an implementation for the addUsers method via stored procedure call:

AddUserRepositoryImpl Class

import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureQuery;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Repository;

import com.iats.constant.Constant;
import com.iats.model.response.AddUserResponse;
import com.iats.model.response.AddUserSPResponse;
import com.iats.model.response.UserSPDetail;
import com.iats.repository.AddUserRepository;

@Repository
public class AddUserRepositoryImpl implements AddUserRepository {

    private static Logger logger = LoggerFactory.getLogger(AddUserRepositoryImpl.class);

    private final static String USERS_PROC = ".INSERT_JP_USERS";

    @Autowired
    private EntityManager entityManager;

    @Autowired
    private Environment env;

    @Override
    public AddUserResponse addUsers(UserSPDetail userSPDetail) {
        String dbName = env.getProperty("spring.jpa.properties.hibernate.default_schema");
        StoredProcedureQuery query = this.entityManager.createStoredProcedureQuery(dbName + USERS_PROC);

        query.registerStoredProcedureParameter("Email_Param", String.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("First_Name_Param", String.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("Middle_Name_Param", String.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("Last_Name_Param", String.class, ParameterMode.IN);

        query.registerStoredProcedureParameter("Gender_Param", String.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("Phone_Number_Param", String.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("Summary_Param", String.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("Experience_Param", Integer.class, ParameterMode.IN);

        query.registerStoredProcedureParameter("Profile_Img_Param", String.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("Result_Param", Integer.class, ParameterMode.OUT);

        query.setParameter("Email_Param", userSPDetail.getEmail());
        query.setParameter("First_Name_Param", userSPDetail.getFirstName());
        query.setParameter("Middle_Name_Param", userSPDetail.getMiddleName());

        query.setParameter("Last_Name_Param", userSPDetail.getLastName());
        query.setParameter("Gender_Param", userSPDetail.getGender());
        query.setParameter("Phone_Number_Param", userSPDetail.getPhoneNumber());

        query.setParameter("Summary_Param", userSPDetail.getSummary());
        query.setParameter("Experience_Param", userSPDetail.getExperience());
        query.setParameter("Profile_Img_Param", userSPDetail.getProfileImg());

        int count = ((Number) query.getOutputParameterValue("Result_Param")).intValue();
        logger.info("SP Response " + count);

        if (count == 1) {
            AddUserResponse usrResp = new AddUserResponse(Constant.STATUS_TRUE, Constant.SUCCESS);
            usrResp.setData(new AddUserSPResponse("User added successfully."));
            return usrResp;
        } else {
            AddUserResponse usrResp = new AddUserResponse(Constant.STATUS_TRUE, Constant.SUCCESS);
            usrResp.setData(new AddUserSPResponse("User updated successfully."));
            return usrResp;
        }
    }

}

The above code is explaining how to call the database stored procedure in Java Spring Boot using EntityManager which is part of Java Persistence API. After a successful call, the stored procedure result set will return the result in the OUT parameter. In the above code for the OUT parameter, we’re using a query.getOutputParameterValue()

Also, we’re using a query.setParameter() for setting the user input parameter.

In the output parameter count value can be 1 or 0. If the count value is 1 it’s mean we’ve added record successfully in the database. If the count value is 0 it means we’ve updated the record into the database.

In the AddUserRepositoryImpl class, we’ve method “addUsers” inside this method below the line of code will call the stored procedure.

StoredProcedureQuery query = this.entityManager.createStoredProcedureQuery(dbName + USERS_PROC)

AddUserRepository Interface

import com.iats.model.response.AddUserResponse;
import com.iats.model.response.UserSPDetail;

public interface AddUserRepository {

    public AddUserResponse addUsers(UserSPDetail userSPDetail);
}

Implementing the UserService

UserService Interface

import com.iats.model.request.AddUserRequest;
import com.iats.model.response.AddUserResponse;

public interface UserService {

    public AddUserResponse addUsers(AddUserRequest addUserRequest);
}

UserServiceImpl Class

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

import com.iats.model.request.AddUserRequest;
import com.iats.model.request.UserRequest;
import com.iats.model.response.AddUserResponse;
import com.iats.model.response.UserResponseList;
import com.iats.model.response.UserSPDetail;
import com.iats.repository.AddUserRepository;
import com.iats.service.UserService;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private AddUserRepository addUserRepository;

    @Override
    public AddUserResponse addUsers(AddUserRequest addUserRequest) {
        return addUsers(addUserRequest.getEmail(), addUserRequest.getFirstName(), addUserRequest.getMiddleName(),
                addUserRequest.getLastName(), addUserRequest.getGender(), addUserRequest.getPhoneNumber(),
                addUserRequest.getSummary(), addUserRequest.getProfileImg(), addUserRequest.getExperience());
    }

    private AddUserResponse addUsers(String email, String firstName, String middleName, String lastName, String gender,
            String phoneNumber, String summary, String profileImg, int experience) {

        AddUserResponse spResp = null;
        UserSPDetail userSPDetail = new UserSPDetail();
        userSPDetail.setEmail(email);
        userSPDetail.setFirstName(firstName);
        userSPDetail.setMiddleName(middleName);
        userSPDetail.setLastName(lastName);
        userSPDetail.setGender(gender);
        userSPDetail.setPhoneNumber(phoneNumber);
        userSPDetail.setSummary(summary);
        userSPDetail.setProfileImg(profileImg);
        userSPDetail.setExperience(experience);
        spResp = addUserRepository.addUsers(userSPDetail);
        return spResp;
    }
}

User Controller Class which is calling add users method inside UserServiceImpl class

import javax.servlet.http.HttpServletRequest;
import javax.validation.Valid;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.iats.constant.Constant;
import com.iats.model.request.AddUserRequest;
import com.iats.model.response.AddUserResponse;
import com.iats.service.UserService;

@RestController
@RequestMapping(value = "/api/v1")
public class UserController {

    @Autowired
    private UserService userService;

    @PostMapping("/addUsers")
    public ResponseEntity<Object> addUser(@Valid @RequestBody AddUserRequest addUserRequest,
            HttpServletRequest httpServletRequest) throws Exception {
        AddUserResponse addUserResponse = new AddUserResponse(Constant.STATUS_TRUE, Constant.SUCCESS);
        addUserResponse = userService.addUsers(addUserRequest);
        return ResponseEntity.ok(addUserResponse);
    }
}

Trying How It Works

Using Postman, we’ll test the RESTful API POST method for adding users using stored procedure calls.

Below is an example of the postman HTTP request that I will use to add records into the database using stored procedure calls. In this example, I’m passing the request body with email, first, middle and last name, gender, phone number, summary, experience, and profile image. The record will be stored in the database table “jp_users”.

Test Example

Request URL with Post method

http://localhost:8080/api/v1/addUsers

In the above example record has been saved into the database table.

Summary

In this tutorial, you have learned how to call stored procedures using Spring Data JPA in the Spring Boot project. The programmatic creation of a StoredProcedureQuery is one of two options to define a stored procedure call in JPA. Therefore, you have to call one of the createStoredProcedureQuery methods on the entity manager, and then you have to define the input and output parameters of the procedure.


Leave a Reply

Your email address will not be published. Required fields are marked *

Free Video Lessons

Enter your email and stay on top of things,

Subscribe!