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 parameters
storedProcedure.registerStoredProcedureParameter("subtotal", Double.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("tax", Double.class, ParameterMode.OUT);
storedProcedure.setParameter("subtotal", 1f);
// execute SP
storedProcedure.execute();
// get result
Double 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 be willing to exploit the power of database stored procedures. Unfortunately, JPA doesn’t support stored procedures, and you have to depend on a proprietary feature of your persistence provider. However, you can use simple stored functions (without out parameters) with a native SQL query.