In Relational Database Management Systems (RDBMS), a stored procedure is a batch of prepared SQL code grouped as a logical unit that you store in the database. Stored procedures allow reusability of SQL code.

In this post, I will explain how to call MySQL stored procedures from a Spring Boot application.

Dependency

For the application in this post, add the Spring Data JPA starter and the MySQL connecter dependency in your Maven pom.xml file.

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
   <version>2.4.3</version>
</dependency>

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.23</version>
</dependency>

Calling Stored Procedure from Spring Boot Example

I will demonstrate calling stored procedures with a Spring Boot application with the controller, service, and repository layers.

The application contains a Blog entity class.

Blog.java

@Entity
public class Blog {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column
private long blogId;

@Column
private String title;
@Column
private Integer yearOfPost;
public long getBlogId() {
return blogId;
}
public String getTitle() {
return title;
}
public Integer getYearOfPost() {
return yearOfPost;
}
}

Creating Stored Procedure

The next step is to create a stored procedure to be referred to from the entity class.

The SQL code of a stored procedure to retrieve all blogs is this.

<>

You can run the preceding stored procedure using a MySQL client.

I have used MySQL Workbench to create a stored procedure named getAllBlogs.

The getAllBlogs procedure takes no parameter and returns all the records of the blog table.

The next step is to call the stored procedure. There are several approaches for that.

Approach 1 – @NamedStoredProcedureQuery Annotation

A @NamedProcedureQuery defines a query that calls stored procedures as named queries. To use this annotation, I have refactored the  Blog entity class, like this.

Blog.java

package org.springframework.guru.model;

import javax.persistence.*;

@Entity
@Table(name = "blog")
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(name = "getAllBlogs",procedureName = "getAllBlogs"),
@NamedStoredProcedureQuery(name = "getBlogsByTitle", procedureName = "getBlogsByTitle", parameters = {@StoredProcedureParameter(mode = ParameterMode.IN,name = "tblogTitle",type=String.class)} )})

public class Blog {
@Id
private Integer blogId;
private String blogTitle;
private Integer yearOfPost;
}

In the preceding Blog class, there are two @NamedStoredProcedureQuery annotations. Notice that the @NamedStoredProcedureQueries annotation groups both the @NamedStoredProcedureQuery annotations.

In a @NamedStoredProcedureQuery annotation, the name attribute specifies the name of the query. The procedureName attribute specifies the name of the stored procedure to call.

The second @NamedStoredProcedureQuery annotation on Line 9 specifies the mode, name, and type of the parameter to be passed to the stored procedure.

You can use four different modes of parameters to implement a stored procedure:

• IN: for input parameters
• OUT: for output parameters
• INOUT: is a combination of IN and OUT parameters. This parameter is initialized by the caller. The stored procedure modifies and returns back the parameter to the caller.
• REF_CURSOR: for cursors on a result set

BlogRepository Interface
We will now create the Spring Data JPA repository interface. In Spring Data JPA, repositories are interfaces that declare database operations. At runtime, Spring Data JPA provides implementations of the repository interfaces you create.

The code for the BlogRepository interface is this.

BlogRepository

package org.springframework.guru.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.guru.domain.Blog;
import org.springframework.stereotype.Repository;

@Repository
public interface BlogRepository extends JpaRepository<Blog,Integer> {
}

Let us call the stored procedure from the service class.

The code of the BlogService class is this.

BlogService.java

@Service
public class BlogService {
   @Autowired
   private BlogRepository blogRepository;

   @Autowired
   @PersistenceContext
   private EntityManager em;

   public List getTotalBlogs(){
       return em.createNamedStoredProcedureQuery("getAllBlogs").getResultList();
   }
   public List getBlogsByTitle(String title) {
       return em.createNamedStoredProcedureQuery("getBlogsByTitle").setParameter("tblogTitle",title).getResultList();
   }
}

The preceding BlogService class is a Spring service class annotated with the @Service annotation.

Line 6 auto wires an EntityManager object.  The two methods getAllBlogs() and getBlogsByTitle()

