如何使用 Spring Data JPA 在存储过程中使用 IN、OUT 和 INOUT 参数

介绍

在本教程中，我将向您展示如何使用 Spring Data JPA 在存储过程中使用 IN、OUT 和 INOUT 参数。我还将调用一个完全没有参数的存储过程。

我将使用 MySQL 服务器，因此 MySQL 服务器可能存在一些限制，或者 Spring Data JPA 本身在调用存储过程时可能存在一些限制，但我至少会向您展示如何根据传递给存储过程的参数类型以不同的方式调用存储过程。

例如，与Oracle数据库不同，MySQL服务器不支持，因此您不能在实体类中使用参数类型。在这种情况下，您不能使用 Spring Data JPA 存储库样式方法来调用存储过程，您需要作为本机查询调用，或者您需要从中创建实例。REF_CURSOR``REF_CURSOR``@NamedStoredProcedureQuery``StoredProcedureQuery``EntityManager

相关文章：

• 使用休眠的存储过程
• 使用 Spring SimpleJdbcCall 的存储过程
• 使用 Spring StorageProcedure 的存储过程

先决条件

Java 至少 8， Gradle 6.1.1 – 6.7.1， Maven 3.6.3， MySQL 8.0.17 – 8.0.22， Spring Data JPA 2.2.5 – 2.4.3

项目设置

您可以在自己喜欢的 IDE 或工具中创建基于 gradle 或 maven 的项目。该项目的名称是spring-data-jpa-storage-procedure。

如果你在Eclipse中创建基于gradle的项目，那么你可以使用下面的build.gradle脚本：

buildscript {
    ext {
        springBootVersion = '2.2.5.RELEASE' to 2.4.3
    }
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}
plugins {
    id 'java-library'
    id 'org.springframework.boot' version "${springBootVersion}"
}
sourceCompatibility = 12
targetCompatibility = 12
repositories {
    mavenCentral()
}
dependencies {
    implementation("org.springframework.boot:spring-boot-starter:${springBootVersion}")
    implementation("org.springframework.boot:spring-boot-starter-data-jpa:${springBootVersion}")
    implementation('mysql:mysql-connector-java:8.0.17') to 8.0.22
    //required only if jdk 9 or higher version is used
    runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}

如果你在Eclipse中创建基于maven的项目，那么你可以使用下面的pom.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.roytuts</groupId>
    <artifactId>spring-data-jpa-stored-procedure</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>12</maven.compiler.source>
        <maven.compiler.target>12</maven.compiler.target>
    </properties>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.5.RELEASE to 2.4.3</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>javax.xml.bind</groupId>
            <artifactId>jaxb-api</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

MySQL 表

我将在roytuts数据库下创建一个名为user_details到 MySQL 服务器的表。

