[hibernate-dev] Stored procedure support
Gunnar Morling
gunnar at hibernate.org
Sat Nov 17 05:44:32 EST 2012
Hi,
I see two advantages of the approach using strongly typed parameter objects:
* I as a user don't need to do a cast when retrieving the value from an
output parameter. This avoids having to specify the type twice (once when
registering the parameter, once when retrieving the value) and it avoids
the possibility of accidentally casting to a wrong type when retrieving the
value.
* I as a user see only the appropriate methods at a given point, so I can
do only the right things with the different types of parameters (binding to
in/inout and extracting from inout/out parameters), e.g. I can't
accidentally try to retrieve a value from an input parameter.
Personally I prefer it when an API offers this kind of safety against being
used in the wrong way, but that's surely also a matter of taste.
Implementation-wise the approach with two methods would surely be simpler
but I'd prefer the advanced type-safety for the user over this. Would be
interesting to know what others think :)
Slightly related, I noticed that the classes around stored procedure
support are directly under org.hibernate.*. Note that I don't know anything
about the general packaging scheme in ORM, but I was wondering whether a
dedicated package for this might make sense
(org.hibernate.storedprocedure.*). This would help in structuring things
and might make the "StoredProcedure" prefix for (some of the) class
names unnecessary.
--Gunnar
2012/11/16 Steve Ebersole <steve at hibernate.org>
> 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