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
>>
>>
>
--
@matthewadams12
mailto:matthew_at_matthewadams.me
skype:matthewadams12
yahoo:matthewadams
aol:matthewadams12
google-talk:matthewadams12_at_gmail.com
msn:matthew_at_matthewadams.me
http://matthewadams.me
http://www.linkedin.com/in/matthewadams