Hi Evan,
I think it's actually preferable to keep the "Named":
-- For uniformity with the other named query types
-- As a level of indirection (e.g., for potential reconfiguration
within the XML.)
-Linda
On 4/4/2011 9:30 PM, Evan Ireland wrote:
> Linda,
>
> Since stored procedures are always named, can we just call it
> StoredProcedureQuery
> (drop the "Named" prefix).
>
>> -----Original Message-----
>> From: Linda DeMichiel [mailto:linda.demichiel_at_oracle.com]
>> Sent: Friday, 1 April 2011 1:46 p.m.
>> To: jsr338-experts_at_jpa-spec.java.net
>> Subject: [jsr338-experts] stored procedures
>>
>> 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
>>
>>
>>
>