| |

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.


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.

19 Comments

  1. Save my work, thanks

  2. Avatar photo Kambala Naresh says:

    I miss clarity here what is the this.em means. Please provide valid input.

  3. Simple explanation, thanks!

  4. Avatar photo amirensit says:

    `getResultList()` is called twice in the second example. Does it really has a meaning to call the `getResultList()` before even setting the parameters ?.

    1. Avatar photo amirensit says:

      I didn’t see the names of stored procedure queries : “”getBooks” and “getReviews”. That’s right 🙂

  5. Avatar photo Vishal Munde says:

    Hi

    You certainly improved my knowledge. Thanks.
    I referred your blog to call stored procedure using JPA. But I wonder how I can pass the array or list to stored procedure using JPA. My stored procedure accepts int [] and char [] .
    I have searched through web but not found a single solution with JPA. I am sure you can spread some light on this .

    Thanks.

    1. Avatar photo Thorben Janssen says:

      Hi Vishal,

      That’s not supported by JPA’s @NamedStoredProcedureQuery or StoredProcedureQuery. You need to use a native SQL query instead.

      Regards,
      Thorben

  6. Avatar photo Utsav Bhetwal says:

    Does it require any entity class to call procedures from java ?

    1. Avatar photo Thorben Janssen says:

      Not necessarily. But why do you want to use JPA without entities?
      In that case, jOOQ or even plain JDBC is a better option.

  7. Hi

    I am wondering if it is possible to use JPA to kick off stored procedure that does a lot of back end work, but returns nothing (no records / no entity). I currently use SimpleJdbcCall for this purpose, but would like to switch to JPA if feasible.

    Thanks

    1. Avatar photo Thorben Janssen says:

      Hi Mark,

      sure, you can do that. You just need to define a @NamedStoredProcedureQuery without an OUT parameter.

      Regards,
      Thorben

  8. Avatar photo William Pinilla says:

    Thank you, It was useful your knowledge!

  9. Thanks for your post, it’s very useful. I have a question: How the parameters in the @NamedStoredProcedureQuery definition match with the parameters in the stored procedure definition? I see in the stored procedure definiton the bookid appears first and in the @NamedStoredProcedureQuery definition appears second.

    1. Avatar photo Thorben Janssen says:

      Parameters of type REF_CURSOR always need to be the first one in the @NamedStoredProcedureQuery definition. All following ones are then assigned in the order of their definition.

  10. Thanks for the detail description of it. Can you please let me know how to call system defined stored procedures say DBMS_LOB.APPEND procedure. Should I just follow the above assuming the name of the stored procedure to be DBMS_LOB.APPEND. Please suggest.

    1. Avatar photo Thorben Janssen says:

      Yes, you should be able to call system defined stored procedures in the same way.

  11. I am interested in last part of the tutorial where you are talking about Ref cursor . your function in DB is not working . are you sure that function is running in your system ?

    1. Avatar photo Thorben Janssen says:

      That stored procedure works fine on a PostgreSQL database. Which DBMS did you use?

Comments are closed.