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