Calling Custom Database Functions With JPA and Spring Boot

1. Overview

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.

2. Project Setup

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

Let’s include Spring Boot Data JPA and H2 dependencies in our 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. Database Functions

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.

3.1. Functions vs. Stored Procedures

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

3.2. H2 Function

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:

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.

4. Invoking as a Stored Procedure

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.

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

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

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.

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: 
    {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.

5. Native Query

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

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:

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.

5.2. Native Select

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:

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: 
    SELECT
        SHA256_HEX(name) 
    FROM
        product

6. Function Registration

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.

6.1. Custom Dialect

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:

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.

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.

6.2. Hibernate Configuration

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.

We override the customize() method to put an additional property to indicate we’ll use 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.

6.3. Repository Method

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

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: 
    select
        SHA256_HEX(p1_0.name) 
    from
        product p1_07'

7. Conclusion

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.

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