Calling a Stored Procedure in Spring Boot REST with JPA

I was interested in the Spring Boot community and started working on a project to import a list of surnames from a website that I work on. The list is stored in a MySQL database and I wanted to be able to access the information from Spring Boot. However, I had to call a stored procedure and needed the result back in JSON format.

In today’s tutorial, we will be writing a Spring Boot REST application that will issue an SQL query to the database and retrieve a record. In order to make this scenario easier to follow, we are going to use an existing repository that contains a table called “User” with the following schema:

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 some of the application logic in a database is a common approach in large, data-intensive applications. A stored procedure is a group of predefined SQL statements stored in a database. In Java, there are several ways to approach stored procedures. I’ll show you how to call a stored procedure from the Spring Data JPA repositories in a Spring Boot REST project. I have created a database called jp_database. In this database, I created a stored procedure called INSERT_JP_USERS. If you want to create a stored procedure in your own MySQL, you can use the MySQL Query tab and run the SQL script to create the stored procedure described below.

MySQL database and stored procedure

word-image-3368

HeidiSQL utility for MySQL interface

In this tutorial, I use the HeidiSQL tool to create the parameters of the stored procedure and the SQL script in the body of the procedure. The screenshot below shows the IN and OUT parameters, the second screenshot shows the rest of the SQL script in the body section.   word-image-3369 word-image-3370

Setting modes

Before I show you how to call a stored procedure with JPA, I need to tell you about the different setting modes. You can use 4 different setup modes to implement a stored procedure: IN: for input parameters OUT: for output parameters INOUT: for parameters used for input and output. REF_CURSOR : for the cursor on the result set In this example I will use the IN and OUT parameters. The INOUT parameters are used in the same way as the IN and OUT parameters.

Stored procedures with IN and OUT parameters

Let’s start with a simple stored procedure that takes 9 IN parameters, inserts and updates the user via a query, and returns the result as an OUT parameter. You can use the following SQL script to create a stored procedure.

Create a storage 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 DEFINE 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, resume, experience, profile picture) 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 the application.properties file in the src/main/resources folder and add the database connection settings as shown 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_scheme=jp_database

Calling a stored procedure

Create the AddUserRepositoryImpl class and provide the implementation of the addUsers method by calling a stored procedure:

classAddUserRepositoryImpl

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 static String USERS_PROC = .INSERT_JP_USERS ; @Autowired private EntityManager entityManager ; @Autowired private environment approx; @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, Investor.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(LastName_Param, userSPDetail.getSurname()); 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 successfully updated.));return usrResp;} } The code above explains how to call a procedure in Java Spring Boot that is stored in a database with EntityManager, which is part of the Java Persistence API. After a successful call, the result set of the stored procedure returns the result in the OUT parameter. In the above code we use query.getOutputParameterValue() for the OUT parameter In addition, we use query.setParameter() to set the user’s input parameter. In the output parameter, the count value can be 1 or 0. If the number is 1, it means that we have successfully added a record to the database. If the number is 0, it means that we have updated the record in the database. In the AddUserRepositoryImpl class we have the addUsers method, in this method the below 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); }

UserService Implementation

User 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 first name, String middle name, String last name, String gender, String phone number, String summary, String profileImg, 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 that calls the Add User method in the 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 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); } }

Try how it works

Using Postman, we test the RESTful API POST method for adding users using stored procedure calls. Below is an example of a postman HTTP request that I will use to add records to the database using stored procedure calls. In this example, I am passing the body of the request with the email address, first name, middle name, last name, gender, phone number, resume, experience, and profile picture. The entry is stored in the database table jp_users.

Test example

To request a URL with the post method http://localhost:8080/api/v1/addUsers word-image-3371 word-image-3372 In the above example, the record was stored in the database table. word-image-3373

Summary

In this tutorial you learned how to call stored procedures with Spring Data JPA in a Spring Boot project. StoredProcedureQuery programming is one of two ways to define a stored procedure call in JPA. So you need to call one of the entity manager’s createStoredProcedureQuery methods, and then set the procedure’s input and output parameters.

Frequently Asked Questions

How can we call stored procedure in spring boot using JPA?

If you’re reading this, it means that you’ve probably heard about Hibernate, a popular open source Java persistence framework. It’s the JPA implementation that is at the core of Spring MVC and Spring Web frameworks. However, Hibernate is not without its problems. Most notably, it doesn’t support stored procedures. This can be a problem if your application needs to invoke a stored procedure (say, to download data from a file server), but first you need to run the stored procedure. In this tutorial I’ll show you how to call a stored procedure from Spring Boot with Hibernate. Calling a stored procedure in Spring Boot REST means to execute a procedure in a Java stored procedure directly from a Java application using the Spring Framework. This is an alternative to the @SpringBootApplication annotation, which means that the stored procedure can be invoked from a Spring HTTP server.

How can we call stored procedure using JPA repository?

Sometimes we need to call stored procedures written in Java and use the results of those stored procedures in our Spring Boot application. Most of the times, we need to issue the stored procedure from Spring Boot REST API and we need to call the stored procedure from Java. This means that we have to create a Java application that calls stored procedure and use the result in the Spring Boot web application. This article will show you how we can call stored procedure with JPA and use the result in Spring Boot application. I had a requirement for calling a stored procedure from the JPA repository that was based on a value coming from a repository. This was a basic requirement for a project. If you have used Spring Boot, you know that this is not a simple task, as you have to use the JPA layer.

How can we call stored procedure in hibernate spring boot?

Though you may not be using hibernate as a Spring Boot starter, you might want to write stored procedures in SQL to manage your data. What’s the deal with hibernate? It’s a common JavaEE technology that was introduced back in the early days of Java EE 6. For our purpose, it’s important to understand how hibernate works and what it can be used for. Hibernate stores your persistent objects in memory and releases the objects when the user closes the application. Hibernate is commonly used in Spring Boot applications, since it adds great features such as the @Configuration annotation and the hibernate-validator library.

calling stored procedures from spring data jpa repositorieshow to call stored procedure in spring boot using jdbctemplate examplespring data jpa call stored procedure with multiple output parameterspring data jpa stored procedure cursor examplehow to call stored procedure in spring boot using jpa examplecalling stored procedure spring boot application using jpa repository,People also search for,Feedback,Privacy settings,How Search works,how to call stored procedure in spring boot using jpa example,calling stored procedure spring boot application using jpa repository,spring data jpa call stored procedure with multiple input parameter,spring data jpa call stored procedure without entity,calling stored procedures from spring data jpa repositories,how to call stored procedure in spring boot using jdbctemplate example,spring data jpa call stored procedure with multiple output parameter,spring data jpa stored procedure cursor example

You May Also Like