In Relation To

The soon-to-be final JPA 2.1 specification adds standardized support for dealing with JDBC CallableStatements (stored procedure and function calls). Arun Gupta has a decent summary of the initial JPA 2.1 features, including Stored procedure support, at https://blogs.oracle.com/arungupta/entry/jpa_2_1_early_draft. Standardized here means both across providers as well as across database vendors. Pretty sweet. As much as I liked the idea of standarized support for handling callable statements, I was not overly thrilled with certain aspects of the proposed JPA StoredProcedureQuery API. My worries were mainly around how the outputs were accessed, especially when multiple results are expected. Let's first look at a simple example of a procedure returning a result:

StoredProcedreQuery query = entityManager.createStoredProcedureQuery( "top10SalesmenByQuarter", Employee.class );
query.registerStoredProcedureParameter( "quarter", String.class, ParameterMode.IN );
query.setParameter( "quarter", "Q1-2000" );
List top10Salesmen = query.getResultList();
...

Nothing too odious there.

However, imagine that we instead want to call a procedure that has a mix of update counts and results. This is where, in my humble opinion, the StoredProcedureQuery gets a bit dodgy. Largely it tries to follow the JDBC paradigm for accessing mixed returns. The argument for that approach of course is that it is familiar to developers familiar with the JDBC API. Lets take an example:

StoredProcedreQuery query = entityManager.createStoredProcedureQuery( "mixedReturns" );
...
while( 1==1 ) {
    boolean isResult = query.hasMoreResults();
    if ( isResult ) {
        handleResult( query.getResultList() );
    }
    else {
        int updateCount = query.getUpdateCount();

        // complete exit condition is ( ! query.hasMoreResults() && query.getUpdateCount != -1 )
        if ( updateCount == -1 ) {
            break;
        }

        handleUpdateCount( updateCount );
    }
}
...

To me, thats not very user friendly. However I was not able to get proposed changes to that API in. So I instead decided to develop an alternate API; A Hibernate-native API accessed through Session. The above query, using that API would look like:

org.hibernate.procedure.ProcedureCall call = entityManager.unwrap( Session.class ).createStoredProcedureCall( "mixedReturns" );
...
org.hibernate.procedure.ProcedureResult callResult = call.getResult();

while ( callResult.hasMoreReturns() ) {
    final org.hibernate.result.Return return = callResult.getNextReturn();
    if ( org.hibernate.result.ResultSetReturn.class.isInstance( return ) ) {
        handleResult( ( (org.hibernate.result.ResultSetReturn) return ).getResultList() );
    }
    else {
        handleUpdateCount( (org.hibernate.result.UpdateCountReturn) return ).getUpdateCount() );
    }
}
...

Both APIs support processing of multiple ResultSets too. If return classes or result-set-mappings are supplied, they apply to all of the processed ResultSets:

StoredProcedreQuery query = entityManager.createStoredProcedureQuery( "top_and_bottom_salesmen_by_quarter", Employee.class );
query.registerStoredProcedureParameter( "quarter", String.class, ParameterMode.IN );
query.registerStoredProcedureParameter( "top_salesmen", String.class, ParameterMode.REF_CURSOR );
query.registerStoredProcedureParameter( "bottom_salesmen", String.class, ParameterMode.REF_CURSOR );
query.setParameter( "quarter", "Q1-2000" );

// we will end up with 2 result lists, where each list contains elements of type Employee.  Pretty sweet!

boolean isResult = query.hasMoreResults();
while( isResult ) {
    handleResult( query.getResultList() );
}
...

ProcedureCall call = entityManager.unwrap( Session.class ).createStoredProcedureCall( "top_and_bottom_salesmen_by_quarter", Employee.class );
call.registerParameter( "quarter", String.class, ParameterMode.IN );
call.registerParameter( "top_salesmen", String.class, ParameterMode.REF_CURSOR );
call.registerParameter( "bottom_salesmen", String.class, ParameterMode.REF_CURSOR );
call.setParameter( "quarter", "Q1-2000" );

// we will end up with 2 result lists, where each list contains elements of type Employee.  Pretty sweet!

ProcedureResult callResult = call.getResult();
while ( callResult.hasMoreReturns() ) {
    final ResultSetReturn rtn = (ResultSetReturn) callResult.getNextReturn();
    handleResult( rtn.getResultList() );
}
...