We've gotten a quite a few requests for stored procedure support.
Unlike support for native queries, this does not entail query
definition, but rather specification of sufficient information to
enable the invocation of the stored procedure and extraction of its
results.
This is a strawman to get the discussion started. Given the lack of
portability across databases, I have tried to focus this strawman
around designing to it the JDBC APIs for CallableStatements. A draft
of a potential StoredProcedureQuery interface is attached below.
There are two cases:
Case (1) Stored procedure information is provided as metadata.
Unlike the case of a named native query, the metadata names a stored
procedure that exists in the database rather than providing a stored
procedure definition.
The metadata needs to specify parameter types and modes (IN, OUT, etc.)
as well as how result sets, if any, are to be mapped.
The following annotations attempt to capture this information.
@Target(value=TYPE)
@Retention(value=RUNTIME)
public @interface NamedStoredProcedureQuery{
String name; // name that is used to reference the named query; may not be the same as the name of the stored procedure
String procedureName; // name of the stored procedure in the database
StoredProcedureParameter[] parameters default {};
Class[] resultClass default {};
String[] resultSetMapping default {}; // name of SqlResultSetMapping
QueryHint[] hints default {};
}
@Target(value=TYPE)
@Retention(value=RUNTIME)
public @interface NamedStoredProcedureQueries {
NamedStoredProcedureQuery [] value;
}
I have assumed in the above that a stored procedure may return more
than one result set. As with native queries, the mapping of a result
set can be specified either in terms of a resultClass or as a
resultSetMapping. The above assumes that if there are multiple result
sets then they will be mapped using the same mechanism -- e.g., all
via a set of result class mappings or all via a set of result set
mappings. These mappings need to be specified in the order in which
the result sets will be returned by the stored procedure invocation.
If the stored procedure returns one or more result sets and no
resultClass or resultSetMapping element is specified, any result
set will be returned as a list of type Object[]. The combining
of different result set mapping strategies is undefined.
Information needs to be provided for all parameters. Parameters must
be specified in the order in which they occur in the parameter list of
the stored procedure. If parameter names are used, the parameter name
is used to bind the parameter value and to extract the output value
(if the parameter is an INOUT or OUT parameter). If parameter names
are not specified, it is assumed that positional parameters are used.
The mixture of named and positional parameters is undefined.
@Target(value={})
@Retention(value=RUNTIME)
public @interface StoredProcedureParameter {
String name default "";
ParameterMode mode default ParameterMode.IN;
Class type;
}
public enum ParameterMode {
IN,
INOUT,
OUT,
REF_CURSOR
}
REF_CURSOR is used to specify the case where a result set is returned
via an output parameter that is of a cursor variable type. This is
needed for databases that support returning ref cursors as output
parameters.
On the EntityManager interface:
/**
* Used when stored procedure metadata is specified using
* NamedStoredProcedureQuery (or equivalently in XML)
* The name argument refers to the name of the named query specified
* in the NamedStoredProcedureQuery annotation or XML metadata.
* @param name name assigned to the stored procedure in metadata
*/
public StoredProcedureQuery createNamedStoredProcedureQuery(String name);
Case (2) Stored procedure information is provided dynamically.
The stored procedure is invoked by its name in the database.
Parameter and result set information needs to be provided dynamically.
The following 3 methods are intended to be added to the EntityManager
interface. Again, if there is more than one result set, a single
approach needs to be chosen for the result set mapping.
/**
* Used when input and output mapping information is specified
* dynamically.
* The procedureName argument refers to the name of the stored
* procedure in the database.
* If the stored procedure returns one or more result sets,
* any result set will be returned as a list of type Object[].
*
*/
public StoredProcedureQuery createStoredProcedureQuery(
String procedureName);
/**
* Used when input and output mapping information is specified
* dynamically.
* The procedureName argument refers to the name of the stored
* procedure in the database.
* The resultClass arguments need to be specified in the order in
* which the result sets will be returned by the stored procedure
* invocation.
*/
public StoredProcedureQuery createStoredProcedureQuery(
String procedureName,
Class... resultClass);
/**
* Used when input and output mapping information is specified
* dynamically.
* The procedureName argument refers to the name of the stored
* procedure in the database.
* The resultSetMapping arguments need to be specified in the order
* in which the result sets will be returned by the stored procedure
* invocation.
*/
public StoredProcedureQuery createStoredProcedureQuery(
String procedureName,
String... resultSetMapping);
In case (2), since @NamedStoredProcedureQuery is not used to specify
metadata, all parameters need to be registered for the query using the
StoredProcedureQuery.registerStoredProcedureParameter method:
/*
* When using parameter names, all parameters must be registered in
* the order in which they occur in the parameter list of the
* stored procedure
*/
public StoredProcedureQuery registerStoredProcedureParameter(
String parameterName,
Class type,
ParameterMode mode);
public StoredProcedureQuery registerStoredProcedureParameter(
int position,
Class type,
ParameterMode mode);
StoredProcedureQuery execution:
The application needs to use the setParameter methods to set the values
of all IN and INOUT parameters.
Retrieving results:
The case where there is only a single result set (or a single result)
plus any results passed back via INOUT and OUT parameters can be
supported using the existing Query methods.
List getResultList()
Object getSingleResult()
The case where there is only an update count plus any results passed
back via INOUT and OUT parameters can be supported using the existing
Query method:
int executeUpdate()
The getOutputParameterValue methods are used to retrieve the values
passed back from the procedure through INOUT and OUT parameters.
Object getOutputParameterValue(int position)
Object getOutputParameterValue(String parameterName)
Both the simple case, where scalar results are passed back only via
INOUT and OUT parameters, and the most general case (multiple result
sets and/or update counts, possibly also in combination with output
parameter values) are supported using the execute() method. (This
method works along the same lines as what JDBC provides with the
execute method.)
boolean execute();
The execute method returns true if the first result is a result set,
and false if it is an update count or there are no results other
than through INOUT and OUT parameters, if any.
For portability, results that correspond to JDBC result sets and
update counts need to be processed before the values of any INOUT or
OUT parameters are extracted.
If execute returns true, the pending result set can be obtained
by calling getResultList. The hasMoreResults method can then
be used to test for further results:
/*
* returns true if the next result is a result set
*/
boolean hasMoreResults();
If execute or hasMoreResults returns false, getUpdateCount can be
called to obtain the pending result if it is an update count. The
getUpdateCount method will return either the update count (>= 0) or -1
if there is no update count (i.e., either the next result is a result
set or there is no next result).
/*
* returns the update count or -1 if there is no pending result or
* if the next result is not an update count.
*/
int getUpdateCount;
After results returned through getResultList and getUpdateCount have
been exhausted, results returned through INOUT and OUT parameters can
be retrieved.
In the simplest case, where results are returned only via INOUT and
OUT parameters, execute() can be followed immediately by calls to
getOutputParameterValue.
Please post your feedback, suggestions, improvements, and alternatives
to the above to the list.
thanks,
-Linda
package javax.persistence;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Set;
import java.util.Map;
/**
* Interface used to control stored procedure query execution.
*
* @see Query
* @see Parameter
*
* @since Java Persistence 2.1
*/
public interface StoredProcedureQuery extends Query {
/**
* Set a query property or hint. The hints elements may be used
* to specify query properties and hints. Properties defined by
* this specification must be observed by the provider.
* Vendor-specific hints that are not recognized by a provider
* must be silently ignored. Portable applications should not
* rely on the standard timeout hint. Depending on the database
* in use and the locking mechanisms used by the provider,
* this hint may or may not be observed.
* @param hintName name of the property or hint
* @param value value for the property or hint
* @return the same query instance
* @throws IllegalArgumentException if the second argument is not
* valid for the implementation
*/
StoredProcedureQuery setHint(String hintName, Object value);
/**
* Bind the value of a <code>Parameter</code> object.
* @param param parameter object
* @param value parameter value
* @return the same query instance
* @throws IllegalArgumentException if the parameter
* does not correspond to a parameter of the
* query
*/
<T> StoredProcedureQuery setParameter(Parameter<T> param, T value);
/**
* Bind an instance of <code>java.util.Calendar</code> to a <code>Parameter</code> object.
* @param param parameter object
* @param value parameter value
* @param temporalType temporal type
* @return the same query instance
* @throws IllegalArgumentException if the parameter does not
* correspond to a parameter of the query
*/
StoredProcedureQuery setParameter(Parameter<Calendar> param, Calendar value,
TemporalType temporalType);
/**
* Bind an instance of <code>java.util.Date</code> to a <code>Parameter</code> object.
* @param param parameter object
* @param value parameter value
* @param temporalType temporal type
* @return the same query instance
* @throws IllegalArgumentException if the parameter does not
* correspond to a parameter of the query
*/
StoredProcedureQuery setParameter(Parameter<Date> param, Date value,
TemporalType temporalType);
/**
* Bind an argument to a named parameter.
* @param name parameter name
* @param value parameter value
* @return the same query instance
* @throws IllegalArgumentException if the parameter name does
* not correspond to a parameter of the query or if
* the argument is of incorrect type
*/
StoredProcedureQuery setParameter(String name, Object value);
/**
* Bind an instance of <code>java.util.Calendar</code> to a named parameter.
* @param name parameter name
* @param value parameter value
* @param temporalType temporal type
* @return the same query instance
* @throws IllegalArgumentException if the parameter name does
* not correspond to a parameter of the query or if
* the value argument is of incorrect type
*/
StoredProcedureQuery setParameter(String name, Calendar value,
TemporalType temporalType);
/**
* Bind an instance of <code>java.util.Date</code> to a named parameter.
* @param name parameter name
* @param value parameter value
* @param temporalType temporal type
* @return the same query instance
* @throws IllegalArgumentException if the parameter name does
* not correspond to a parameter of the query or if
* the value argument is of incorrect type
*/
StoredProcedureQuery setParameter(String name, Date value,
TemporalType temporalType);
/**
* Bind an argument to a positional parameter.
* @param position position
* @param value parameter value
* @return the same query instance
* @throws IllegalArgumentException if position does not
* correspond to a positional parameter of the
* query or if the argument is of incorrect type
*/
StoredProcedureQuery setParameter(int position, Object value);
/**
* Bind an instance of <code>java.util.Calendar</code> to a positional
* parameter.
* @param position position
* @param value parameter value
* @param temporalType temporal type
* @return the same query instance
* @throws IllegalArgumentException if position does not
* correspond to a positional parameter of the query or
* if the value argument is of incorrect type
*/
StoredProcedureQuery setParameter(int position, Calendar value,
TemporalType temporalType);
/**
* Bind an instance of <code>java.util.Date</code> to a positional parameter.
* @param position position
* @param value parameter value
* @param temporalType temporal type
* @return the same query instance
* @throws IllegalArgumentException if position does not
* correspond to a positional parameter of the query or
* if the value argument is of incorrect type
*/
StoredProcedureQuery setParameter(int position, Date value,
TemporalType temporalType);
/**
* Set the flush mode type to be used for the query execution.
* The flush mode type applies to the query regardless of the
* flush mode type in use for the entity manager.
* @param flushMode flush mode
* @return the same query instance
*/
StoredProcedureQuery setFlushMode(FlushModeType flushMode);
// NEW METHODS START HERE:
/**
* All positional parameters must be registered.
* @param position parameter position
* @param type type of the parameter
* @param mode parameter mode
* @return the same query instance
*/
StoredProcedureQuery registerStoredProcedureParameter(
int position,
Class type,
ParameterMode mode);
/**
* When using parameter names, all parameters must be registered in
* the order in which they occur in the parameter list of the
* stored procedure.
* @param parameterName name of the parameter as registered or
* specified in metadata
* @param type type of the parameter
* @param mode parameter mode
* @return the same query instance
*/
StoredProcedureQuery registerStoredProcedureParameter(
String parameterName,
Class type,
ParameterMode mode);
/**
* Used to retrieve the values passed back from the procedure
* through INOUT and OUT parameters.
* For portability, all results corresponding to result sets
* and update counts must be retrieved before the values of
* output parameters.
* @param position parameter position
* @return the result that is passed back through the parameter
*/
Object getOutputParameterValue(int position);
/**
* Used to retrieve the values passed back from the procedure
* through INOUT and OUT parameters.
* For portability, all results corresponding to result sets
* and update counts must be retrieved before the values of
* output parameters.
* @param parameterName name of the parameter as registered or
* specified in metadata
* @return the result that is passed back through the parameter
*/
Object getOutputParameterValue(String parameterName);
/**
* Returns true if the first result corresponds to a result set,
* and false if it is an update count or if there are no results
* other than through INOUT and OUT parameters, if any.
* @return true if first result corresponds to result set
*/
boolean execute();
/**
* Returns true if the next result corresponds to a result set,
* and false if it is an update count or if there are no results
* other than through INOUT and OUT parameters, if any.
* @return true if next result corresponds to result set
*/
boolean hasMoreResults();
/**
* returns the update count or -1 if there is no pending result or
* if the next result is not an update count.
* @return update count or -1 if there is no pending result or if
* next result is not an update count
*/
int getUpdateCount();
}