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
>
>