Hibernate Tips: How to call a PostgreSQL function

By Thorben Janssen

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 for a future Hibernate Tip, please leave a comment below.

Question:

How do you call a PostgreSQL function with Hibernate?

Solution:

As I explained in my post about using a PostgreSQL database with Hibernate, there are multiple options to call a PostgreSQL function with Hibernate.

If it’s one of the SQL’s or PostgreSQL’s standard functions that’s supported by Hibernate’s PostgreSQL dialect, you can simply call it in a JPQL query.

List<Object[]> o = em.createQuery("SELECT a, count(b) FROM Author a JOIN a.books b ").getResultList();

It get’s more complicated, if it’s a custom function or any other function that’s not supported by the Hibernate’s PostgreSQL dialect. You then need to either create your own Hibernate dialect, or you use JPA’s function function. It was introduced in JPA 2.1 and gives you a generic way to define a function call. You just need to provide the name of the database function as the first parameter, followed by the parameters you want to provide to your function call.

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

And you can also call all PostgreSQL functions as a stored procedure. You, therefore, create a @NamedStoredProcedureQuery and define the name you will use to reference this query, the name of the stored procedure and all parameters.

@NamedStoredProcedureQuery(
    name = "getReviews", 
    procedureName = "get_reviews", 
    resultClasses = Review.class, 
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class), 
        @StoredProcedureParameter(mode = ParameterMode.IN, type = Long.class)
    }
)

You can then instantiate the @NamedStoredProcedureQuery by providing its name to the createNamedStoredProcedureQuery method of your EntityManager. This method returns a StoredProcedureQuery interface which you can then use to set the values of the input parameters and to execute the query.

StoredProcedureQuery q = this.em.createNamedStoredProcedureQuery("getReviews");
q.setParameter(2, b.getId());
List reviews = q.getResultList();

Learn more:

You can read more about Hibernate’s support for PostgreSQL-specific features and all other things you should keep in mind when you’re using Hibernate with this database, in Hibernate with PostgreSQL – 6 things you need to know.

And I explained JPA’s and Hibernate’s support for stored procedure calls in much more details in:

 

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!



Tags

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 Reply

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"}