Arnau Aregall

Hibernate 6: custom SQL functions in Spring Data JPA repositories

Hibernate 6: custom SQL functions in Spring Data JPA repositories

Arnau Aregall's photo
Arnau Aregall
·

7 min read

Table of contents

Introduction

This article will walk through the process of configuring custom SQL functions using Hibernate 6 and Spring Data.

The goal is to demonstrate, using Hibernate 6 and Spring Data JPA:

  • How to declare our custom SQL functions on our codebase (not as database objects) using Hibernate API.

  • How to call our functions from our queries using Hibernate Query Language (HQL) or JPA specifications.

TLDR; Show me the code!

The full source code of the article is published on GitHub 😉

https://github.com/ArnauAregall/hibernate-6-custom-functions

Why would we need a custom Hibernate function?

On many occasions, I have found myself managing entities whose table column declarations are not what we know as basic types (varchar, bigint, etc).

If we are taking real benefit of Postgres, it's quite common that our table columns are arrays of any type or even JSON objects (jsonb).

When we need to query those types of columns from our application code, Spring Data query methods are quite limited even though those generate good criteria queries for most of our cases.

We often find ourselves in need of implementing a custom SQL function, which probably will make use of the native SQL syntax of our database platform or maybe even some database plugins.

To be able to call those functions in HQL queries or JPA specifications, we need to register the functions so Hibernate knows how to transform the HQL to SQL syntax.

Of course, a shortcut to solve the problem would be using native SQL syntax in our Spring repositories methods with @Query("select * from my_table where my_custom_function(my_column)", nativeQuery=true) and declaring our custom SQL functions as database objects. But as you know, every project has its own rules or restrictions, and maybe even that function needs to be used in a complex JPA specification - in those cases, we will be likely forced to register the function.

You may think that it's worth doing some extra weight-lifting in this regard when we have a non-trivial or very particular need in our business logic - in the next section, I will show you a trivial example that still requires custom functions.

Understanding by example: querying Products by tags

Let's imagine that our e-commerce application has a domain model named Product that represents a product we want to sell online.

It would have an internal ID and an SKU code, the product name and its price, and also some tags so we could quickly find similar products.

Our database schema table would have the following structure on Postgres:

CREATE TABLE IF NOT EXISTS product (
    id UUID UNIQUE NOT NULL,
    sku VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    price FLOAT NOT NULL,
    tags varchar[],
    PRIMARY KEY (id)
);

The table structure may sound quite familiar. The only particularity is we have a column named tags which is an array of varchars.

Let's say our application's functional requirement is to be able to filter Products by tags. Logically the criteria would be "find all products where the tags array contains electronics".

The SQL query using Postgres dialect would look as follows:

select p.* from products p where (p.tags @> '{"electronics"}');

On our application layer, we would first map the table to a JPA entity as follows:

package tech.aaregall.lab.hibernate6.persistence.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

import java.util.UUID;

@Entity
@Table(name = "product")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;
    private String sku;
    private String name;
    private Float price;
    private String[] tags;

    protected Product() {}

    public Product(String sku, String name, Float price, String[] tags) {
        this.sku = sku;
        this.name = name;
        this.price = price;
        this.tags = tags;
    }

    // getters & setters ommited for brevity
}

Then, using Spring Data JPA repositories, we would likely implement a query method to filter Products whose tags contain the required tag.

package tech.aaregall.lab.hibernate6.persistence.repository;

import org.springframework.data.repository.CrudRepository;
import tech.aaregall.lab.hibernate6.persistance.entity.Product;
import java.util.Collection;
import java.util.UUID;

public interface ProductRepository extends CrudRepository<Product, UUID> {

    Collection<Product> findAllByTagsContaining(String tag);

}

Quite familiar once again right?

Well, I am sorry to tell you that this approach will not work.

Our Spring application will fail when spinning up the context - the Spring Data JPA query lookup will fail while transforming the query method name to a JPA criteria by throwing the following exception:

org.hibernate.query.SemanticException: Operand of 'member of' operator must be a plural path

It would be logical to try changing the tags property mapping from a String[] to a type that JPA can recognize as a "plural" path on the criteria, as the error complains - maybe something like a collection:

@JdbcTypeCode(org.hibernate.type.SqlTypes.ARRAY)
private Set<String> tags;

Well, that will not work either - the column mapping would be compliant for JPA, but the same SemanticException will be thrown when starting our application. The query method findAllByTagsContaining still cannot be correctly transformed to a JPA criteria.

Is in this kind of situation that we may need to register custom SQL functions and implement our queries in HQL.

How to register custom functions in Hibernate 6

There are three key components or steps involved when registering custom functions:

    1. Our custom function implementation: a class containing the function rendering logic.
    1. The function contributor: a class that will register our custom function with a given function name.
    1. The Hibernate meta-information file: we will need to give a hint to Hibernate so it knows that we want to use at least one custom function contributor that takes care of registering our custom functions.

Implementing a custom function

Following the previous example, we will create a function that renders the SQL code to implement an "array contains" criteria.

The function will require exactly two arguments (the array and the value to look for in it) and will return a boolean.

This can be achieved by extending org.hibernate.dialect.function.StandardSQLFunction class and overriding the render method.

package tech.aaregall.lab.hibernate6.persistence.functions;

import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.sql.ast.SqlAstTranslator;
import org.hibernate.sql.ast.spi.SqlAppender;
import org.hibernate.sql.ast.tree.SqlAstNode;
import org.hibernate.type.BasicTypeReference;
import org.hibernate.type.SqlTypes;

