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

1. 概述

数据库函数是数据库管理系统的核心组件，通过封装逻辑并在数据库内部执行，实现高效的数据处理和操作。本文将探讨在JPA和Spring Boot应用中调用自定义数据库函数的多种实现方式。

2. 项目配置

我们将使用H2数据库演示后续概念。在pom.xml中添加以下依赖：

<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. 函数与存储过程对比

不同数据库系统的具体实现可能不同，但主要差异可总结为下表：

特性	数据库函数	存储过程
调用方式	可在查询中调用	必须显式调用
返回值	总是返回单个值	可返回0个、1个或多个值
参数	仅支持输入参数	支持输入和输出参数
调用关系	不能调用存储过程	可调用函数
典型用途	计算或数据转换	复杂业务逻辑

3.2. H2函数示例

为演示JPA调用数据库函数，我们在H2中创建一个示例函数。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算法计算哈希值，并返回其十六进制表示。

4. 通过存储过程方式调用

第一种方式是将数据库函数当作存储过程调用。通过在实体类上使用@NamedStoredProcedureQuery注解实现，该注解允许直接在实体类中定义存储过程元数据。

以下是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
}

在Repository中，使用@Procedure注解引用已定义的存储过程：

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

执行时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调用

当需要在SELECT查询中使用函数时，可采用原生SELECT查询。以下示例对Product表的name列应用函数：

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

Hibernate日志会显示完全匹配的SQL：

Hibernate: 
    SELECT
        SHA256_HEX(name) 
    FROM
        product

6. 函数注册方式

函数注册是Hibernate定义和注册自定义数据库函数的过程，使其可在JPQL或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()注册可用函数。我们重写该方法以添加默认方言不包含的函数。

PatternFunctionDescriptorBuilder创建JPQL函数映射，将数据库函数SHA256_HEX映射为JPQL函数sha256hex。参数?1表示函数的第一个输入参数。

6.2. Hibernate配置

需指示Spring Boot使用CustomH2Dialect替代默认方言。通过HibernatePropertiesCustomizer实现：

@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中使用JPQL查询调用新注册的函数：

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

执行时Hibernate日志显示正确转换：

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

7. 总结

本文简要对比了数据库函数与存储过程的差异，两者都是封装数据库逻辑的强大工具。我们探讨了三种调用数据库函数的方式：

✅ 使用@NamedStoredProcedureQuery注解
✅ 原生查询（CALL/SELECT）
✅ 通过自定义方言注册函数

通过在Repository方法中集成数据库函数，可以高效构建数据驱动的应用。本文示例代码可在GitHub获取。

Persistence

REST

Security