CREATE TABLE IF NOT EXISTS `user_details` (
  `id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `dob` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

现在，我将立即转储一些数据以测试我们的应用程序。

INSERT INTO `user_details` (`id`, `first_name`, `last_name`, `email`, `dob`) VALUES
    (7, 'Soumitra', 'Roy', 'contact@roytuts.com', '30-08-2016'),
    (8, 'Souvik', 'Sanyal', 'souvik.sanyal@email.com', '30-09-1991'),
    (9, 'Abc', 'Xyz', 'abc@xyz.com', '12-01-1998'),
    (10, 'Liton', 'Sarkar', 'liton@email.com', '30-08-2012');

存储过程

我将创建三个过程来处理存储过程中的 IN、OUT 和 INOUT 参数。

下面的存储过程从表中返回多行，我没有向其传递任何参数。

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_users`()
BEGIN
    SELECT * FROM user_details;
END//
DELIMITER ;

下面的存储过程采用两个参数 – IN 和 OUT。In 参数接受输入，OUT 参数提供输出。

以下存储过程在连接名字和姓氏后返回用户的全名。

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user_full_name_in_out`(
    IN `user_id` INT,
    OUT `full_name` VARCHAR(50)
)
BEGIN
    SELECT concat(first_name, ' ', last_name) into full_name FROM user_details WHERE id = user_id;    
END//
DELIMITER ;

以下存储过程仅接受一个参数 INOUT，此参数接受输入并提供输出。

以下过程还会在连接名字和姓氏后返回用户的全名。

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user_full_name_inout`(
    INOUT `in_out` VARCHAR(50)
)
BEGIN
    SELECT concat(first_name, ' ', last_name) INTO in_out FROm user_details WHERE dob = in_out;
END//
DELIMITER ;

调用存储过程

现在，您将了解如何调用存储过程，如何将输入传递给 In 和 INOUT 参数，以及如何从 OUT 和 INOUT 参数接收输出。

get_users

此过程列出表user_details 中的所有用户。只需执行 commandon 数据库服务器即可获得以下结果：call get_users()

现在您将看到如何从 Spring 应用程序调用。

假设您有以下存储库接口，它扩展了 Spring 的存储库接口：

package spring.data.jpa.stored.procedure.repository;
import java.util.List;
import javax.transaction.Transactional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;
import spring.data.jpa.stored.procedure.entity.UserDetails;
@Transactional
public interface UserDetailsJpaRepository extends JpaRepository<UserDetails, Integer> {
    @Query(value = "call get_users()", nativeQuery = true)
    List<UserDetails> findUserDetailsList();
    @Procedure(procedureName = "get_user_full_name_in_out", outputParameterName = "full_name")
    String findUserFullNameIn_OutUsingName(@Param("user_id") Integer in);
}

可以使用@Query注释通过以下代码行将过程作为本机查询调用。

@Query(value = "call get_users()", nativeQuery = true)
List<UserDetails> findUserDetailsList();

还可以使用EntityManager以其他方式调用存储过程。假设您有以下存储库类。

package spring.data.jpa.stored.procedure.repository;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;
import org.springframework.stereotype.Repository;
import spring.data.jpa.stored.procedure.entity.UserDetails;
@Repository
public class UserDetailsRepository {
    @PersistenceContext
    private EntityManager entityManager;
    public List<UserDetails> findUserDetailsListUsingAlias() {
        StoredProcedureQuery users = entityManager.createNamedStoredProcedureQuery("getUsers");
        return users.getResultList();
    }
    public List<UserDetails> findUserDetailsListUsingName() {
        StoredProcedureQuery users = entityManager.createStoredProcedureQuery("get_users");
        return users.getResultList();
    }
    public String findUserFullNameInOutUsingName(String dob) {
        StoredProcedureQuery q = entityManager.createStoredProcedureQuery("get_user_full_name_inout");
        q.registerStoredProcedureParameter("in_out", String.class, ParameterMode.INOUT);
        q.setParameter("in_out", dob);
        return q.getOutputParameterValue("in_out").toString();
    }
    public String findUserFullNameIn_OutUsingName(Integer in) {
        StoredProcedureQuery q = entityManager.createStoredProcedureQuery("get_user_full_name_in_out");
        q.registerStoredProcedureParameter("user_id", Integer.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("full_name", String.class, ParameterMode.OUT);
        q.setParameter("user_id", in);
        return q.getOutputParameterValue("full_name").toString();
    }
}

现在，可以使用两种不同的方法来调用存储过程：

@PersistenceContext
private EntityManager entityManager;
public List<UserDetails> findUserDetailsListUsingAlias() {
    StoredProcedureQuery users = entityManager.createNamedStoredProcedureQuery("getUsers");
    return users.getResultList();
}
public List<UserDetails> findUserDetailsListUsingName() {
    StoredProcedureQuery users = entityManager.createStoredProcedureQuery("get_users");
    return users.getResultList();
}

get_user_full_name_in_out

可以使用以下命令从存储过程中获取结果：

CALL `get_user_full_name_in_out`('7', @full_name);
SELECT @full_name;

上面的命令将为您提供以下结果：

现在我将从春季应用程序调用。您可以使用以下代码行从 Spring Data JPA 存储库接口调用：UserDetailsJpaRepository

@Procedure(procedureName = "get_user_full_name_in_out", outputParameterName = "full_name")
String findUserFullNameIn_OutUsingName(@Param("user_id") Integer in);

您还可以在UserDetailsRepository类中使用以下代码片段：

public String findUserFullNameIn_OutUsingName(Integer in) {
    StoredProcedureQuery q = entityManager.createStoredProcedureQuery("get_user_full_name_in_out");
    q.registerStoredProcedureParameter("user_id", Integer.class, ParameterMode.IN);
    q.registerStoredProcedureParameter("full_name", String.class, ParameterMode.OUT);
    q.setParameter("user_id", in);
    return q.getOutputParameterValue("full_name").toString();
}

get_user_full_name_inout

您可以在 MySQL 服务器中使用以下命令调用此存储过程：

SET @in_out = '30-08-2016';
CALL `get_user_full_name_inout`(@in_out);
SELECT @in_out;

上述命令将为您提供以下输出：

以下代码片段写入UserDetailsRepository类：

public String findUserFullNameInOutUsingName(String dob) {
    StoredProcedureQuery q = entityManager.createStoredProcedureQuery("get_user_full_name_inout");
    q.registerStoredProcedureParameter("in_out", String.class, ParameterMode.INOUT);
    q.setParameter("in_out", dob);
    return q.getOutputParameterValue("in_out").toString();
}

实体类

下面给出了在上述存储库接口和类中使用的相应实体类。

在类上，我使用@NamedStoredProcedureQueries注释声明了存储过程。

我使用@Column注释指定了和 Java 属性不同的列名。

@Entity
@Table(name = "user_details")
@NamedStoredProcedureQueries({
        @NamedStoredProcedureQuery(name = "getUsers", procedureName = "get_users", resultClasses = {
                UserDetails.class }) })
//@NamedStoredProcedureQuery(name = "getUsers", procedureName = "get_users", resultClasses = { UserDetails.class })
public class UserDetails implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Column
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "first_name")
    private String firstName;
    @Column(name = "last_name")
    private String lastName;
    @Column
    private String dob;
        //getters and setters
}

