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.