How to call stored procedures in JPA

---

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!

---

Using stored procedures to implement parts of the application logic in the database is a widely used approach in huge, data-heavy applications. Nevertheless, there was no good support for them before JPA 2.1. You had to use a native query, to call the stored procedure in the database.

Since the JPA 2.1 release, JPA supports two different ways to call stored procedures, the dynamic StoredProcedureQuery, and the declarative @NamedStoredProcedureQuery. In this article, I will show you how to define a @NamedStoredProcedureQuery and how to use it to call a stored procedure in the database. And next week, I will tell you more about the StoredProcedureQuery.

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

The example application for this post is small and simple. It consists of a Book and a Review entity which you can see in the following diagram.

The JPA 2.1 specification defines the @NamedStoredProcedureQuery, 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 it supports.

Parameter modes

Before I show you how to call your first stored procedure with JPA, I have to tell you something about the different parameter modes. You can use 4 different modes of parameters to implement a stored procedure:

• 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.

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

Let’s start with a simple stored procedure which takes 2 IN parameters, sums them up and returns the result 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 definition of the @NamedStoredProcedureQuery for this stored procedure is straight forward. You just need to define the name of this query, the name of the stored procedure in the database and a list of 3 parameters that match the parameters of the stored procedure. The name of the query is required by the EntityManager to create the @NamedStoredProcedureQuery. So better choose something that’s easy to understand and remember.

The parameters are defined via @StoredProcedureParameter annotations which take a name, a mode and a type. The name is the name of the parameter in the stored procedure and used later to set and get the specific value. I will show you an example for position based parameter mapping in the REF_CURSOR examples. The mode attribute takes a ParameterMode enum with one of the previously described 4 parameter modes, and the type attribute defines the type of the parameter.

@NamedStoredProcedureQuery(
	name = "calculate", 
	procedureName = "calculate", 
	parameters = { 
		@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "x"), 
		@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "y"), 
		@StoredProcedureParameter(mode = ParameterMode.OUT, type = Double.class, name = "sum")
	}
)

That’s all we need to do to define the @NamedStoredProcedureQuery. So lets see how we can call it.

StoredProcedureQuery query = this.em.createNamedStoredProcedureQuery("calculate");
query.setParameter("x", 1.23d);
query.setParameter("y", 4.56d);
query.execute();
Double sum = (Double) query.getOutputParameterValue("sum");

As you can see, you get a StoredProcedureQuery for the defined @NamedStoredProcedureQuery by calling the createNamedStoredProcedureQuery(String name) method of the EntityManager. The StoredProcedureQuery provides the required methods to set values of the IN parameters, execute the stored procedure and get the OUT parameter.

Stored procedures with REF_CURSOR

Simple input and output parameters are often not enough, and the stored procedure returns the result of a query. This can be done with an REF_CURSOR parameter as it is done in the following example.

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

As you can see in the following code snippet, the definition of the @NamedStoredProcedureQuery is similar to the previous example. The main differences are the different parameter mode and the definition of the result class returned by this stored procedure call. And I use the positional parameter mapping instead of the name based one we used in the previous example.

@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)
	}
)

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.

List<Book> books = (List<Book>) this.em.createNamedStoredProcedureQuery("getBooks").getResultList();
for (Book b : books) {
	StoredProcedureQuery q = this.em.createNamedStoredProcedureQuery("getReviews");
	q.setParameter(2, b.getId());
	List<Review> reviews = q.getResultList();
	for (Review r : reviews) {
		// do something
	}
}

Summary

JPA 2.1 introduced @NamedStoredProcedureQuery which can be used to declaratively define the stored procedure call. The stored procedure can use parameters of 4 different modes. IN, OUT, and INOUT can be used to define simple input and output parameters. The REF_CURSOR type can be used to return the result of a query.

After we have defined the @NamedStoredProcedureQuery, we can use the createNamedStoredProcedureQuery(String name) method of the EntityManager to create a StoredProcedureQuery object. This provides the required methods to set the input parameter, call the stored procedure and get the result.

This and 11 other features introduced in JPA 2.1 are also described in my free New Features in JPA 2.1  cheat sheet.