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