Register custom or non-standard SQL functions in Spring Boot JPA or Query DSL
JPA supports most of the standard SQL functions which can be used in writing queries. But how do we use the non-standard or custom DB functions in JPA/Hibernate? Native queries are one way to do that but how to use it if we are writing dynamic queries using Criteria API or Query DSL.
Registering the SQL function with JPA and MetadataBuilderContributor
Since Hibernate 5.2.18, you can use the MetadataBuilderContributor utility to customize the MetadataBuilder even if you are bootstrapping via JPA.
The MetadataBuilderContributor interface can be implemented like this:
In the above snippet, I am overriding the contribute method by providing one of the MySQL JSON functions.
The JSON_CONTAINS() function checks whether one JSON document contains another JSON document.
JSON_CONTAINS(target_json, candidate_json)
Parameters
target_jsonRequired. A JSON document.
candidate_jsonRequired. The included JSON document.
As you see, I have created a custom key “json_contains_key”. This actually will be used in Criteria API or Query DSL and will be parsed by Hibernate as JSON_CONTAINS().
And provide the custom MetadataBuilderContributor to Hibernate via the hibernate.metadata_builder_contributor configuration property.
spring.jpa.properties.hibernate.metadata_builder_contributor=com.abhicodes.customdialect.util.SQLFunctionContributorUsing Custom Function in Query DSL Query
Since JSON_CONTAINS() returns a boolean value, I am using Expressions.booleanTemplate to form the predicate, pass in the custom key, and the two required parameters: target JSON and candidate JSON. Thereafter I pass the predicate to the repository to fetch the values.
You can find a full working code on Github.
If you liked the article please take a minute to offer me a clap 👏 (you can clap multiple times), follow me, or even buy me a coffee https://www.buymeacoffee.com/abhiandy
