Calling Custom Database Functions With JPA and Spring Boot – 使用 JPA 和 Spring Boot 调用自定义数据库函数

最后修改: 2024年 2月 24日

中文/混合/英文(键盘快捷键:t)

1. Overview

1.概述

Database functions are essential components in database management systems, enabling the encapsulation of logic and execution within the database. They facilitate efficient data processing and manipulation.

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

In this tutorial, we’ll explore various approaches to calling custom database functions within JPA and Spring Boot applications.

在本教程中,我们将探讨在 JPA 和 Spring Boot 应用程序中调用自定义数据库函数的各种方法。

2. Project Setup

2. 项目设置

We’ll demonstrate the concepts in the subsequent sections using the H2 database.

我们将在后续章节中使用 H2 数据库演示这些概念。

Let’s include Spring Boot Data JPA and H2 dependencies in our pom.xml:

让我们在 pom.xml 中加入 Spring Boot Data JPAH2 依赖项:

<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. Database Functions

3. 数据库功能

Database functions are database objects that perform specific tasks by executing a set of SQL statements or operations within the database. This enhances the performance when the logic is data intensive. Although database functions and stored procedures operate similarly, they exhibit differences.

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

3.1. Functions vs. Stored Procedures

3.1.函数与存储过程

While different database systems may have specific differences between them, the major differences between them can be summarized in the following table:

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

Feature Database Functions Stored Procedures
Invocation Can be invoked within queries Must be called explicitly
Return Value Always return a single value May return none, single, or multiple values
Parameter Support input parameters only Support both input and output parameters
Calling Cannot call stored procedures with a function Can call functions with a stored procedure
Usage Typically perform calculations or data transformations Often used for complex business logic

3.2. H2 Function

3.2.H2 功能

To illustrate invoking database functions from JPA, we’ll create a database function in H2 to illustrate how to invoke it from JPA. The H2 database function is simply embedded Java source code that will be compiled and executed:

为了说明如何从 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;
    }
';

This database function SHA256_HEX accepts a single input argument as a string, processing it through the SHA-256 hashing algorithm, and subsequently returns the hexadecimal representation of its SHA-256 hash.

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

4. Invoking as a Stored Procedure

4.作为存储过程调用

The first approach is to invoke database functions similar to stored procedures within JPA. We accomplish it by annotating the entity class by @NamedStoredProcedureQuery. This annotation allows us to specify the metadata of a stored procedure directly within the entity class.

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

Here’s an example of the Product entity class with the defined stored procedure SHA256_HEX:

下面是一个使用已定义存储过程 SHA256_HEXProduct 实体类的示例:

@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
}

In the entity class, we annotate our Product entity class with @NamedStoredProcedureQuery. We assign Product.sha256Hex as the name of the named stored procedure.

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

In our repository definition, we annotate the repository method with the @Procedure and refer to the name of our @NamedStoredProcedureQuery. This repository method takes a string argument, then supplies it to the database function, and returns the result of the database function call.

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

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

We’ll see Hibernate invoking it like calling a stored procedure from the Hibernate log upon execution:

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

Hibernate: 
    {call SHA256_HEX(?)}

@NamedStoredProcedureQuery is primarily designed for invoking stored procedures. The database functions can be invoked alone, similar to stored procedures as well. However, it may not be ideal for database functions used in conjunction with select queries.

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

5. Native Query

5. 本地查询

Another approach to call database functions is through native queries. There are two different ways to invoke database functions using a native query.

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

5.1. Native Call

5.1.本地调用

From the previous Hibernate log, we can see that Hibernate executed a CALL command. Likewise, we can use the same command to invoke our database function natively:

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

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

The execution result will be the same as we saw in the example of using @NamedStoredProcedureQuery.

执行结果将与我们在使用 @NamedStoredProcedureQuery 的示例中看到的相同。

5.2. Native Select

5.2.本地选择