import java.util.List;

class ArrayContainsSQLFunction extends StandardSQLFunction {

    private static final BasicTypeReference<Boolean> RETURN_TYPE = new BasicTypeReference<>("boolean", Boolean.class, SqlTypes.BOOLEAN);

    ArrayContainsSQLFunction(final String functionName) {
        super(functionName, true, RETURN_TYPE);
    }

    @Override
    public void render(SqlAppender sqlAppender, List<? extends SqlAstNode> arguments, SqlAstTranslator<?> translator) {
        if (arguments.size() != 2) {
            throw new IllegalArgumentException(STR."Function '\{getName()}' requires exactly 2 arguments");
        }

        sqlAppender.append("(");
        arguments.get(0).accept(translator);
        sqlAppender.append(" @> ARRAY[");
        arguments.get(1).accept(translator);
        sqlAppender.append("])");
    }

}

Registering the function with a FunctionContributor

We will register our custom function with the name array_contains.

We can achieve that by creating a new public class that implements the org.hibernate.boot.model.FunctionContributor interface, and overrides the contributeFunctions method.

package tech.aaregall.lab.hibernate6.persistence.functions;

import org.hibernate.boot.model.FunctionContributions;
import org.hibernate.boot.model.FunctionContributor;

public class CustomFunctionsContributor implements FunctionContributor {

    @Override
    public void contributeFunctions(FunctionContributions functionContributions) {
        functionContributions.getFunctionRegistry()
                .register("array_contains", new ArrayContainsSQLFunction("array_contains"));
    }
}

The Hibernate meta hint file

The last step will be creating the Hibernate meta hint file in our classpath to instruct we want to register a custom FunctionContributor:

src/main/resources/META-INF/services/org.hibernate.boot.model.FunctionContributor

And on that file, add our custom function contributor full class name:

tech.aaregall.lab.hibernate6.persistence.functions.CustomFunctionsContributor

Calling custom functions from Spring JPA Repositories

Now that we have our custom function registered in Hibernate, we can call it either using HQL syntax on @Query methods, or using a JPA specification in a Spring Data Repository.

package tech.aaregall.lab.hibernate6.persistence.repository;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import tech.aaregall.lab.hibernate6.persistance.entity.Product;

import java.util.Collection;
import java.util.UUID;

public interface ProductRepository extends CrudRepository<Product, UUID>, JpaSpecificationExecutor<Product> {

    // HQL
    @Query("select p from Product p where array_contains(p.tags, :tag)")
    Collection<Product> findAllByTagsContaining(@Param("tag") String tag);

    // JPA specification
    default Collection<Product> findAllByTagsContaining_Specification(final String tag) {
        return findAll((root, criteriaQuery, criteriaBuilder) ->
                criteriaBuilder.isTrue(
                        criteriaBuilder.function("array_contains", Boolean.class,
                                root.get("tags").as(String[].class), 
                                criteriaBuilder.literal(tag))
                )
        );
    }

}

Implementing repository integration tests

We will create an integration test configuration class that we will declare a Postgres Docker container bean, using Testcontainers.

package tech.aaregall.lab.hibernate6.config;

import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.utility.DockerImageName;

@Configuration
class TestConfig {

    @Bean
    @ServiceConnection
    PostgreSQLContainer<?> postgresContainer() {
        return new PostgreSQLContainer<>(DockerImageName.parse("postgres:latest"));
    }

}

And finally, implement the basic ProductRepository integration tests for our query methods.

@SpringBootTest
class ProductRepositoryTest {

    @Autowired
    private ProductRepository productRepository;

    @BeforeEach
    void beforeEach() {
        productRepository.deleteAll();
    }

    @Test
    void findAllByTagsContaining() {
        productRepository.saveAll(List.of(
                new Product("sku-111", "Dog Food", 30.99f, new String[] {"pets", "home", "dogs", "animals"}),
                new Product("sku-222", "Smart TV", 899.99f, new String[] {"electronics", "home"})
        ));

        final Collection<Product> result = productRepository.findAllByTagsContaining("electronics");

        assertThat(result)
                .hasSize(1)
                .extracting(Product::getSku)
                .containsExactly("sku-222");
    }

    @Test
    void findAllByTagsContaining_Specification() {
        productRepository.saveAll(List.of(
                new Product("sku-333", "Running Shoes", 64.55f, new String[] {"sport", "running", "footing"}),
                new Product("sku-444", "Swimsuit", 30.99f, new String[] {"sport", "swimming", "water polo"})
        ));

        final Collection<Product> result = productRepository.findAllByTagsContaining_Specification("swimming");

        assertThat(result)
                .hasSize(1)
                .extracting(Product::getSku)
                .containsExactly("sku-444");
    }

}

Conclusions

This article demonstrated:

  • Situations when registering custom SQL functions into Hibernate can be helpful.

  • Identifying the key components to register a custom SQL function using Hibernate 6.

  • How to implement a custom SQL function using Hibernate 6.

  • How to call our custom SQL function in Spring Data JPA repositories either by using an HQL query or a JPA specification.

  • How to implement simple yet assertive integration tests for our queries.

The source code of the example application is available in the linked GitHub repository.

If you enjoyed the article, let me know by dropping a star ⭐️ on the GitHub repository!

hibernateSpringbootJavaSQLpostgres