Calling Stored Procedure thru JPA

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 ?

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?

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

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!

How to get out parmeters by using native query. please provide some sample.

See,

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

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!

Got it now, I had a little mistake. Here is my code:

ANNOTATIONS:

CODE CALLING IT:

public int executeGetBalanceProcedure(int tranType, Timestamp dateTran, String oa){
        EntityManager em = getEntityManager();
        int theBalance = 0;
        try {
            EntityTransaction etx = em.getTransaction();
            etx.begin();
            Query q = em.createNamedQuery("GETBALANCE_PROCEDURE");
            q.setParameter("param1", tranType);
            q.setParameter("param2", dateTran);
            q.setParameter("param3", oa);
            for (Iterator it = q.getParameters().iterator(); it.hasNext();) {
                Parameter pa = (Parameter)it.next();
                System.out.println(pa.getName()+" - "+q.getParameterValue(pa.getName()));
            }
            //q.executeUpdate(); SOBRA, el getSingleResult lo hace por el
            theBalance = (Integer)q.getSingleResult();
            etx.commit();
        } catch (Exception ex) {
            Logger.getLogger(StoredProcedureJpaController.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            em.close();
        }
        return theBalance;
    }

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.

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).

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!