• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Calling Stored Procedure thru JPA

 
Ranch Hand
Posts: 584
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi guys,

I found very exciting the new Java Persistence API which heavily uses annotations.

However, all samples I found are aimed to persist a single table or related tables.

So I'm wondering ...

For those applications which heavily use stored procedures, is there some way to use JPA ?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Edisandro,
It isn't support in any database specific way. Some databases let you call a stored proc like a prepared statement if it doesn't have any out parameters. If this works with your database, you could use JPA's native query.

What benefit would JPA provide here?
 
Edisandro Bessa
Ranch Hand
Posts: 584
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Jeanne for your prompt reply.

Regarding your comment :


... you could use JPA's native query.



So you are telling that JPA has a feature called "native query" where I can submit stored procedure calls ? (Depending of database support of course)


What benefit would JPA provide here?



Well, actually we have many legacy systems which heavily use stored procedures thru prepared statements as you said, so we started to wonder whether we could still continue using stored procedures for further applications but getting the benefits of JPA's connection pool manager and transactions, which are transparently managed by API, instead of hard controlling transactions by ourselves as we have done so far.

Thank you very much.
Edisandro
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Edisandro Bessa:
So you are telling that JPA has a feature called "native query" where I can submit stored procedure calls ? (Depending of database support of course)


Correct. You call entityManager.createNativeQuery()



benefits of JPA's connection pool manager and transactions, which are transparently managed by API, instead of hard controlling transactions by ourselves as we have done so far.


That's a good reason!
 
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How to get out parmeters by using native query. please provide some sample.
 
Ranch Hand
Posts: 553
 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi James, I followed the link you suggested and found this: "JPA does not support stored procedures that use OUTPUT or INOUT parameters.". I'm using MySQL, does it mean I cannot retrieve the out parameter from a procedure? I'm using EclipseLink annotations.

If there is a way, could you please post the code snippet?



theBalance is the OUT parameter, which I think I should not say "q.setParameter("param4", theBalance); " Still, how to retrieve the OUT parameter after doing the q.executeUpdate(); ?

Thanks a lot!
 
Juan Ramirez
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Got it now, I had a little mistake. Here is my code:

ANNOTATIONS:



CODE CALLING IT:




My discovery was that I had to keep the returnsResultSet=false to false in the annotation. I still don't know what a true would mean, but that was my initial try... Anyways, it works now with the OUT parameter and through JPA annotations.
 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, you need to set returnsResultSet=false if you want the query to return the output parameter values. If you set it to true, then it is assumed that the procedure will return a ResultSet, and the result set value will be returned (an outputParametersDetected event would be raised for any output parameters).
 
Juan Ramirez
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello James, I came up with another doubt

I have a stored procedure call getBalance. This balance, given an accountNumber IN parameter, should return with and OUT parameter the balance of that account holder. However, I would like to know how to handle exceptions correctly from my stored procedure. I thought registering an additional OUT parameter with something like a errorMessage name would be fine. Here is my code:



How do you recommend me handling SQL errors (like querying for an inextistent row)? and use JPA appropriately? I need to know from my JPAController class if something went wrong inside the procedure, and have an error message of what went wrong. Can you share some code snippet of MySQL and JPAController class?

Thank you!
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

James Sutherland wrote:Yes, you need to set returnsResultSet=false if you want the query to return the output parameter values. If you set it to true, then it is assumed that the procedure will return a ResultSet, and the result set value will be returned (an outputParametersDetected event would be raised for any output parameters).




Hi, Iam using Open JPA 1.2 where there is not @NamedStoredProc, Can some one help me how to call Stored proc in JPA 1.2 with IN & OUT Parameters.

Thanks a lot in Advance.
 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sorry buddy. I've been looking for over 3 days and it would appear open jpa doesn't let you call stored procedures with in and out parameters. Best bet is to crack open the stored proc and try and get the sql into a native named query.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic