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?
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.
