How to call stored procedures in JPA – Part 2

---

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.

Join the Persistence Hub!

---

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.

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.