# Hibernate 6: custom SQL functions in Spring Data JPA repositories # 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](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**: ```sql 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: ```sql 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: ```java 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. ```java 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 { Collection findAllByTagsContaining(String tag); } ``` Quite familiar once again right? Well, I am sorry to tell you that t**his 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: ```plaintext 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: ```java @JdbcTypeCode(org.hibernate.type.SqlTypes.ARRAY) private Set 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. ```java 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 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 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. ```java 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**: ```plaintext src/main/resources/META-INF/services/org.hibernate.boot.model.FunctionContributor ``` And on that file, add our custom function contributor full class name: ```plaintext 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. ```java 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, JpaSpecificationExecutor { // HQL @Query("select p from Product p where array_contains(p.tags, :tag)") Collection findAllByTagsContaining(@Param("tag") String tag); // JPA specification default Collection 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. ```java 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. ```java @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 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 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](https://github.com/ArnauAregall/hibernate-6-custom-functions). If you enjoyed the article, let me know by dropping a star ⭐️ on the GitHub repository!