[hibernate-dev] Stored procedure support

[Gunnar Morling]

IMO registerInParameter(String,Class) et al. read better than
registerParameter(int,Class,ParameterMode), while the latter might be a bit
simpler to use when writing the code. As code is more read than written,
optimizing the read use case might be the better option. But I agree that
six methods compared to two is quite a difference.

Using different types for the different kinds of parameters would also
avoid the possibility of reading values from an IN parameter.



2012/11/16 Steve Ebersole <steve at hibernate.org>

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