These methods intentionally do not use generics for ease of use. A
method using the java.persistence.Parameter type should be added for
users who wish to use "type safe" calls.
--Gordon
Matthew Adams wrote:
> I haven't fully thought this through, but is there a way to provide
> more type safety to the methods
>
> Object getOutputParameterValue(int position)
> Object getOutputParameterValue(String parameterName)
>
> on StoredProcedureQuery? Any other methods where generics could be used?
>
> On Thu, Apr 7, 2011 at 6:31 PM, Linda DeMichiel
> <linda.demichiel_at_oracle.com> wrote:
>
>> Hi all,
>>
>> I've received only one comment on this proposal. Please let
>> me know if there is any other feedback; whether you would like
>> to suggest any specific changes; whether I should proceed with
>> this; etc., etc.
>>
>> thanks,
>>
>> Linda
>>
>>
>> On 3/31/2011 5:46 PM, Linda DeMichiel wrote:
>>
>>> 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
>>>
>>>
>>>
>
>
>
>