9:34 a.m.
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(a)hibernate.org
http://hibernate.org