As we depicted before, we couldn’t use it in conjunction with select queries. We’ll switch it to a select query and apply the function to a column value from a table. In our example, we define a repository method that uses a native select query to invoke our database function on the name column of the Product table:

正如我们之前所描述的,我们不能将它与选择查询结合使用。我们将把它转换为选择查询,并将函数应用于表中的列值。在我们的示例中,我们定义了一个存储库方法,该方法使用本地选择查询在 Product 表的 name 列上调用我们的数据库函数:

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

Upon execution, we can get the identical query from the Hibernate log as we defined because we defined it as a native query:

执行时,我们可以从 Hibernate 日志中获得与我们定义的相同的查询,因为我们将其定义为本地查询:

Hibernate: 
    SELECT
        SHA256_HEX(name) 
    FROM
        product

6. Function Registration

6.功能注册

Function registration is a Hibernate process of defining and registering custom database functions that can be used within JPA or Hibernate queries. This helps Hibernate translate the custom functions into the corresponding SQL statements.

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

6.1. Custom Dialect

6.1.自定义方言

We can register custom functions by creating a custom dialect. Here’s the custom dialect class that extends the default H2Dialect and registers our function:

我们可以通过创建自定义方言来注册自定义函数。下面的自定义方言类扩展了默认的 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();
    }
}

When Hibernate initializes a dialect, it registers available database functions to the function registry via initializeFunctionRegistry(). We override the initializeFunctionRegistry() method to register additional database functions that the default dialect doesn’t include.

当 Hibernate 初始化一个方言时,它会通过 initializeFunctionRegistry() 将可用的数据库函数注册到函数注册表中。我们重载 initializeFunctionRegistry() 方法,以注册默认方言不包含的其他数据库函数。

PatternFunctionDescriptorBuilder creates a JPQL function mapping that maps our database functions SHA256_HEX to a JPQL function sha256Hex and registers the mapping to the function registry. The argument ?1 indicates the first input argument for the database function.

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

6.2. Hibernate Configuration

6.2.冬眠配置

We have to instruct Spring Boot to adopt the CustomH2Dialect instead of the default H2Dialect. Here the HibernatePropertiesCustomizer that comes in place. It’s an interface provided by Spring Boot to customize properties used by Hibernate.

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

We override the customize() method to put an additional property to indicate we’ll use CustomH2Dialect:

我们重载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 automatically detects and applies the customization during the application start-up.

Spring Boot 会在应用程序启动时自动检测并应用定制。

6.3. Repository Method

6.3.存储库方法

In our repository, we can now use the JPQL query that applies the newly defined function sha256Hex instead of the native query:

现在,我们可以在版本库中使用 JPQL 查询,该查询应用了新定义的函数 sha256Hex,而不是本地查询:

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

When we check the Hibernate log upon the execution, we see that Hibernate correctly translated the JPQL sha256Hex function to our database function SHA256_HEX:

当我们在执行时检查 Hibernate 日志时,会发现 Hibernate 正确地将 JPQL sha256Hex 函数翻译成了我们的数据库函数 SHA256_HEX

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

7. Conclusion

7.结论

In this article, we conducted a brief comparison between database functions and stored procedures. Both offer a powerful means to encapsulate logic and execute within the database.

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

Moreover, we have explored different approaches to call database functions, including utilizing @NamedStoredProcedureQuery annotation, native queries, and function registration via custom dialects. By incorporating database functions into repository methods, we can easily build database-driven applications.

此外,我们还探索了调用数据库函数的不同方法,包括利用 @NamedStoredProcedureQuery 注释、本地查询以及通过自定义方言进行函数注册。通过将数据库函数纳入存储库方法,我们可以轻松构建数据库驱动的应用程序。

As usual, the examples providing a practical resource of the concepts discussed are available over on GitHub.

与往常一样,提供所讨论概念的实用资源的示例可在 GitHub 上获取。