在 Spring Boot 中使用 JPA 调用自定义数据库函数

教程

1、概览

数据库函数是数据库管理系统的重要组成部分，可将逻辑和执行封装在数据库中。它们有助于高效的数据处理和操作。

本文将带你了解在 Spring Boot 应用中使用 Spring Data JPA 调用自定义数据库函数的各种方法。

2、项目设置

为了简单，本文使用 H2 数据库。

首先在 pom.xml 中加入 Spring Boot Data JPA 和 H2 依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
    <version>3.2.2</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.224</version>
</dependency>

3、数据库函数

数据库函数是通过在数据库中执行一组 SQL 语句或操作来执行特定任务的数据库对象。当逻辑是数据密集型的时候，这可以提高性能。虽然数据库函数和存储过程的操作类似，但它们也有不同之处。

3.1、函数与存储过程

虽然不同的数据库系统之间可能会有具体的差异，但它们之间的主要差异可归纳在下表中：

特点 数据库函数 存储过程
执行 可在查询中调用 必须明确调用
返回值 始终返回一个值 可返回无值、单值或多值
参数 仅支持输入参数 支持输入和输出参数
调用 无法使用函数调用存储过程 可使用存储过程调用函数
用途 通常执行计算或数据转换 通常用于复杂的业务逻辑

3.2、H2 函数

为了说明如何从 JPA 调用数据库函数，我们将创建一个在 H2 数据库中的数据库函数来说明如何从 JPA 中调用它。

H2 数据库函数只是一个嵌入的 Java 源代码，它将被编译和执行：

CREATE ALIAS SHA256_HEX AS '
    import java.sql.*;
    @CODE
    String getSha256Hex(Connection conn, String value) throws SQLException {
        var sql = "SELECT RAWTOHEX(HASH(''SHA-256'', ?))";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, value);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getString(1);
            }
        }
        return null;
    }
';

此数据库函数 SHA256_HEX 接受一个字符串输入参数，通过 SHA-256 哈希算法对其进行处理，然后返回其 SHA-256 哈希值的十六进制表示。

4、以存储过程的形式调用

第一种方法是调用类似于 JPA 中存储过程的数据库函数。我们可以通过 @NamedStoredProcedureQuery 来注解实体类。该注解允许我们直接在实体类中指定存储过程的元数据。

示例如下，展示了具有定义存储过程 SHA256_HEX 的 Product 实体类：

@Entity
@Table(name = "product")
@NamedStoredProcedureQuery(
  name = "Product.sha256Hex",
  procedureName = "SHA256_HEX",
  parameters = @StoredProcedureParameter(mode = ParameterMode.IN, name = "value", type = String.class)
)
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "product_id")
    private Integer id;

    private String name;

    // 构造函数、Getter、Setter 省略
}

在实体类上，用 @NamedStoredProcedureQuery 对 Product 实体类进行注解。将 Product.sha256Hex 指定为命名存储过程的名称。

在 Repository 定义中，用 @Procedure 对 Repository 方法进行注解，并引用 @NamedStoredProcedureQuery 的名称（name）。该 Repository 方法接收一个字符串参数，然后将其提供给数据库函数，并返回数据库函数调用的结果。

public interface ProductRepository extends JpaRepository<Product, Integer> {
    @Procedure(name = "Product.sha256Hex")
    String getSha256HexByNamed(@Param("value") String value);
}

在执行时，你可以看到 Hibernate 在 Hibernate 日志中调用它，就像调用存储过程一样：

Hibernate: 
    {call SHA256_HEX(?)}

@NamedStoredProcedureQuery 主要用于调用存储过程。数据库函数也可以单独调用，与存储过程类似。不过，对于与 SELECT 查询结合使用的数据库函数来说，它可能并不理想。

5、原生查询

调用数据库函数的另一种方法是通过原生（本地）查询。使用原生查询调用数据库函数有两种不同的方法。

5.1、原生 CALL

从之前的 Hibernate 日志中，我们可以看到 Hibernate 执行了一条 CALL 命令。同样，我们也可以使用相同的命令调用数据库函数：