服务类

相应的服务类可以写成：

package spring.data.jpa.stored.procedure.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import spring.data.jpa.stored.procedure.entity.UserDetails;
import spring.data.jpa.stored.procedure.repository.UserDetailsJpaRepository;
import spring.data.jpa.stored.procedure.repository.UserDetailsRepository;
@Service
public class UserService {
    @Autowired
    private UserDetailsJpaRepository jpaRepository;
    @Autowired
    private UserDetailsRepository repository;
    public String findUserFullNameIn_OutUsingName(Integer in) {
        return jpaRepository.findUserFullNameIn_OutUsingName(in);
    }
    public List<UserDetails> getUserListUsingNativeQuery() {
        return jpaRepository.findUserDetailsList();
    }
    public List<UserDetails> getUserDetailsListUsingProcAlias() {
        return repository.findUserDetailsListUsingAlias();
    }
    public List<UserDetails> getUserDetailsListUsingProcName() {
        return repository.findUserDetailsListUsingAlias();
    }
    public String getUserFullNameInOutUsingProcName(String dob) {
        return repository.findUserFullNameInOutUsingName(dob);
    }
    public String getUserFullNameIn_OutUsingProcName(int in) {
        return repository.findUserFullNameIn_OutUsingName(in);
        // return repository.findUserFullNameIn_OutUsingName(in);
    }
}

主类

一个带有 main 方法的类足以运行我们的 Spring Boot 应用程序。我在这里使用独立应用程序。

package spring.data.jpa.stored.procedure;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import spring.data.jpa.stored.procedure.service.UserService;
@SpringBootApplication
@EnableJpaRepositories(basePackages = "spring.data.jpa.stored.procedure.repository")
public class SpringDataJpaStoredProcedureApp implements CommandLineRunner {
    @Autowired
    private UserService service;
    public static void main(String[] args) {
        SpringApplication.run(SpringDataJpaStoredProcedureApp.class, args);
    }
    @Override
    public void run(String... args) throws Exception {
        System.out.println("===========================================");
        System.out.println("User List using Native Query");
        service.getUserListUsingNativeQuery().stream().forEach(
                u -> System.out.println(u.getId() + " " + u.getFirstName() + " " + u.getLastName() + " " + u.getDob()));
        System.out.println("===========================================");
        System.out.println("User List using Procedure name");
        service.getUserDetailsListUsingProcName().stream().forEach(
                u -> System.out.println(u.getId() + " " + u.getFirstName() + " " + u.getLastName() + " " + u.getDob()));
        System.out.println("===========================================");
        System.out.println("User List using Procedure alias");
        service.getUserDetailsListUsingProcAlias().stream().forEach(
                u -> System.out.println(u.getId() + " " + u.getFirstName() + " " + u.getLastName() + " " + u.getDob()));
        System.out.println("===========================================");
        System.out.println(
                "IN and OUT parameters using Procedure name: " + service.getUserFullNameIn_OutUsingProcName(7));
        System.out.println("===========================================");
        System.out.println(
                "INOUT parameter using Procedure name: " + service.getUserFullNameInOutUsingProcName("30-08-2016"));
        System.out.println("===========================================");
        System.out.println(service.findUserFullNameIn_OutUsingName(10));
    }
}

数据库配置

将以下数据库详细信息放入类路径目录src/main/resources下的application.properties文件中，以连接您的 MySQL 服务器。确保根据数据库详细信息进行更改：

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/roytuts
spring.datasource.username=root
spring.datasource.password=root

测试应用程序

执行上述主类将给出以下输出：

===========================================
User List using Native Query
7 Soumitra Roy 30-08-2016
8 Souvik Sanyal 30-09-1991
9 Abc Xyz 12-01-1998
10 Liton Sarkar 30-08-2012
===========================================
User List using Procedure name
7 Soumitra Roy 30-08-2016
8 Souvik Sanyal 30-09-1991
9 Abc Xyz 12-01-1998
10 Liton Sarkar 30-08-2012
===========================================
User List using Procedure alias
7 Soumitra Roy 30-08-2016
8 Souvik Sanyal 30-09-1991
9 Abc Xyz 12-01-1998
10 Liton Sarkar 30-08-2012
===========================================
IN and OUT parameters using Procedure name: Soumitra Roy
===========================================
INOUT parameter using Procedure name: Soumitra Roy
===========================================
Liton Sarkar

存储库接口/类和服务层与任何其他层分离，您可以轻松地注入到任何层中。

这就是如何使用 Spring Data JPA 框架在存储过程中使用 IN、OUT 和 IN OUT 参数的全部内容。

源代码

下载