java - Registering a SQL function with JPA and Hibernate

Question

I would like to know what's the best way to register a custom SQL function with JPA/Hibernate.

Do I have to go through extending the MysqlInnodb dialect or is there a better way?

Can anyone please provide code samples and pointers to relevant documentation?

Answer 1

You might read articles telling you to register the SQL function by extending the Hibernate Dialect, but that's a naive solution.

Since Hibernate ORM 5.2.18 and 5.3.1, the best way to register a SQL function is to supply a MetadataBuilderContributor like this:

public class SqlFunctionsMetadataBuilderContributor 
        implements MetadataBuilderContributor {
         
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "group_concat",
            new StandardSQLFunction(
                "group_concat", 
                StandardBasicTypes.STRING
            )
        );
    }
}

Which you can pass to Hibernate via the hibernate.metadata_builder_contributor configuration property:

<property>
    name="hibernate.metadata_builder_contributor" 
    value="com.vladmihalcea.book.hpjp.hibernate.query.function.SqlFunctionsMetadataBuilderContributor"
</property>

Or, if you bootstrap Hibernate natively, you can just apply the SQL function to the MetadataBuilder during bootstrap.