A new feature is provided in  JPA 2.1 allowing a direct usage of stored procedures without using native query. This tutorial will show 3 different ways to call a database stored procedure with Hibernate.

Used in this tutorial :
  • Spring Boot (1.4.2.RELEASE)
    • spring-boot-starter-data-jpa
    • spring-boot-starter-test
  • Hibernate (5.0.11.Final)
  • Postgresql (9.4.1212.jre7)
  • Maven (3.3.9)

Context

Let’s consider a database that stores authors and their books. Below the entities model :

Author books database model

Author books database model

Consider the following stored procedure that adds a new book to an author and returns the identifier of the created book.

I’m using a PostgreSQL database in this tutorial.

In the next sections, we will use the new JPA stored procedure features to call the stored procedure using Hibernate as implementation.

Calling database stored procedure

github-mark
The tutorial source code is available on Github. Download the source

Using @NamedStoredProcedureQuery

JPA 2.1 provides the @NamedStoredProcedureQuery annotation to define your stored procedure. The basic usage is as follow :

Let’s take a moment to understand each attribute

  • @NamedStoredProcedureQuery: Used to specifiy and name a stored procedure and its parameters.
    • name:  The name to identify the stored procedure in the persistence unit.
    • procedureName: The name of  stored procedure in the database.
    • parameters: List of parameters ( @StoredProcedureParameter) of the database stored procedure. They must be specified in the order in which they occur in the stored procedure definition in the database.
  • @ StoredProcedureParameter: Specifies a parameter of a named stored procedure query.
    • name: The name of the parameter as defined by the stored procedure in the database. If the name is not specified, positional parameters will be used.
    • type : JDBC type of the parameter.
    • mode : Enumeration of type  ParameterMode. Values possible are :
      • IN: Stored procedure input parameter,
      • OUT: Stored procedure output parameter,
      • INOUT: Stored procedure input/output parameter
      • REF_CURSOR: Stored procedure reference cursor parameter..

In order to call the stored procedure addbook of our example, the NamedStoredProcedureQuery should be as follows :

How to call it ?

The javax.persistence.EntityManager interface exposes the   createNamedStoredProcedureQuery(String name) method to call the named stored procedure. This method returns a StoredProcedureQuery in which you must set all the parameters definied in the NamedStoredProcedureQuery.

Below the usage in our example :

Note that in this example, I used named parameters. Parameters can also be setted by positions as shown in the next section.

Creating dynamic stored procedure

The second way to call a database stored procedure is by creating dynamically a StoredProcedureQuery. The javax.persistence.EntityManager interface exposes the   createStoredProcedureQuery(String procedureName) method to create an instance of StoredProcedureQuery.

Similarly to the NamedStoredProcedureQuery, the parameters must be registered before performing the call. The parameters registration can be done by using one of the following method :

  • StoredProcedureQuery#registerStoredProcedureParameter(String parameterName, Class type,  ParameterMode mode) to register a named parameter
  • using StoredProcedureQuery#registerStoredProcedureParameter(int position, Class type, ParameterMode mode) to register a parameter by its position.

Example of use :

Contrary to the previous section, we will use positional parameters in the following example :

Using @Procedure of Spring data jpa

Spring data jpa, as usual, provides an easy way to call stored procedures by using the annotation @Procedure. It maps directly the repository method with the declared JPA 2.1 named stored procedure.

Example of use :

github-mark
The tutorial source code is available on Github. Download the source

References