[hibernate-dev] Stored procedure support
Gunnar Morling
gunnar at hibernate.org
Fri Nov 16 15:47:51 EST 2012
Right, that's what I had in mind.
Another option would be a more builder-like approach:
StoredProcedureCall call = session.createStoredProcedureCall("my_proc");
OutParameter<Long> p1Param =
RegisteredParameter.outParameter(Long.class).named("p1Param");
InParameter<String> p2Param =
RegisteredParameter.inParameter(String.class).atPosition(1);
call.registerParameters(p1Param, p2Param);
This reduces the number of methods offered at one place (first
out/in/inout, then named/atPosition) and thus both reads/writes rather well
IMO.
Just a thought, haven't really tried this out :)
2012/11/16 Steve Ebersole <steve at hibernate.org>
> Again I am not so sure that registerInParameter is any more readable
> than registerParameter passed ParameterMode.IN. Registering a
> parameter is registering a parameter is registering a parameter to
> para-quote Ms Stein. To me the one thing that would make sense to
> split them out that way is what I think you are thinking as well in
> your last bit. That is, if we were to do:
>
> interface RegisteredParameter<T> {
> public Class<T> getJavaType();
> public ParameterMode getMode();
> }
>
> interface BindableRegisteredParameter<T> extends RegisteredParameter<T> {
> public void bind(T value);
> }
>
> Now the "split" methods make more sense:
> public <T> BindableRegisteredParameter<T> registerInParameter(int
> position, Class<T> type);
> public <T> BindableRegisteredParameter<T> registerInParameter(String
> name, Class<T> type);
> public <T> BindableRegisteredParameter<T> registerInOutParameter(int
> position, Class<T> type);
> public <T> BindableRegisteredParameter<T>
> registerInOutParameter(String name, Class<T> type);
> public <T> RegisteredParameter<T> registerOutParameter(int position,
> Class<T> type);
> public <T> RegisteredParameter<T> registerOutParameter(String name,
> Class<T> type);
>
>
> On Fri, Nov 16, 2012 at 2:28 PM, Gunnar Morling <gunnar at hibernate.org>
> wrote:
> > 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
> >
> >
>
More information about the hibernate-dev
mailing list