Hibernate Tips: How to call a user-defined function in a CriteriaQuery

By Thorben Janssen

Criteria API, Query

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


Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching and statically and dynamically defined queries.

Get it now as a paperback, ebook or PDF.

Tags

Criteria API, Query


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.

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