Partner – JPA Buddy – NPI (tag=JPA/Hibernate)
announcement - icon

The right tools can and will save a lot of time. As long as you are using Hibernate and IntelliJ IDEA you can boost your coding speed and quality with JPA Buddy. It will help in a lot of the day-to-day work:

  • Creating JPA entities that follow best practices for efficient mapping
  • Creating DTOs from entities and MapStruct mappers using convenient visual tools
  • Generating entities from the existing database or Swagger-generated POJOs
  • Visually composing methods for Spring Data JPA repositories
  • Generating differential SQL to update your schema in accordance with your changes in entities
  • Autogenerating Flyway migrations and Liquibase changelogs comparing entities with the database or two databases
  • … and a lot more

Simply put, you'll learn and use the best practices of Hibernate and surrounding technology and become a lot more!

Definitely visit the JPA Buddy site to see its features in action closer.

Course – LSD (cat=Persistence)

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

>> CHECK OUT THE COURSE

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:

<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/baeldung
spring.datasource.username=baeldung
spring.datasource.password=baeldung

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. 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 the @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);

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:

@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. 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.

Course – LSD (cat=Persistence)

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

>> CHECK OUT THE COURSE
res – Persistence (eBook) (cat=Persistence)
Comments are closed on this article!