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() );
}
...
14 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
 
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
21. Jun 2014, 14:18 CET | Link

it is a promotion tool like simply no other. Email marketing still has no contenders in the internet marketing arena: if you’re not seeing the return-of-investment on your email subscribers, you must have been doing something wrong! Or, you may be using a wrong email marketing service…http://www.compareemailmarketingservices.com

22. Jun 2014, 13:11 CET | Link

Girlfriend Activation System Review (PDF Version) Reviewed by: Sterling Krosby Legal Notice/Disclaimer Of Warranty This review is for personal use only. You do NOT have permission to modify or sell it, but you may give it away for free to others who may benefit from it so long as none of the content, links, or other information contained within is “spun”, replaced, or modified in ANY manner.girlfriend activation system christian h. book reviews

23. Jun 2014, 07:15 CET | Link

This site is a fun and informative guide to the Carol Cline Potty Training In 3 Days PDF book and complete potty training system. Carol's simple and effective method gets your toddler excited about ditching his/her diapers and using the toilet just like mom and dad.www Rebel Mouse

25. Jun 2014, 06:53 CET | Link

Written for consumers by a consumer, this fan site includes a fun and informative Acne No More review, ebook FAQ, and PDF download guide that provides an insider's look at the program and answers frequently asked questions about Mike Walden's unique and highly popular acne treatment guide.www.rebelmouse.com

 
30. Aug 2014, 04:00 CET | Link
polo

Life will always be confused, no one can really predict, the future, Coach Outlet Black Friday, Live in the world, Ralph Lauren UK, and so we were growing up, Sac Longchamp Pairs, in the confusion, Michael Kors Outlet Online, life of all things, the same is spent also confused, Beats By Dre Outlet, confused, Michael Kors Outlet, confused world, Ralph Polo Outlet, in this world, I am a leaf, Oakley Sunglaases Factory, accompanied by wind, North Face Outlet Online, leisurely, Super Bags Market, but spent 12 years in trouble, Canada Goose Outlet, me than to know their own future, Burberry Outlet, but do not know the survival dream, because I was so small, Polo Outlet Store, wind, Michael Kors Outlet, quietly floating, Coach Factory, I stood under a tree, Gucci Shoes Outlet, watching the falling leaves, MCM Outlet, know that fall, North Clearace Outlet, at the very bottom, I perhaps these leaves, Marc Jacobs On Sale, to know fall, Monster Beats Outlet, but still could not find a direction, Nike Jordan Shoes, in the confusion.

16. Sep 2014, 09:18 CET | Link
michael kors outlet online sale,michael kors,kors outlet,michael kors outlet,michael kors handbags,michael kors outlet online,michael kors handbags clearance,michael kors purses,michaelkors.com,michael kors bags,michael kors shoes,michaelkors,cheap michael kors

, oakley vault,oakley sunglasses,oakleys,oakley sunglasses cheap,oakley.com,sunglasses outlet,cheap oakley,cheap oakley sunglasses,oakley outlet,cheap sunglasses,oakley prescription glasses,fake oakleys,oakley sunglasses outlet,oakley glasses,oakley store,fake oakley,oakley sale,cheap oakleys,discount oakley sunglasses, tory burch outlet,tory burch,tory burch handbags,tory burch shoes,tory burch sale,toryburch,tory burch sandals,toryburch.com,tory burch flip flops, ray ban sunglasses outlet,ray ban,ray ban sunglasses,rayban,ray bans,ray ban outlet,ray-ban,raybans,ray ban wayfarer,ray-ban sunglasses,raybans.com,rayban sunglasses,cheap ray ban, oakley sunglasses,oakley vault,oakleys,oakley sunglasses cheap,oakley.com,sunglasses outlet,cheap oakley,cheap oakley sunglasses,oakley outlet,cheap sunglasses,oakley prescription glasses,fake oakleys,oakley sunglasses outlet,oakley glasses,oakley store,fake oakley,oakley sale,cheap oakleys,discount oakley sunglasses, burberry outlet online,burberry,burberry outlet,burberry handbags,burberry factory outlet,burberry sale, polo ralph lauren outlet online,ralph lauren,polo ralph,polo ralph lauren,ralph lauren outlet,polo shirts,ralph lauren outlet online,polo ralph lauren outlet,ralphlauren.com,polo outlet,ralph lauren polo, gucci outlet,gucci handbags,gucci belts,gucci shoes,gucci,gucci belt,gucci sunglasses,gucci bags,cheap gucci, polo ralph lauren outlet,ralph lauren,polo ralph,polo ralph lauren,ralph lauren outlet,polo shirts,ralph lauren outlet online,polo ralph lauren outlet online,ralphlauren.com,polo outlet,ralph lauren polo, michael kors outlet,michael kors,kors outlet,michael kors handbags,michael kors outlet online,michael kors outlet online sale,michael kors handbags clearance,michael kors purses,michaelkors.com,michael kors bags,michael kors shoes,michaelkors,cheap michael kors, toms shoes,toms outlet,toms shoes outlet,toms.com,tom shoes, cheap oakley sunglasses,oakley sunglasses,oakley vault,oakleys,oakley sunglasses cheap,oakley.com,sunglasses outlet,cheap oakley,oakley outlet,cheap sunglasses,oakley prescription glasses,fake oakleys,oakley sunglasses outlet,oakley glasses,oakley store,fake oakley,oakley sale,cheap oakleys,discount oakley sunglasses, louboutin,christian louboutin,red bottom shoes,louboutin shoes,red bottoms,louboutin outlet,christian louboutin shoes,christian louboutin outlet,red bottom shoes for women,louboutins, michael kors outlet online,michael kors,kors outlet,michael kors outlet,michael kors handbags,michael kors outlet online sale,michael kors handbags clearance,michael kors purses,michaelkors.com,michael kors bags,michael kors shoes,michaelkors,cheap michael kors, michael kors outlet,michael kors outlet online,michael kors,kors outlet,michael kors handbags,michael kors outlet online sale,michael kors handbags clearance,michael kors purses,michaelkors.com,michael kors bags,michael kors shoes,michaelkors,cheap michael kors, ray ban sunglasses,ray ban,rayban,ray bans,ray ban outlet,ray-ban,raybans,ray ban wayfarer,ray-ban sunglasses,raybans.com,ray ban sunglasses outlet,rayban sunglasses,cheap ray ban, louboutin outlet,louboutin,christian louboutin,red bottom shoes,louboutin shoes,red bottoms,christian louboutin shoes,christian louboutin outlet,red bottom shoes for women,louboutins, michael kors handbags clearance,michael kors handbags,michael kors bags,michael kors purses

 
29. Sep 2014, 14:57 CET | Link
cxcvxzc | cxczc(AT)yahoo.com

It is our intention to drive the first Electric Van for approximately 90 days and discover all of the nuances that might come with an EV or if there are any design changes we would like to have included with future vans,” said Robichaud. “It is then our intention to start the transition at two per month. Logistically it takes time to swap out a technician to a new van and we do not want to overwhelm ourselves, although the sooner we switch the sooner we start saving.

 
29. Sep 2014, 14:59 CET | Link
cxcvxzc wrote on Sep 29, 2014 08:57:
It is our intention to drive the first Electric Van for approximately 90 days and discover all of the nuances that might come with an EV or if there are any design changes we would like to have included with future vans,” said Robichaud. “It is then our intention to start the transition at two per month. Logistically it takes time to swap out a technician to a new van and we do not want to overwhelm ourselves, although the sooner we switch the sooner we start saving. important site

Click HELP for text formatting instructions. Then edit this text and check the preview.

 
30. Sep 2014, 13:15 CET | Link
Chris Kresser

I have liked US Wellness Meats and Chris Kresser for some time now. Thank you for the chance to win this awesome prize! important site

02. Nov 2014, 02:09 CET | Link

I have found this awsome Steam Wallet Hack and i want to share it with you! Go here to download Steam Wallet hack for free right now! Télécharger Steam Wallet Hack Gratuit

 
19. Dec 2014, 04:28 CET | Link

In case you are involved in a car accident, you should immediately get medical assistance for all the people who might need it. lawyer