aspose file tools*
The moose likes Object Relational Mapping and the fly likes Calling Stored Procedure thru JPA Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Calling Stored Procedure thru JPA" Watch "Calling Stored Procedure thru JPA" New topic
Author

Calling Stored Procedure thru JPA

Edisandro Bessa
Ranch Hand

Joined: Jan 19, 2006
Posts: 584
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 ?


"If someone asks you to do something you don't know how to, don't tell I don't know, tell I can learn instead." - Myself
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30512
    
150

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?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Edisandro Bessa
Ranch Hand

Joined: Jan 19, 2006
Posts: 584
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30512
    
150

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!
xsunil kumar
Ranch Hand

Joined: Dec 14, 2009
Posts: 133
How to get out parmeters by using native query. please provide some sample.
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
See,

http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#Stored_Procedures

TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Juan Ramirez
Greenhorn

Joined: Feb 01, 2010
Posts: 7
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

Joined: Feb 01, 2010
Posts: 7
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

Joined: Oct 01, 2007
Posts: 553
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

Joined: Feb 01, 2010
Posts: 7
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!
vasu muli
Greenhorn

Joined: May 11, 2012
Posts: 3
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.
Derek West
Greenhorn

Joined: Jun 18, 2012
Posts: 9
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Calling Stored Procedure thru JPA