announcement - icon

JPA can behave very differently depending on the exact circumstances under which it is used. Code that works in our local environment or in staging performs very poorly (or even flat out fails) when thrown against real-scale databases in production environments.

Debugging these JPA issues in production is pretty difficult - existing APMs don’t provide enough granular insights at the code level, and tracking every single place someone queried entities one by one instead of in bulk can be a grueling, time-consuming task.

Lightrun is a new approach to debugging in production. Using Lightrun’s Logs and Snapshots, you can now get debugger-level granularity in production without opening inbound ports, redeploying, restarting, or even stropping the running application.

In addition, instrumenting Lightrun Metrics at runtime allows you to track down persistence issues securely and in real-time. Want to see it in action? Check out our 2-minute tutorial for debugging JPA performance issues in production using Lightrun:

>> Debugging Spring Persistence and JPA Issues Using Lightrun

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 learn how to call stored procedures from Spring Data JPA Repositories.

2. Project Setup

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:


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


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:

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

    private String model;

    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:

    SELECT * FROM car WHERE year >= year_in ORDER BY year;

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:

    SELECT COUNT(*) into count_out from car WHERE model = model_in;

5. Reference Stored Procedures in Repository

In Spring Data JPA, repositories are where we provide database operations. We can construct a repository for the database operations on the Car entity, and reference stored procedures in this 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:

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 the @Procedure annotation:

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);

Finally, 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:

@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. We also match the output parameter of the stored procedure to the return value of the repository method.

5.3. Reference a Stored Procedure With the @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. We also map the stored procedure output to the list of entity Car objects.

6. Summary

In this article, we explored how to access stored procedures through JPA repositories. We also 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.

Persistence bottom
Get started with Spring Data JPA through the reference Learn Spring Data JPA course: >> CHECK OUT THE COURSE
Persistence footer banner
Comments are closed on this article!