[hibernate-dev] Stored procedure support

[Gunnar Morling]

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
>