[hibernate-dev] Stored procedure support
Steve Ebersole
steve at hibernate.org
Fri Nov 16 16:18:11 EST 2012
I dislike static builders. If we were going to go that route, I'd
rather see contextual builders:
StoredProcedureCall call = session.createStoredProcedureCall("my_proc");
OutParameter<Long> p1Param =
call.parameterRegistrar().out(Long.class).named("p1");
However, this still assumes that these "specialized memento types" are
needed (OutParameter, versus InParameter, versus InOutParameter).
Really, what does that buy you? At the end of the day it really is
just the difference between:
RegisteredParameter<Long> p1 = ...;
call.bind( p1, 123 );
and:
BindableRegisteredParameter<Long> p1 = ...;
p1.bind( 123 );
However, I think it is arguable at best whether the second form is any
more readable or in any other way easier for the user. So not sure I
see a real benefit to splitting this out this way. Aka, still not
seeing a compelling argument for something other than just the 2
methods. Maybe if we want compile time safety for which parameters
can be passed to bind/extract? But is that really a win for the user?
That would mean a whole hierarchy to "parameter registrations":
interface ParameterRegistration<T> {
...
}
interface BindableParameterRegistration<T> extends ParameterRegistration<T> {
}
interface ExtractableParameterRegistration<T> extends ParameterRegistration<T> {
}
class InParameterRegistration<T> implements BindableParameterRegistration<T> {
...
}
class InOutParameterRegistration<T>
implements BindableParameterRegistration<T>,
ExtractableParameterRegistration<T> {
...
}
class OutParameterRegistration<T> implements
ExtractableParameterRegistration<T> {
}
Then we have inherent "double type safety" here because we can define
the bind/extract contracts like:
<T> void bind(BindableParameterRegistration<T> param, T value);
and
<T> T extractParameterValue(ExtractableParameterRegistration<T> param);
Is that really useful?
On Fri, Nov 16, 2012 at 2:47 PM, Gunnar Morling <gunnar at hibernate.org> wrote:
> 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