How can you call custom database functions with Hibernate?

If you want to use your custom function in HQL, you'll need to define it in appropriate Dialect

Take a look at PostgreSQLDialect (or any other, really) source, and you'll see a bunch of registerFunction() calls. You'll need to add one more :-) - for your own custom function.

You'll then have to specify your own dialect in Hibernate configuration.

As of Hibernate 5, if you don't want to depend on or customize the dialect, you can define a MetadataBuilderInitializer. For example, to use MySQL DATE_ADD with an INTERVAL from HQL, you can define a custom function called date_add_interval:

public class DateAddIntervalMetadataBuilderInitializer
        implements MetadataBuilderInitializer {
    @Override
    public void contribute(MetadataBuilder metadataBuilder,
            StandardServiceRegistry serviceRegistry) {
        metadataBuilder.applySqlFunction("date_add_interval",
            new SQLFunctionTemplate(DateType.INSTANCE,
                "DATE_ADD(?1, INTERVAL ?2 ?3)"));
    }
}

You would also need to put the name of the class in a JAR resource file called META-INF/services/org.hibernate.boot.spi.MetadataBuilderInitializer.

This approach is particularly useful when using Hibernate via a framework such as JPA and/or Spring, where the configuration is performed implicitly by the framework.