public interface ProductRepository extends JpaRepository<Product, Integer> {
    @Query(value = "CALL SHA256_HEX(:value)", nativeQuery = true)
    String getSha256HexByNativeCall(@Param("value") String value);
}

执行结果与我们在使用 @NamedStoredProcedureQuery 的示例中看到的一样。

5.2、原生 SELECT

示例如下，定义一个 Repository 方法，该方法使用原生（native） SELECT 查询在 Product 表的 name 列上调用数据库函数：

public interface ProductRepository extends JpaRepository<Product, Integer> {
    @Query(value = "SELECT SHA256_HEX(name) FROM product", nativeQuery = true)
    String getProductNameListInSha256HexByNativeSelect();
}

在执行时，我们可以从 Hibernate 日志中看到与我们定义的相同查询，因为我们将其定义为原生查询：

Hibernate: 
    SELECT
        SHA256_HEX(name) 
    FROM
        product

6、函数注册

函数注册是 Hibernate 的一个过程，用于定义和注册自定义数据库函数，这些函数可以在 JPA 或 Hibernate 查询中使用。这有助于 Hibernate 将自定义函数转换为相应的SQL语句。

6.1、自定义方言

可以通过创建自定义方言来注册自定义函数。下面是自定义方言类，它继承了默认的 H2Dialect 并注册了我们的函数：

public class CustomH2Dialect extends H2Dialect {
    @Override
    public void initializeFunctionRegistry(FunctionContributions functionContributions) {
        super.initializeFunctionRegistry(functionContributions);
        SqmFunctionRegistry registry = functionContributions.getFunctionRegistry();
        TypeConfiguration types = functionContributions.getTypeConfiguration();

        new PatternFunctionDescriptorBuilder(registry, "sha256hex", FunctionKind.NORMAL, "SHA256_HEX(?1)")
          .setExactArgumentCount(1)
          .setInvariantType(types.getBasicTypeForJavaType(String.class))
          .register();
    }
}

当 Hibernate 初始化方言时，它通过调用 initializeFunctionRegistry() 方法向函数注册表中注册可用的数据库函数。我们可以重写 initializeFunctionRegistry() 方法来注册默认方言不包含的其他数据库函数。

PatternFunctionDescriptorBuilder 创建 JPQL 函数映射，将数据库函数 SHA256_HEX 映射到 JPQL 函数 sha256Hex，并将映射注册到函数注册表。参数 ?1 表示数据库函数的第一个输入参数。

6.2、Hibernate 配置

定义 HibernatePropertiesCustomizer 配置类， 它是由 Spring Boot 提供的一个接口，用于自定义 Hibernate 使用的属性。在配置类中，指定 Spring Boot 使用 CustomH2Dialect 而不是默认的 H2Dialect。

重写 customize() 方法，增加了一个属性，表示要使用 CustomH2Dialect：

@Configuration
public class CustomHibernateConfig implements HibernatePropertiesCustomizer {
    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put("hibernate.dialect", "com.baeldung.customfunc.CustomH2Dialect");
    }
}

Spring Boot 会在应用启动时自动检测并应用自定义配置。

6.3、Repository 方法

现在，可以在 Repository 中使用应用了新定义函数 sha256Hex 的 JPQL 查询，而不是原生查询：

public interface ProductRepository extends JpaRepository<Product, Integer> {
    @Query(value = "SELECT sha256Hex(p.name) FROM Product p")
    List<String> getProductNameListInSha256Hex();
}

在执行时检查 Hibernate 日志，可以发现 Hibernate 正确地将 JPQL sha256Hex 函数翻译成了数据库函数 SHA256_HEX：

Hibernate: 
    select
        SHA256_HEX(p1_0.name) 
    from
        product p1_07'

7、总结

本文先对数据库函数和存储过程进行了简要比较。两者都提供了封装逻辑并在数据库中执行的强大手段。

然后介绍了在 Spring Boot 应用中使用 Spring Data JPA 调用数据库函数的不同方法，包括利用 @NamedStoredProcedureQuery 注解、原生查询以及通过自定义方言进行函数注册。

通过将数据库函数纳入 Repository 方法，我们可以轻松构建数据库驱动的应用。

Ref：https://www.baeldung.com/spring-data-jpa-findby-multiple-columns