Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can you call custom database functions with Hibernate?

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?

like image 296
jsight Avatar asked Dec 15 '09 18:12

jsight


People also ask

How do you call a function in hibernate?

You can call Hibernate's @NamedNativeQuery in the same way as you call any other named query. You only need to call the createNamedQuery of your EntityManager with the name of the query, set all bind parameter values and call the getSingleResult or getResultList method. TypedQuery<Review> q = em.

How do you call a function in a database?

To call an aggregate function directly, you must pass an ObjectQuery<T> to the function. For more information, see the second example below. The methods in the SqlFunctions class are specific to SQL Server functions. Similar classes that expose database functions may be available through other providers.


2 Answers

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.

like image 146
ChssPly76 Avatar answered Sep 23 '22 05:09

ChssPly76


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.

like image 30
Trevor Robinson Avatar answered Sep 22 '22 05:09

Trevor Robinson