How to call a stored procedure from Hibernate / JPA?

Let’s learn how to invoke a Stored Procedure from Hibernate and JPA applications.

Supposing you have the following Oracle Stored procedure:

CREATE FUNCTION agenda RETURN SYS_REFCURSOR
AS 
    my_cursor SYS_REFCURSOR;
BEGIN
    OPEN my_cursor FOR
        SELECT id,person_name,person_surname,person_address
        FROM Person;
    RETURN  my_cursor;
 END;

Invoking it from Hibernate requires mapping the Stored Procedure in the Person class.

<sql-query name="SP_agenda" callable="true">
    <return alias="ev" class="Person">
        <return-property name="id" column="id"/>
        <return-property name="name" column="person_name"/>
        <return-property name="surname" column="person_surname"/>
        <return-property name="address" column="person_address"/>
    </return>
    { ? = call agenda() }
</sql-query>

Then, you can execute the procedure just like a normal Query:

Query query = session.getNamedQuery("SP_agenda");
List results = query.list();

Calling a Stored procedure with JPA

If you are running JPA applications, there are multiple ways to run Stored Procedure through the EntityManager interface. Let’s start from the simplest way which is available in all JPA versions.

Run a Stored Procedure as Native Query

In this approach, you wrap the Stored Procedure call in a Native Query:

Query query = em.createNativeQuery("{call agenda()}",
                                   Person.class)); 

List<Person> result = query.getResultList();

If your Stored Procedure has IN parameters, you can set them as follows:

Query query = em.createNativeQuery("{call agenda(?,?)}",
                                   Person.class)           
                                   .setParameter(1, name)
                                   .setParameter(2, surname);

List<Person> result = query.getResultList();

Run a Stored Procedure using StoredProcedureQuery

If you are running JPA 2.1 or newer, another approach consists in using the javax.persistence.StoredProcedureQuery interface to wrap the Stored Procedure Execution.

StoredProcedureQuery query = em.createStoredProcedureQuery("agenda");
query.execute():

If your stored procedure as IN/OUT parameters, before running the execute method you can register them as follows:

query.registerStoredProcedureParameter("name", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("surname", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("money", Double.class, ParameterMode.OUT);

Finally, you can declarative define your Stored Procedure via annotations using the @NamedStoredProcedureQuery annotation which is available as well since JPA 2.1:

@NamedStoredProcedureQuery(
    name = "agenda", 
    procedureName = "agenda", 
    parameters = { 
        @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "name"), 
        @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "surname"), 
        @StoredProcedureParameter(mode = ParameterMode.OUT, type = Double.class, name = "money")
    }
)