Hmmmm......
But we already have NamedNativeQuery. So wouldn't that be a bit error-prone?
-Linda
On 4/7/2011 5:20 PM, Evan Ireland wrote:
> Linda,
>
> Could you call it StoredProcedureNamedQuery then?
>
> It's a kind of "named query" that happens to be a "stored procedure".
>
>> -----Original Message-----
>> From: Linda DeMichiel [mailto:linda.demichiel_at_oracle.com]
>> Sent: Friday, 8 April 2011 11:25 a.m.
>> To: jsr338-experts_at_jpa-spec.java.net
>> Subject: [jsr338-experts] Re: stored procedures
>>
>> 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
>>>>
>>>>
>>>>
>>
>