Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to call stored procedure from Entity Manager if there is out parameter in stored procedure.

 
xsunil kumar
Ranch Hand
Posts: 143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am trying to call a stored procedure using JPA entity manager. In my stored procedure, i have some in and out parameters. I need to get value from out parameter. Please suggest.
 
Victor Dolirio
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello xsunil,

You might use named native queries to do this (annotations or xml mapping). If you are using hibernate as your persistence provider, set the hint "org.hibernate.callable" to true. To get out parameters you might use your entity mapping normally (property resultClass) or use scalar values normally as well.
Other provider than hibernate I don't how to implement surely.

Hope to had help.

[]s,
Victor Dolirio
 
xsunil kumar
Ranch Hand
Posts: 143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Victor ,

But i am using JPA and how to get out parameter using named native / native query. If you have any sample example, it will be great help on me.

-sunil
 
Victor Dolirio
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sunil, suppose that you are using hibernate as you persistence provider and that we have the following SP:



Now you might define a named native query so that it remains callable by using a hint (in annotation):



Now you can use JPA API to get the result:



It works as well with scalar projections...

[]s,
Victor Dolirio
 
kin lau
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
do you have sample for eclipselink JPA to call stored procedure ..?
 
Victor Dolirio
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This example was written purelly in JPA. You can use it normally but needs to research if you need to pass some hint to the eclipselink provider.
 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use the @NamedStoredProcedureQuery annotation to define a query to a stored procedure in EclipseLink. Or you could you the StoredProcedureCall class.

see,
http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Using_EclipseLink_JPA_Extensions_for_Stored_Procedure_Query


also see,
http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#Stored_Procedures
 
Juan Ramirez
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi James,

I'm using annotations, and I have a stored procedure with 3 IN parameters and 2 OUT parameters. How can I execute and retrieve those 2 OUT parameters? I tried using this:

MY PROCEDURE:



MY CALLING METHOD


This would actually execute my procedure, I can verify that from a little insert I'm doing with it, however the 2 output parameters do not show up . Is this the method q.getResultList().size(); to use to pick them up?

Thank you!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic