How to call a stored procedure from Java and JPA

JPA 2.1 now support Stored Procedure, read the Java doc here.

Example:

StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("sales_tax");// set parametersstoredProcedure.registerStoredProcedureParameter("subtotal", Double.class, ParameterMode.IN);storedProcedure.registerStoredProcedureParameter("tax", Double.class, ParameterMode.OUT);storedProcedure.setParameter("subtotal", 1f);// execute SPstoredProcedure.execute();// get resultDouble tax = (Double)storedProcedure.getOutputParameterValue("tax");

See detailed example here.

I am deploying my web application in Jboss AS. Should I use JPA to access the stored procedure or CallableStatement. Any advantage of using JPA in this case.

It is not really supported by JPA but it's doable. Still I wouldn't go this way:

• using JPA just to map the result of a stored procedure call in some beans is really overkill,
• especially given that JPA is not really appropriate to call stored procedure (the syntax will be pretty verbose).

I would thus rather consider using Spring support for JDBC data access, or a data mapper like MyBatis or, given the simplicity of your application, raw JDBC and CallableStatement. Actually, JDBC would probably be my choice. Here is a basic kickoff example:

CallableStatement cstmt = con.prepareCall("{call getEmployeeDetails(?, ?)}");cstmt.setInt("employeeId", 123);cstmt.setInt("companyId", 456);ResultSet rs = cstmt.executeQuery();

Reference

• JDBC documentation: Java SE 6

You need to pass the parameters to the stored procedure.

It should work like this:

    List result = em      .createNativeQuery("call getEmployeeDetails(:employeeId,:companyId)")      .setParameter("emplyoyeeId", 123L)      .setParameter("companyId", 456L)      .getResultList();

Update:

Or maybe it shouldn't.

In the Book EJB3 in Action, it says on page 383, that JPA does not support stored procedures (page is only a preview, you don't get the full text, the entire book is available as a download in several places including this one, I don't know if this is legal though).

Anyway, the text is this:

JPA and database stored procedures

If you’re a big fan of SQL, you may bewilling to exploit the power ofdatabase stored procedures.Unfortunately, JPA doesn’t supportstored procedures, and you have todepend on a proprietary feature ofyour persistence provider. However,you can use simple stored functions(without out parameters) with a nativeSQL query.