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.
1 2 3 4 5 6 7 | -- To create a table CREATE TABLE `person` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ; |
Now, creating a stored procedure requires a little coding and configuration. Run the following codes as a script.
1 2 3 4 5 6 | DELIMITER $$ CREATE PROCEDURE `addPerson`(personName varchar(50)) BEGIN INSERT INTO PERSON(NAME) VALUES(personName); END$$ DELIMITER ; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | package com.turreta.springboot.springdata.sp; import javax.persistence.*; @Entity @Table(name = "person") public class Person { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Integer id; private String name; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } } |
Then, we create the PersonRepository interface.
1 2 3 4 5 6 7 8 9 10 11 | package com.turreta.springboot.springdata.sp; import org.springframework.data.jpa.repository.query.Procedure; import org.springframework.data.repository.CrudRepository; @Repository public interface PersonRepository extends CrudRepository<Person, Integer> { @Procedure void addPerson(String name); } |
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.
1 2 3 4 | spring.datasource.url=jdbc:mysql://127.0.0.1:3306/jpahibernatedemo spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.username=root spring.datasource.password=password |
Spring Boot Stored Procedure Testing
The following codes are from our main Spring Boot class.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | package com.turreta.springboot.springdata.sp; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.context.ApplicationContext; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; @SpringBootApplication public class ComTurretaSpringbootSpringdataSpApplication { public static void main(String[] args) { ApplicationContext c = SpringApplication.run(ComTurretaSpringbootSpringdataSpApplication.class, args); PersonRepository repo = c.getBean(PersonRepository.class); repo.addPerson("KL"); repo.addPerson("SG"); } } |
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
Hi – you seem to be missing the Stored Procedure call in your database definition. @Procedure in your PersonRepository is pointing to “addPerson(…)”
Hi Henrik, I have updated my blog post with the Stored Procedure. Thank you for the hint!