How to call custom database functions with JPA and Hibernate

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

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.

CREATE OR REPLACE FUNCTION calculate(
    IN x double precision,
    IN y double precision,
    OUT sum double precision)
  RETURNS double precision AS
BEGIN
    sum = x + y;
END;

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.

Author a = em.createQuery("SELECT a FROM Author a WHERE a.id = function('calculate', 1, 2)", Author.class).getSingleResult();

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.

Object r = em.createQuery("SELECT function('calculate', a.id, 1) FROM Author a WHERE a.id = 1").getSingleResult();

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.

package org.thoughts.on.java.db;

import org.hibernate.dialect.PostgreSQL94Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;

public class MyPostgreSQL9Dialect extends PostgreSQL94Dialect {

	public MyPostgreSQL9Dialect() {
		super();
		registerFunction("calculate", new StandardSQLFunction("calculate"));
	}
}

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

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
	<persistence-unit name="my-persistence-unit">
		<description>Custom Database Functions</description>
		<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
		<exclude-unlisted-classes>false</exclude-unlisted-classes>

		<properties>
		  <!-- use custom dialect -->
			<property name="hibernate.dialect" value="org.thoughts.on.java.db.MyPostgreSQL9Dialect" />

      <!-- define database connection -->
			<property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
			<property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/HPT" />
			<property name="javax.persistence.jdbc.user" value="postgres" />
			<property name="javax.persistence.jdbc.password" value="postgres" />
		</properties>
	</persistence-unit>
</persistence>

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.