| |

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.


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();
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.

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.

3 Comments

  1. Nice write-up Thorben. While I certainly agree that writing a custom Dialect is the best option, I did just want to point out an alternative for defining an additional function: namely, org.hibernate.boot.MetadataBuilder#applySqlFunction

    Also, longer term (7.0 timeframe – https://github.com/hibernate/hibernate-orm/wiki/Roadmap) I hope to allow some facility to specify custom functions via xml config.

    1. Avatar photo Thorben Janssen says:

      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.

Comments are closed.