Calling Stored Procedures from Spring Data JPA Repositories

1. Overview

A stored procedure is a group of predefined SQL statements stored in the database. In Java, there are several ways to access stored procedures. In this tutorial, we’ll show how to call stored procedures from Spring Data JPA Repositories.

2. Project Setup

In this tutorial, we’ll use the Spring Boot Starter Data JPA module as the data access layer. We’ll also use MySQL as our backend database. Therefore, we’ll need Spring Data JPA, Spring Data JDBC, and MySQL Connector dependencies in our project pom.xml file:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

Once we have the MySQL dependency definition, we can configure the database connection in the application.properties file:

spring.datasource.url=jdbc:mysql://localhost:3306/maixuanviet
spring.datasource.username=maixuanviet
spring.datasource.password=maixuanviet@1234

3. Entity Class

In Spring Data JPA, an entity represents a table stored in a database. Therefore, we can construct an entity class to map the car database table:

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

    @Column
    private String model;

    @Column
    private Integer year;

   // standard getters and setters
}

4. Stored Procedure Creation

A stored procedure can have parameters so that we can get different results based on the input. For example, we can create a stored procedure that takes an input parameter of integer type and returns a list of cars:

CREATE PROCEDURE FIND_CARS_AFTER_YEAR(IN year_in INT)
BEGIN 
    SELECT * FROM car WHERE year >= year_in ORDER BY year;
END

A stored procedure can also use output parameters to return data to the calling applications. For example, we can create a stored procedure that takes an input parameter of string type and stores the query result into an output parameter:

CREATE PROCEDURE GET_TOTAL_CARS_BY_MODEL(IN model_in VARCHAR(50), OUT count_out INT)
BEGIN
    SELECT COUNT(*) into count_out from car WHERE model = model_in;
END

5. Reference Stored Procedures in Repository

In Spring Data JPA, repositories are where we provide database operations. Therefore, we can construct a repository for the database operations on the Car entity and reference stored procedures in this repository:

@Repository
public interface CarRepository extends JpaRepository<Car, Integer> {
    // ...
}

Next, let’s add some methods to our repository that call stored procedures.

5.1. Map a Stored Procedure Name Directly

We can define a stored procedure method using the @Procedure annotation and map the stored procedure name directly.

There are four equivalent ways to do that. For example, we can use the stored procedure name directly as the method name:

@Procedure
int GET_TOTAL_CARS_BY_MODEL(String model);

If we want to define a different method name, we can put the stored procedure name as the element of @Procedure annotation:

@Procedure("GET_TOTAL_CARS_BY_MODEL")
int getTotalCarsByModel(String model);

We can also use the procedureName attribute to map the stored procedure name:

@Procedure(procedureName = "GET_TOTAL_CARS_BY_MODEL")
int getTotalCarsByModelProcedureName(String model);

Similarly, we can use the value attribute to map the stored procedure name:

@Procedure(value = "GET_TOTAL_CARS_BY_MODEL")
int getTotalCarsByModelValue(String model);

5.2. Reference a Stored Procedure Defined in Entity

We can also use the @NamedStoredProcedureQuery annotation to define a stored procedure in the entity class:

@Entity
@NamedStoredProcedureQuery(name = "Car.getTotalCardsbyModelEntity", 
  procedureName = "GET_TOTAL_CARS_BY_MODEL", parameters = {
    @StoredProcedureParameter(mode = ParameterMode.IN, name = "model_in", type = String.class),
    @StoredProcedureParameter(mode = ParameterMode.OUT, name = "count_out", type = Integer.class)})
public class Car {
    // class definition
}

Then, we can reference this definition in the repository:

@Procedure(name = "Car.getTotalCardsbyModelEntity")
int getTotalCarsByModelEntiy(@Param("model_in") String model);

We use the name attribute to reference the stored procedure defined in the entity class. For the repository method, we use @Param to match the input parameter of the stored procedure. Also, we match the output parameter of the stored procedure to the return value of the repository method.

5.3. Reference a Stored Procedure with @Query Annotation

We can also call a stored procedure directly with the @Query annotation:

@Query(value = "CALL FIND_CARS_AFTER_YEAR(:year_in);", nativeQuery = true)
List<Car> findCarsAfterYear(@Param("year_in") Integer year_in);

In this method, we use a native query to call the stored procedure. We store the query in the value attribute of the annotation.

Similarly, we use @Param to match the input parameter of the stored procedure. Also, we map the stored procedure output to the list of entity Car objects.

6. Summary

In this tutorial, we showed how to access stored procedures through JPA repositories. Also, we discussed two simple ways to reference the stored procedures in JPA repositories.

As always, the source code for the article is available over on GitHub.