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

数据库函数是数据库管理系统中的重要组件， 将逻辑和执行封装在数据库中。它们促进高效的数据处理和操作。

依赖：
让我们在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>

数据库函数
数据库函数是通过在数据库中执行一组 SQL 语句或操作来执行特定任务的数据库对象。当逻辑是数据密集型时，这可以提高性能。尽管数据库函数和存储过程的操作类似，但它们却存在差异。

数据库函数 vs. 存储过程
虽然不同的数据库系统之间可能存在特定的差异，但它们之间的主要差异可以：

  • 数据库函数通常执行计算或数据转换    
  • 存储过程通常用于复杂的业务逻辑

数据库函数案例
为了说明从 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 哈希的十六进制表示形式。

作为存储过程调用
第一种方法是调用类似于 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;
    // constructor, getters and setters
}

在实体类中，我们使用@NamedStoredProcedureQuery注释我们的Product实体类。我们将Product.sha256Hex指定为命名存储过程的名称。

在我们的存储库定义中，我们使用@Procedure注释存储库方法，并引用@NamedStoredProcedureQuery的名称。此存储库方法采用字符串参数，然后将其提供给数据库函数，并返回数据库函数调用的结果。

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

我们将看到 Hibernate 调用它就像在执行时从 Hibernate 日志调用存储过程一样：

Hibernate: 
    {call SHA256_HEX(?)}

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

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

本地调用
从前面的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 的示例中看到的相同。

本机选择
正如我们之前所描述的，我们不能将它与选择查询结合使用。我们将其切换为选择查询并将该函数应用于表中的列值。在我们的示例中，我们定义了一个存储库方法，该方法使用本机选择查询来调用Product表的名称列上的数据库函数：

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

函数注册
函数注册是定义和注册可在 JPA 或 Hibernate 查询中使用的自定义数据库函数的 Hibernate 过程。这有助于 Hibernate 将自定义函数翻译成相应的 SQL 语句。

自定义方言
我们可以通过创建自定义方言来注册自定义函数。这是扩展默认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指示数据库函数的第一个输入参数。

Hibernate配置
我们必须指示 Spring Boot 采用CustomH2Dialect而不是默认的H2Dialect。这里HibernatePropertiesCustomizer就位了。它是 Spring Boot 提供的一个接口，用于自定义 Hibernate 使用的属性。

我们重写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 在应用程序启动期间自动检测并应用自定义。

存储库方法
在我们的存储库中，我们现在可以使用应用新定义的函数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'

结论
在本文中，我们对数据库函数和存储过程进行了简要比较。两者都提供了封装逻辑并在数据库中执行的强大方法。

此外，我们还探索了调用数据库函数的不同方法，包括利用@NamedStoredProcedureQuery注释、本机查询和通过自定义方言进行函数注册。通过将数据库功能合并到存储库方法中，我们可以轻松构建数据库驱动的应用程序。