Featured Image with Sidebar

How to call custom database functions with JPA and Hibernate

By Thorben Janssen

Query

JPA supports a set of database functions which you can use to perform small operations and transformations within a query. This is often easier and faster than doing it in the Java code.

But how do you call functions which are not supported by the JPA standard, like database-specific ones or the custom functions implemented by your database team?

Native queries are of course one option. Another one is the function function() which was introduced in JPA 2.1 and allows you to call any function in a JPQL or Criteria query.

Database function

Let’s have a look at the custom database function, before I show you the Java code. As you can see in the following code snippet, the function is pretty simple. It takes two input parameters of type double and calculates their sum.

Call a custom database function

JPA 2.1 introduced function() to call database functions which are not directly supported by the standard. As you can see in the following code snippet, the syntax is pretty easy. You provide the name of the function as the first parameter and then all parameters of the custom function. In this example, the name of the function is “calculate” and I provide the numbers 1 and 2 as parameters.

Cheat Sheet: Get your free cheat sheet with all features added in JPA 2.1 (incl. function())

Custom function calls in the WHERE part of the query work out of the box with Hibernate because it can guess the return type. But if you want to call the function in the SELECT part, like in the following code snippet, you have to register the function first.

Register function in Hibernate dialect

One option to do this is to create a custom dialect. Hibernate provides specific dialects for most common databases to support database specific features, like functions, data types, and SQL dialects. Most database specific functions are already supported by the specific Hibernate dialect. So please have a look at it, before you try to register the function yourself.

The definition of a custom function is not that difficult, as you can see in the following code snippet. You can extend an existing dialect and register your function in the constructor by calling the registerFunction(String name, SQLFunction function) method.

I use a PostgreSQL database in this example and therefore extend the PostgreSQL94Dialect, which already supports the database specific features. The only thing I have to do is to register my custom calculate function in the constructor. I register it as a StandardSQLFunction which dynamically defines the return type based on the type of the first parameter.

And then I have to reference the new dialect in the persistence.xml file.

Conclusion

JPA 2.1 introduced the generic function function() to call any database function in a JPQL or Criteria query, even if the function is not directly supported by the JPA standard.

If you use Hibernate as your JPA implementation, you have to also register the function in the dialect to use it in the SELECT part of the query. You therefore should extend an existing dialect for your specific database and register the additional functions in the constructor.

If you want to learn more about the features introduced in JPA 2.1, have a look at JPA 2.1 – 12 features every developer should know and make sure to download the New Features in JPA 2.1 cheat sheet.

About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Repl​​​​​y

Your email address will not be published. Required fields are marked

This site uses Akismet to reduce spam. Learn how your comment data is processed.

    1. Hi Steve,

      thanks for the tip.
      Is there an option to use the MetadataBuilder with JPA compliant bootstrapping or do I have to use the Hibernate specific one?

      Regards,
      Thorben

      1. No, there is not. Unfortunately JPA bootstrapping is a “fire one shot and forget” kind of solution. So no point in which to interject calls. We (the Hibernate team) have proposed a multi-phase EMF bootstrap to the JPA EG multiple times (as it helps alleviate many chicken-egg problems especially in regards to EE container bootstrapping), but it has never gained traction. Something like that would be needed to obtain access to vendor-specific bootstrap components anyway.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}