How can you call custom database functions with Hibernate?

Question

If I were to define some function in the database (perhaps Postgres, or any other database):

create or replace function isValidCookie(ckie);

I would call it from SQL as:

select * from cookietable c where isValidCookie(c.cookie);

How can I call a custom function such as this from Hibernate?

I know you can use a view same as you would a table (just use mutable in the xml), but to call a function would be very handy — Zoidberg, Dec 15 '09 at 18:14
You may be able to do that using native queries. This [doc](http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html#sp_query) explains. — Vincent Ramdhanie, Dec 15 '09 at 18:16

ChssPly76 · Accepted Answer · 2012-08-10T20:11:50.220

16

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.

edited Aug 10 '12 at 20:11

answered Dec 15 '09 at 18:28

ChssPly76

99,456
24
206
195

score 7 · Answer 2 · answered Dec 28 '16 at 21:20

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.

Hey I need exactly this feature to support Mariadb COLUMN_GET and TIMESTAMPDIFF functions. Currently hibernate tries to prefix the function parameters with the query alias. Where do I have to register these classes in Spring Boot? — Pascal, Mar 20 '17 at 10:43

How can you call custom database functions with Hibernate?

2 Answers2

Linked