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:
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.
@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:
@Procedure("plus1inout")
Integer explicitlyNamedPlus1inout(Integer arg);
The following example shows how to reference an implicitly mapped procedure by using a procedureName
alias:
procedureName
alias.@Procedure(procedureName = "plus1inout")
Integer plus1inout(Integer arg);
The following example shows how to reference an explicitly mapped named procedure in EntityManager
:
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:
EntityManager
by using the method name.@Procedure
Integer plus1(@Param("arg") Integer arg);