Featured Image with Sidebar

How to call stored procedures in JPA

By Thorben Janssen

Query

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.

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.

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.

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

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.

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.

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.

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.

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.

Tools

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. `getResultList()` is called twice in the second example. Does it really has a meaning to call the `getResultList()` before even setting the parameters ?.

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

  3. 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. Hi Mark,

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

      Regards,
      Thorben

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

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

  6. 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 ?

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