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