calls the createNamedStoredProcedureQuery() method of EntityManager and returns the result as a List

BlogController.java

This is the code for the BlogController class.

@RestController
public class BlogController {

   @Autowired
   private BlogService blogService;

   @GetMapping("/titleCount")
   public List getTotalBlogs(){
       return blogService.getTotalBlogs();
   }

   @GetMapping(path = "/titleCountP")
   public List getBlogsByTitle(@RequestParam("blogTitle") String title) {
       return blogService.getBlogsByTitle(title);
   }
}

The preceding BlogController class is annotated with @RestController annotation and contains two handler methods.

Running the Application

Once you run the application, Tomcat will start and by default will listen to port 8080. Open the browser and access the URL http://localhost:8080/titleCount.

This figure shows the response returned by the application. Here the response is all the blog posts returned by the getAllBlogs stored procedure.

Approach-2 @Procedure Annotation

The second approach is to use the @Procedure annotation of Spring Data JPA in the repository interface. This annotation allows you to map a user-defined repository method to a database stored procedure.

To show this approach, I have created another stored procedure named GET_TOTAL_BLOGS_BY_TITLE.

The SQL code is this.

CREATE PROCEDURE 'GET_TOTAL_BLOGS_BY_TITLE' (IN title_in VARCHAR(50), OUT count_out INT)
BEGIN
 SELECT COUNT(*) into count_out from blog WHERE title = title_in;
END

Here, GET_TOTAL_BLOGS_BY_TITLE is the procedure name that takes an input parameter of VARCHAR type title_in. It stores the query result into an output parametercount_out to return the count of blog posts with the given blog title.

The JPA Repository

The next step is to declare the methods in the JPA repository to call the stored procedure using the @Procedure annotation. There are several ways of doing that.

• Approach 1 – Stored procedure as the method name

@Procedure
int GET_TOTAL_BLOGS_BY_TITLE(String title);

• Approach 2 – Stored procedure name as a String value

You have your own method name, for example getTotalBlogsByTitle(). You can pass the stored procedure name as a String value to the @Procedure annotation.

@Procedure("GET_TOTAL_BLOGS_BY_TITLE")
int getTotalBlogsByTitle(String title);

• Approach 3 – Stored procedure name as the procedureName attribute

@Procedure(procedureName = "GET_TOTAL_BLOGS_BY_TITLE")
int getTotalBlogsByTitleProcedureName(String model);

• Approach 4 – Stored procedure name as the value attribute

@Procedure(value = "GET_TOTAL_BLOGS_BY_TITLE")
int getTotalBlogsByTitleValue(String model);

The updatedBlogRepository interface is this.

BlogRepository.java

package org.springframework.guru.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;
import org.springframework.guru.domain.Blog;
import org.springframework.stereotype.Repository;

@Repository
public interface BlogRepository extends JpaRepository<Blog,Integer> {

    @Procedure
    int GET_TOTAL_BLOGS_BY_TITLE(String title);

    @Procedure("GET_TOTAL_BLOGS_BY_TITLE")
    int getTotalBlogsByTitle(String title);

    @Procedure(procedureName = "GET_TOTAL_BLOGS_BY_TITLE")
    int getTotalBlogsByTitleProcedureName(String model);

    @Procedure(value = "GET_TOTAL_BLOGS_BY_TITLE")
    int getTotalBlogsByTitleValue(String model);
}

In order to insert data into the blog table, you can do that by simply writing simple insert query insert into blog values (blogId = 100, title = 'BlogPost1', yearOfPost = 2001 and check the count of entries in the blogtable as shown below.

Running the Application

On running the application, you will see the Tomcat starting on the default port. You can then open the browser at localhost:8080/titleCountEx/?title=BlogPost1to get the total count of blog posts.

The mapping used here is titleCountEx and the value of the parameter passed for the title is BlogPost1.

Since there are total 3 entries with this Blog 3 title, it got reflected in the browser.

The Github Links for both repos are here. Link1
Link2

For in-depth knowledge on MySQL, you can check my Udemy Best Seller Course SQL Beginner to Guru: MySQL Edition – Master SQL with MySQL