Java, Software Development, Spring, Spring Boot

Spring Boot Call Stored Procedure With Spring Data @Procedure

Although most applications use SQL statements, we may need to call stored procedures instead. For instance, we could reuse existing stored procedures instead of crafting new codes. As a result, we do not need to create new test cases, and we do not reinvent the wheels. This post shows how Spring Boot can call stored procedures using Spring Data and its @Procedure annotation.

Java, Spring Boot, and Other Requirements

Before moving on, here is the list of items we used for this post.

  • IntelliJ IDEA Ultimate 2016.3 – Optional
  • Java 8
  • Windows 10 64bit
  • Spring Boot 1.5.6.RELEASE
  • Spring Initialzr
  • MySQL and MySQL Workbench

Spring Initialzr in IntelliJ IDEA

First, let us create a Spring Boot application using Spring Initialzr, which is also available online.

MySQL Instance, Tables, and Stored Procedure

Then, we set up a MySQL server to run locally. We could download an installer and install it on our local machine. Alternatively, we could use a MySQL docker image to create a container where the MySQL server will run. Either, we should have a MySQL server running before we create tables, stored procedures, and other database objects.

Next, we create a table and a stored procedure. Consider the following SQL codes, which we could run against MySQL separately.

Now, creating a stored procedure requires a little coding and configuration. Run the following codes as a script.

Entity and Repository For our Spring Boot Application

Next, we create some Java codes. The Java class before is a persistent entity that represents a record in the Person table.

Then, we create the PersonRepository interface.

Note the method name here. It is the same as our stored procedure in MySQL. Spring Boot @Procedure annotation maps this method to that actual stored procedure.

Finally, we update the application.properties to specify connection details. But before we can access our database, we need a valid user account, which is the root user.

Spring Boot Stored Procedure Testing

The following codes are from our main Spring Boot class.

When we run our codes, we get the following output.

We also get updates to our database table as shown below.

Download the Spring Boot Stored Procedure Project

The codes for this Spring Boot project that uses stored procedure is available in the following link.

https://github.com/Turreta/Call-Stored-Procedure-using-Spring-Data-and-Procedure

Loading

Got comments or suggestions? We disabled the comments on this site to fight off spammers, but you can still contact us via our Facebook page!.


You Might Also Like

2 Comments

  1. 1

    Hi – you seem to be missing the Stored Procedure call in your database definition. @Procedure in your PersonRepository is pointing to “addPerson(…)”

  2. 2

Comments are closed.