Help

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() );
}
...
3 comments:
 
30. Dec 2013, 07:50 CET | Link
First of all, thanks for the cake of Christmas...!!!

I was looking for this since long time and was disappointed with hibernate for only the reason I am not able to read multiple result set from stored procedure.

From you post I got to know about the ProcedureCall pattern and I tried to use it, but unfortunately I failed.

I tried what you have give here but in some of the missing methods I end up with my own implementation.

Here is my code,

...
        ProcedureCall procedureCall = session.createStoredProcedureCall("myStoredProcedureWithTwoResultSets");
        ProcedureOutputs procedureOutputs = procedureCall.getOutputs();

        while ( procedureOutputs.goToNext() ) {
            Output output = procedureOutputs.getCurrent();
            if(output.isResultSet()) {
                System.out.println("found result set");
                List list = ((ResultSetOutputImpl) output).resultList();
                System.out.println("records in set:" + list.size());
            }
        }
...

First, I am not able to get ProcedureResult and ResultSetReturn.
Second, I tried to read output but I am not able to use ResultSetOutputImpl in my code due to AOP.

Here I need you help to understand the things how we can implement such thing to read multiple result sets.
You can correct me if I am wrong in my implementation.

Thanks,
Kanny
ReplyQuote
 
31. Dec 2013, 01:54 CET | Link

If I understand you correctly (I feel a little bit of a language barrier) the main problem you are running into is the fact that when you get back the ProcedureOutputs it is initially pointing at the first Output. This was something I had to change after initial design to fit in with what JPA required. Your above code is skipping the first ResultSetOutput

Getting access to the results would vary depending on whether you knew ahead of time how many sets of results to expect and whether to expect any counts mixed in. Assuming for example that the procedure is definitively known to return back 2 sets of results (with no counts), you could simply use:

        ProcedureCall procedureCall = session.createStoredProcedureCall("myStoredProcedureWithTwoResultSets");
        ProcedureOutputs procedureOutputs = procedureCall.getOutputs();

        List firstResults = ( (ResultSetOutput) procedureOutputs.getCurrent() ).resultList();
        procedureOutputs.goToNext();
        List secondResults = ( (ResultSetOutput) procedureOutputs.getCurrent() ).resultList();

Alternatively, assuming the procedure is known to return 1 or more (but not zero) sets of results, a do/while loop would be better:

        ProcedureCall procedureCall = session.createStoredProcedureCall("myStoredProcedureWithTwoResultSets");
        ProcedureOutputs procedureOutputs = procedureCall.getOutputs();

        do {
            Output output = procedureOutputs.getCurrent();
            if ( output.isResultSet() ) {
                List list = ( (ResultSetOutput) output ).getResultList();
                ....
            }
        } while ( procedureOutputs.goToNext() )

HTH

 
01. Jan 2014, 06:58 CET | Link
Thanks Steve for your response.

Debug time I got to know the thing that I am escaping first result set and I changed it to [do ... while ...] loop pattern as you explained. And it works for me.

Now one more thing that I am facing problem with. In case if my stored procedure returns same columns in all result sets then only I am able to read multiple result sets, other wise on next access to procedureOutputs.getCurrent() it throws exception like,

        Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name record_id is not valid.

(Here record_id is a field from first result set and second result set does not contain that field)

In code study, I felt that the implementation is still not complete (Please correct if I am wrong). If there is any plan for updates then I can wait or if you could advise me for my query.


-Kanny
Post Comment