Stored Procedures

The JPA 2.1 specification introduced support for calling stored procedures by using the JPA criteria query API. We Introduced the @Procedure annotation for declaring stored procedure metadata on a repository method.

The examples to follow use the following procedure:

Example 1. The definition of the plus1inout procedure in HSQL DB.
/;
DROP procedure IF EXISTS plus1inout
/;
CREATE procedure plus1inout (IN arg int, OUT res int)
BEGIN ATOMIC
 set res = arg + 1;
END
/;

Metadata for stored procedures can be configured by using the NamedStoredProcedureQuery annotation on an entity type.

Example 2. StoredProcedure metadata definitions on an entity.
@Entity
@NamedStoredProcedureQuery(name = "User.plus1", procedureName = "plus1inout", parameters = {
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = Integer.class),
  @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res", type = Integer.class) })
public class User {}

You can reference stored procedures from a repository method in multiple ways. The stored procedure to be called can either be defined directly by using the value or procedureName attribute of the @Procedure annotation or indirectly by using the name attribute. If no name is configured, the name of the repository method is used as a fallback.

The following example shows how to reference an explicitly mapped procedure:

Example 3. Referencing explicitly mapped procedure with name "plus1inout" in database.
@Procedure("plus1inout")
Integer explicitlyNamedPlus1inout(Integer arg);

The following example shows how to reference an implicitly mapped procedure by using a procedureName alias:

Example 4. Referencing implicitly mapped procedure with name "plus1inout" in database via procedureName alias.
@Procedure(procedureName = "plus1inout")
Integer plus1inout(Integer arg);

The following example shows how to reference an explicitly mapped named procedure in EntityManager:

Example 5. Referencing explicitly mapped named stored procedure "User.plus1IO" in EntityManager.
@Procedure(name = "User.plus1IO")
Integer entityAnnotatedCustomNamedProcedurePlus1IO(@Param("arg") Integer arg);

The following example shows how to reference an implicitly named stored procedure in EntityManager by using the method name:

Example 6. Referencing implicitly mapped named stored procedure "User.plus1" in EntityManager by using the method name.
@Procedure
Integer plus1(@Param("arg") Integer arg);