[hibernate-dev] Stored procedure support

[Steve Ebersole]

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