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.spring-doc.cn

The examples to follow use the following stored procedure:spring-doc.cn

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.spring-doc.cn

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 {}

Note that @NamedStoredProcedureQuery has two different names for the stored procedure. name is the name JPA uses. procedureName is the name the stored procedure has in the database.spring-doc.cn

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. This refers directly to the stored procedure in the database and ignores any configuration via @NamedStoredProcedureQuery.spring-doc.cn

Alternatively you may specify the @NamedStoredProcedureQuery.name attribute as the @Procedure.name attribute. If neither value, procedureName nor name is configured, the name of the repository method is used as the name attribute.spring-doc.cn

The following example shows how to reference an explicitly mapped procedure:spring-doc.cn

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

The following example is equivalent to the previous one but uses the procedureName alias:spring-doc.cn

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

The following is again equivalent to the previous two but using the method name instead of an explicit annotation attribute.spring-doc.cn

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

The following example shows how to reference a stored procedure by referencing the @NamedStoredProcedureQuery.name attribute.spring-doc.cn

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

If the stored procedure getting called has a single out parameter that parameter may be returned as the return value of the method. If there are multiple out parameters specified in a @NamedStoredProcedureQuery annotation those can be returned as a Map with the key being the parameter name given in the @NamedStoredProcedureQuery annotation.spring-doc.cn

Note that if the stored procedure returns a ResultSet then any OUT parameters are omitted as Java can only return a single method return value unless the method declares a Map return type.

The following example shows how to obtain multiple OUT parameters if the stored procedure has multiple OUT parameters and is registered as @NamedStoredProcedureQuery. @NamedStoredProcedureQuery registration is required to provide parameter metadata.spring-doc.cn

Example 7. StoredProcedure metadata definitions on an entity.
@Entity
@NamedStoredProcedureQuery(name = "User.multiple_out_parameters", procedureName = "multiple_out_parameters", parameters = {
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = Integer.class),
  @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "some_cursor", type = void.class),
  @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res", type = Integer.class) })
public class User {}
Example 8. Returning multiple OUT parameters
@Procedure(name = "User.multiple_out_parameters")
Map<String, Object> returnsMultipleOutParameters(@Param("arg") Integer arg);