[hibernate-dev] Stored procedure support

[Steve Ebersole]

I can definitely see a benefit to your registerOutParameter over
registerParameter if we wanted to return specific types.  But aside
from that, I am not so sure that having to know 3 different sets of
methods to register parameters:

registerInParameter(int,Class)
registerInParameter(String,Class)
registerInOutParameter(int,Class)
registerInOutParameter(String,Class)
registerOutParameter(int,Class)
registerOutParameter(String,Class)

is better than one set:

registerParameter(int,Class,ParameterMode)
registerParameter(String,Class,ParameterMode)

Do you really think that is easier for a user?

On Fri, Nov 16, 2012 at 12:52 PM, Gunnar Morling <gunnar at hibernate.org> wrote:
> Hi,
>
> FWIW, I'd prefer option #2 due to its type-safety. In #3 it's as you say
> challenging to control when extract() can be invoked.
>
> Maybe you could offer dedicated methods for the different parameter modes,
> making client code a bit shorter:
>
> RegisteredParameter<Long> p1Param =
> call.registerOutParameter( "p1", Long.class );
>
> I tend towards that pattern if the number of possible enum values is small,
> as it should be the case here.
>
> --Gunnar
>
>
>
> 2012/11/16 Steve Ebersole <steve at hibernate.org>
>
>> I thought I had written about this before to the list, but maybe not.
>> Anyway, I added much enhanced support for calling database functions and
>> procedures to master.  But this is just my initial swag and as far as I
>> know my eyes are the only ones that have seen it.  So I wanted to get
>> some feedback.  Feel free to give feedback on any/all aspects of that
>> API, but there was one aspect in particular I was really wanting
>> feedback: parameters.  The concept of parameters, much like in queries,
>> is split into 2 parts: declaration and usage.
>>
>> The initial impetus for this was the JPA 2.1 feature for supporting
>> procedure calls.  But I went a little different direction in our
>> "native" support for this, the main difference being modeling the
>> outputs as a separate thing from the call itself.  I really like our API
>> there better.
>>
>> The declaration of the call is modeled as
>> org.hibernate.StoredProcedureCall (although I am thinking of moving away
>> from "StoredProcedure" as the name base here since functions are
>> supported as well; better name suggestions welcome).  The outputs of the
>> call execution is org.hibernate.StoredProcedureOutputs.
>>
>> To create a StoredProcedureCall, one simply calls one of the overloaded
>> Session.createStoredProcedureCall methods passing in either (a) the
>> func/proc name, (b) the func/proc name and any entity class(es) to map
>> the results back to, (c) the func/proc name and any result set mapping
>> name(s) to apply to the results.
>>
>>  From there, parameters are declared/registered through the overloaded
>> StoredProcedureCall#registerStoredProcedureParameter methods.  Again, in
>> retrospect not liking that name; should be declareParameter or
>> registerParameter imo.  Anyway, parameters can be treated as either
>> positional or named.  Named here has a little bit different meaning
>> though, intending to name the arguments in the procedure/function
>> definition.  This is a feature defined by JDBC 3, although as I
>> understand it not all drivers support it (aka, it can lead to
>> SQLFeatureNotSupportedException).  We can even know this a priori via
>> DatabaseMetaData.html#supportsNamedParameters() to give better (and
>> earlier!) exceptions.
>>
>> Anyway, currently registerStoredProcedureParameter returns back
>> StoredProcedureCall for method chaining.  We'll come back to that in a
>> second...
>>
>> After parameters are registered, the values for IN and INOUT style
>> parameters must be set/bound.  Currently this is untyped because of the
>> fact that registration does not return any "memento" with the typing
>> information (the Java type is passed to the register method).  After
>> execution, the values from all INOUT and OUT parameters can be
>> extracted, but again those extractions are untyped for the same reason.
>>   Which leads me to question whether we want to consider handling
>> parameter values (whether in or out) in a typed manner is important.  As
>> an example, currently to extract an OUT parameter value you'd have:
>>
>> StoredProcedureCall call = session.createStoredProcedureCall("my_proc");
>> call.registerStoredProcedureParameter("p1",Long.class,ParameterMode.OUT);
>> //maybe some other stuff...
>> StoredProcedureOutputs outputs = call.getOutputs();
>> Long p1 = (Long) outputs.getOutputParameterValue("p1");
>>
>> The alternative would be something like defining a typed
>> RegisteredParameter contract:
>>
>> interface RegisteredParameter<T> {
>>      public Class<T> getParameterType();
>>      public ParameterMode getMode();
>> }
>>
>> and then:
>>
>> StoredProcedureCall call = session.createStoredProcedureCall("my_proc");
>> RegisteredParameter<Long> p1Param = call.registerParameter(
>>      "p1",
>>      Long.class,
>>      ParameterMode.OUT
>> );
>> //maybe some other stuff...
>> StoredProcedureOutputs outputs = call.getOutputs();
>> Long p1 = outputs.getOutputParameterValue( p1Param );
>>
>>
>> Or maybe even:
>>
>> interface RegisteredParameter<T> {
>>      public Class<T> getParameterType();
>>      public ParameterMode getMode();
>>
>>      public void bind(T value);
>>      public T extract();
>> }
>>
>> StoredProcedureCall call = session.createStoredProcedureCall("my_proc");
>> RegisteredParameter<Long> p1Param = call.registerParameter(
>>      "p1",
>>      Long.class,
>>      ParameterMode.OUT
>> );
>> //maybe some other stuff...
>> StoredProcedureOutputs outputs = call.getOutputs();
>> Long p1 = p1Param.extract();
>>
>> The problem with this last one is managing when that 'extract' can be
>> called...
>>
>>
>> Anyway, thoughts?
>>
>> --
>> steve at hibernate.org
>> http://hibernate.org
>> _______________________________________________
>> hibernate-dev mailing list
>> hibernate-dev at lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev