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

[xsunil kumar]

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]

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]

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]

Sunil, suppose that you are using hibernate as you persistence provider and that we have the following SP:

create or replace procedure
    findPerson(my_cursor out sys_refcursor, param in string)  
as  
begin  
    open my_cursor for  
        select p.* from persons p where u.name like param;  

end findPerson;  

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

@NamedNativeQuery(name = "personQuery",  
    resultClass = Person.class,  
    query = "{call findPerson(?, :personName)}",  
    hints = {  
       @QueryHint(name = "org.hibernate.callable", value = "true")
   }  
)

Now you can use JPA API to get the result:

Query query = em.createNamedQuery("personQuery");  
query.setParameter("personName","john");  
List folks = query.getResultList();  

It works as well with scalar projections...

[]s,
Victor Dolirio

[kin lau]

do you have sample for eclipselink JPA to call stored procedure ..?

[Victor Dolirio]

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]

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]

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:

   CREATE DEFINER=`root`@`localhost` PROCEDURE `getBalanceProc`(IN tranType INT, IN dateTran TIMESTAMP,
IN accNum VARCHAR(20), OUT balance INT, OUT errorMessage VARCHAR(45)) ... ETC ETC 

MY CALLING METHOD

  EntityManager em = getEntityManager();
        int theBalance = 0;
        try {
            EntityTransaction etx = em.getTransaction();
            etx.begin();
            Query q = em.createNamedQuery("GETBALANCE_PROCEDURE");
            q.setParameter("transactionTypeParam", tranType);
            q.setParameter("dateTransactionParam", dateTran);
            q.setParameter("originAccountParam", oa);
            for (Iterator it = q.getParameters().iterator(); it.hasNext();) {
                Parameter pa = (Parameter)it.next();
                System.out.println(pa.getName()+" - "+q.getParameterValue(pa.getName()));
            }
            q.getResultList().size();

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!