1、概览 {#1概览}

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

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

2、项目设置 {#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、数据库函数 {#3数据库函数}

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

3.1、函数与存储过程 {#31函数与存储过程}

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

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

3.2、H2 函数 {#32h2-函数}

为了说明如何从 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、以存储过程的形式调用 {#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原生查询}

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

5.1、原生 CALL {#51原生-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 {#52原生-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、函数注册 {#6函数注册}

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

6.1、自定义方言 {#61自定义方言}

可以通过创建自定义方言来注册自定义函数。下面是自定义方言类，它继承了默认的 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 配置 {#62hibernate-配置}

定义 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 方法 {#63repository-方法}

现在，可以在 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、总结 {#7总结}

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

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

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

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