Calling Stored Procedures from Spring Data JPA Repositories – 从Spring Data JPA存储库调用存储程序

1. Overview

1.概述

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.

存储过程是存储在数据库中的一组预定义SQL语句。在Java中，有几种方法可以访问存储过程。在本教程中，我们将学习如何从Spring Data JPA存储库中调用存储过程。

2. Project Setup

2.项目设置

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:

我们将使用Spring Boot Starter Data JPA模块作为数据访问层。我们还将使用MySQL作为我们的后端数据库。因此，我们的项目pom.xml文件中需要Spring Data JPA、Spring Data JDBC和MySQL Connector依赖项。

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

一旦我们有了MySQL依赖性定义，我们就可以在application.properties文件中配置数据库连接。

spring.datasource.url=jdbc:mysql://localhost:3306/baeldung
spring.datasource.username=baeldung
spring.datasource.password=baeldung

3. Entity Class

3.实体类

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:

在Spring Data JPA中，实体代表存储在数据库中的表。因此，我们可以构建一个实体类来映射car数据库表。

@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

4.创建存储过程

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

5.参考存储库中的存储程序

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:

在Spring Data JPA中，资源库是我们提供数据库操作的地方。我们可以为Car实体的数据库操作构建一个资源库，并在这个资源库中引用存储过程。

@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

5.1.直接映射一个存储过程的名称

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

我们可以使用@Procedure注解定义一个存储过程方法，并直接映射存储过程的名称。

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注释的元素。

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

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

我们还可以使用procedureName属性来映射存储过程名称：。

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

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

最后，我们可以使用value属性来映射存储过程名称。

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

5.2. Reference a Stored Procedure Defined in Entity

5.2.引用实体中定义的存储过程

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

我们还可以使用@NamedStoredProcedureQuery注解来定义实体类中的存储过程：。

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

我们使用name属性来引用实体类中定义的存储过程。对于存储库方法，我们使用@Param来匹配存储过程的输入参数。我们还将存储过程的输出参数与资源库方法的返回值相匹配。

5.3. Reference a Stored Procedure With the @Query Annotation

5.3.用@Query注解来引用一个存储过程

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

我们也可以用@Query注解直接调用存储过程。

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

在这个方法中，我们使用一个本地查询来调用存储过程。我们将查询存储在注释的value属性中。

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.

同样地，我们使用@Param来匹配存储过程的输入参数。我们还将存储过程的输出映射到实体Car对象的列表中。

6. Summary

6.归纳总结

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.

在这篇文章中，我们探讨了如何通过JPA存储库访问存储过程。我们还讨论了两种简单的方法来引用JPA存储库中的存储过程。

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

一如既往，该文章的源代码可在GitHub上获取。