Object Relational Mapping and JPA - Introducing Java EE 7 - page 69

qry.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
qry.setParameter(1, firstName);
qry.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
qry.setParameter(2,lastName);
qry.registerStoredProcedureParameter(3, String.class, ParameterMode.IN);
qry.setParameter(3, status);
try {
qry.execute();
String response = qry.getOutputParameterValue(1).toString();
System.out.println("stored procedure executed..." + response);
} catch (Exception ex) {
System.out.println(ex);
}
return result;
}

Using a native query works fine for executing stored procedures, but it can become more difficult since it must
return a cursor object or read an OUT parameter when attempting to return values from a stored procedure. It can
also become a maintenance nightmare if too many native SQL Strings are embedded throughout an application's
codebase. It is best to use JPQL or the Critiera API when working with JPA.
Let's dig a bit deeper into the @NamedStoredProcedureQuery annotation. Metadata or annotations can be used
to declare stored procedures and their associated parameters. The @ NamedStoredProcedureQuery can be specified
within an entity class to assign a name to a particular stored procedure. The @NamedStoredProcedureQuery accepts
the following parameters:

•

name : The String-based name that will be used to call the stored procedure

•

procedureName : The name of the underlying stored procedure

•

resultClass : The entity object that can be used to return the stored procedure result

•

resultSetMapping : SqlResultSetMapping if needed

•

returnsResultSet : boolean value indicating whether or not a resultSet is returned

•

parameters : zero or more @StoredProcedureParameter annotations, one for each of the
parameters accepted by the stored procedure.

In the example, the CREATE_EMP stored procedure is configured using an @NamedStoredProcedureQuery
annotation. Since there are three parameters that are accepted by the stored procedure, all three of them must be
passed to the stored procedure within the EJB stored procedure call. Execution of a named stored procedure is much
like the execution of a named query. A StoredProcedureQuery object is obtained by calling the EntityManager 's
createStoredProcedureQuery method, passing the name specified for the @NamedStoredProcedureQuery annotation.
Parameter values are set by calling both the Query object's registerStoredProcedureParameter method, and the
setParameter method against the StoredProcedureQuery , and passing the name of the parameter along with
the value to be substituted. The registerStoredProcedureParameter method accepts the parameter position as
the first argument, the parameter type as the second, and the type of ParameterMode as the third. The subsequent
call to setParameter registers the parameter value with the corresponding parameter position. Finally, to invoke the
stored procedure, call the StoredProcedureQuery object's execute method.
A dynamic stored procedure can also be constructed using the StoredProcedureQuery API, rather than using a
named stored procedure. To construct a StoredProcedureQuery object dynamically, pass the name of the underlying
database stored procedure to the EntityManager 's createStoredProcedureQuery method. Parameters for a dynamic