Featured Image with Sidebar

How to call stored procedures in JPA – Part 2

By Thorben Janssen

Query

Up to Version 2.1, JPA was missing real support for stored procedures. The only available option was to use a native query.

This changed with JPA 2.1, when @NamedStoredProcedureQuery and StoredProcedureQuery were introduced as two of the new features. I wrote about @NamedStoredProcedureQuery and how to use it to define stored procedure calls in the previous post. This time, I will have a look at the StoredProcedureQuery and show you how to use it to call the same stored procedures via a Java API.

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.

Example application and test setup

But before we go into the details of StoredProcedureQueries, lets have a look at the example application and used libraries. The example application is the same as in the previous post about @NamedStoredProcedureQuery. It consists of a Book and a Review entity which you can see in the following diagram.

diagram

The StoredProcedureQuery interface is defined by the JPA 2.1 specification and you can therefore use it with any JPA 2.1 implementation, like Eclipse Link or Hibernate. I use Hibernate 4.3.7 for this tutorial.

My database is a PostgreSQL 9.4. The syntax and supported features of stored procedures are very different between the various database systems. Therefore you have to check your database documentation on how to implement stored procedures and which kind of parameters are supported.

Parameter modes

You can skip this section, if you’ve read the post about @NamedStoredProcedureQuery. I already explained the different parameter modes there and just added it here to bring everyone on the same level.
There are 4 different parameter modes that are supported by stored procedures:

  • IN: for input parameters,
  • OUT: for output parameters,
  • INOUT: for parameters which are used for input and output and
  • REF_CURSOR: for cursors on a result set .

As in the previous post, I will use IN and OUT parameters in the first and REF_CURSOR in the second example. INOUT parameters are used similarly as the IN and OUT parameters.

Stored procedures with IN and OUT parameters

The first stored procedure performs a simple calculation. Therefore it takes 2 IN parameters and returns the sum of them as an OUT parameter.

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

The EntityManager was extended by the StoredProcedureQuery createStoredProcedureQuery(String procedureName) method. The parameter procedureName defines the name of the stored procedure that will be called on the database.
After you have used this method to create a StoredProcedureQuery object, you need to define the parameters of this procedure. In this example, these are the two IN parameters x and y and the OUT parameter sum. This is all that has to be done to define the stored procedure call and basically the same as we did via the @NamedStoredProcedureQuery annotation in the previous article.

// define the stored procedure
StoredProcedureQuery query = this.em.createStoredProcedureQuery("calculate");
query.registerStoredProcedureParameter("x", Double.class, ParameterMode.IN);
query.registerStoredProcedureParameter("y", Double.class, ParameterMode.IN);
query.registerStoredProcedureParameter("sum", Double.class, ParameterMode.OUT);

Now you can use this definition of the stored procedure call to set your input parameters and finally execute it in the database. This is done in the same way for a StoredProcedureQuery defined by annotations as for one defined by the Java API. You use one of the setParameter methods to set the input parameters, call the execute() method to call the stored procedure in the database and get the output parameters via one of the getParameter methods.

// set input parameter
query.setParameter("x", 1.23d);
query.setParameter("y", 4.56d);

// call the stored procedure and get the result
query.execute();
Double sum = (Double) query.getOutputParameterValue("sum");

Stored procedures with REF_CURSOR

If you want to call a stored procedure that returns the resultset of a query, you can use a REF_CURSOR parameter for it. The following listing shows a stored procedure that uses a REF_CURSOR to return the result of query.

CREATE OR REPLACE FUNCTION get_reviews(bookid bigint)
  RETURNS refcursor AS
$BODY$
    DECLARE
      reviews refcursor;           -- Declare cursor variables                         
    BEGIN
      OPEN reviews FOR SELECT id, comment, rating, version, book_id FROM review WHERE book_id = bookId;
      RETURN reviews;
    END;
  $BODY$
  LANGUAGE plpgsql

The definition of the stored procedure call is similar to the previous one. You create a StoredProcedureQuery object via the createStoredProcedureQuery method and define the parameters. You probably already spotted the main difference in the query creation in this case. We have to define a mapping for the elements in the REF_CURSOR. In this case, I only provided the Book.class and Hibernate will determine the mapping automatically. If need be, you could also define multiple result classes or a result set mapping here.

The usage of the query is also a little bit different. This time, we need to call getResultList() to get the result of the query. This method implicitly calls the execute() method we used before to call the stored procedure in the database. If the stored procedure returns more than one REF_CURSOR, you have to call getResultList() again to access the next REF_CURSOR.

StoredProcedureQuery query = this.em.createStoredProcedureQuery("get_reviews", Book.class);
query.registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR);
query.execute();
List<Book> books = (List<Book>) query.getResultList();
for (Book b : books) {
    // do something useful
}

Summary

The programmatic creation of a StoredProcedureQuery is one of two options to define a stored procedure call in JPA. Therefore you have to call one of the createStoredProcedureQuery methods on the entity manager and then you have to define the input and output parameters of the procedure. If your stored procedure returns a REF_CURSOR, you have to also provide some mapping information when creating the query. This can be done by naming entities for which the EntityManager can determine the mapping automatically or by using a result set mapping.

Before you leave, download the free “New Features in JPA 2.1” cheat sheet, in which I describe this and other features introduced in JPA 2.1.

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.

  1. Hello! I am using spring boot and JPA 2.1, and I already use calls to procedures in Postgre but never needed to return cursor, in my first experience I got information here in your article and I have also seen that in some articles it is not called the execute method and without directly as below
    List results = query.getResultList ();

    This experience of mine is not having success once I get the error
    o.h.engine.jdbc.spi.SqlExceptionHelper: ERROR: cursor “” does not exist

    I am understanding that this configuration in the properties file should solve most unfortunately not
    spring.datasource.username = postgres
    spring.datasource.password = 12345
    spring.datasource.default-auto-commit = false
    spring.datasource.auto-commit = false
    spring.datasource.commit-on-return = false

    After two days without a solution, I went looking for answers in stackoverflow, Spring documentation and nothing else so far resulted in success, would you have any suggestions?

    Thanks Robson

    1. Hmmm … that’s difficult without seeing the code.
      Can you call the stored procedure from any other SQL client? Does it work?
      If it works, then it’s probably an issue with your stored procedure declaration in your persistence layer. Please check if the cursor is the first parameter in the stored procedure and in your query declaration.

      Regards,
      Thorben

  2. Hi Thorben Janssen,

    This is a great article and help me every time when I need to call a stored procedure in Java.

    Thanks to share you knowledge.

  3. It’s a very interesting info. I have a question: in the REF_CURSOR example, how do you set the value for the bookid parameter? Thanks.

    1. It’s the second parameter in the definition. So, you reference position 2 when setting the parameter:
      q.setParameter(2, b.getId());

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