3 ways to call a stored procedure with Hibernate (JPA 2.1)

Radouane ROUFID

8 years ago

Hibernate

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)

Table of contents

1 Context
2 Calling database stored procedure

Context

Let’s consider a database that stores authors and their books. Below the entities 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.

CREATE OR REPLACE FUNCTION addbook(

bookname character varying,

bookreleasedate timestamp without time zone,

authorfirstname character varying,

authorlastname character varying)

RETURNS integer AS $BODY$

DECLARE

authorId int;

bookId int;

BEGIN

-- select if exist

SELECT AUTHOR_ID INTO authorId FROM author a WHERE a.FIRSTNAME=authorFirstname AND a.LASTNAME=authorLastname;

IF NOT FOUND THEN

-- insert AUTHOR

SELECT nextval('idauhtor_seq') INTO authorId;

INSERT INTO author (AUTHOR_ID, FIRSTNAME, LASTNAME) values (authorId, authorFirstname, authorLastname);

END IF;

-- insert BOOK

SELECT nextval('idbook_seq') INTO bookId;

INSERT INTO book (BOOK_ID, NAME, RELEASE_DATE, AUTHOR_ID) VALUES(bookId, bookName, bookReleaseDate, authorId);

return bookId;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

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

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 :

@NamedStoredProcedureQuery(

name = "", // name of stored procedure in the persistence unit

procedureName = "", //name of stored procedure in the database

parameters = //Parameters of the stored procedure

{

@StoredProcedureParameter(// A parameter,

name = "", //Name of the parameter

mode = ParameterMode.IN, // Mode of the parameter

type = String.class) // JDBC Type.

}

)

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 :

@Entity

@Table(name = "BOOK")

@NamedStoredProcedureQuery(

name = "addBook_sp",

procedureName = "addBook",

parameters = {

@StoredProcedureParameter(name = "bookName", mode = ParameterMode.IN, type = String.class),

@StoredProcedureParameter(name = "bookReleaseDate", mode = ParameterMode.IN, type = Date.class),

@StoredProcedureParameter(name = "authorFirstname", mode = ParameterMode.IN, type = String.class),

@StoredProcedureParameter(name = "authorLastname", mode = ParameterMode.IN, type = String.class),

}

)

public class Book implements Serializable {

…

}

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 :

String bookName = "Design Patterns: Elements of Reusable Object-Oriented Software";

String authorFirstname = "Gang";

String authorLastname = "of four";

// Getting the named stored procedure from the persistence unit and settting the parameters values.

StoredProcedureQuery addBookNamedStoredProcedure = entityManager.createNamedStoredProcedureQuery("addBook_sp");

addBookNamedStoredProcedure.setParameter("bookName", bookName);

addBookNamedStoredProcedure.setParameter("bookReleaseDate", new Date(), TemporalType.DATE);

addBookNamedStoredProcedure.setParameter("authorFirstname", authorFirstname);

addBookNamedStoredProcedure.setParameter("authorLastname", authorLastname);

// Stored procedure call

Integer createdBookId = (Integer) addBookNamedStoredProcedure.getSingleResult();

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 :

String bookName = "Java Persistence with Hibernate";

String authorFirstname = "Christian";

String authorLastname = "Bauer";

// Dynamic stored procedure definition.

StoredProcedureQuery addBookStoredProcedure = entityManager.createStoredProcedureQuery("addBook_sp");

addBookStoredProcedure.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);

addBookStoredProcedure.registerStoredProcedureParameter(2, Date.class, ParameterMode.IN);

addBookStoredProcedure.registerStoredProcedureParameter(3, String.class, ParameterMode.IN);

addBookStoredProcedure.registerStoredProcedureParameter(4, String.class, ParameterMode.IN);

// Setting stored procedure parameters.

addBookStoredProcedure.setParameter(1, bookName);

addBookStoredProcedure.setParameter(2, new Date(), TemporalType.DATE);

addBookStoredProcedure.setParameter(3, authorFirstname);

addBookStoredProcedure.setParameter(4, authorLastname);

// Stored procedure call

Integer createdBookId = (Integer) addBookStoredProcedure.getSingleResult();

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 :

package com.roufid.tutoriel.dao;

import java.util.Date;

import org.springframework.data.jpa.repository.query.Procedure;

import org.springframework.data.repository.CrudRepository;

import com.roufid.tutoriel.entity.Book;

public interface BookDao extends CrudRepository<Book, Integer> {

/**

* Will call the named stored procedure corresponding to :

* CREATE OR REPLACE FUNCTION addbook(

* bookname character varying,

* bookreleasedate timestamp without time zone,

* authorfirstname character varying,

* authorlastname character varying)

* RETURNS integer

@Procedure

public Integer addBook(String bookName, Date bookReleaseDate, String authorFirstname, String authorLastname);

}