Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question you like me to answer, please leave a comment below.

Question:

How can I call a user-defined database function in the WHERE clause of my CriteriaQuery?



Solution:

Since JPA 2.1, you can use the function(String name, Class type, Expression … args) method of the CriteriaBuilder to call user-defined or database-specific functions.

You need to provide the name and the expected result type of the function as the first 2 parameters and you can provide one or more Expression which will be used as function arguments.

The following code snippet shows an example that calls the custom database function calculate.

The function returns a Double and I provide the price of the Book and a query parameter as function arguments.

You can use this approach in the WHERE clause to call all functions supported by your database.

You can also use the function function in the SELECT clause of your query.

But you then need to register the database function so that Hibernate knows its result type.

This makes the function function superfluous because you can use all registered functions directly in your query.

Source Code

Get the source code from GitHub



Learn More:

You can also use the function function in a JPQL query as I show you in this post: How to call custom database functions with JPA and Hibernate.

Hibernate Tips Book