Calling stored procedure from Java / JPA

Question

Hi,

I am writing a simple web application to call a stored procedure and retrieve some data. Its a very simple application, which interacts with client's database. We pass employee id and company id and the stored procedure will return employee details.

Web application cannot update/delete data and is using SQL Server.

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.

Also what will be the sql statement to call this stored procedure. I have never used stored procedures before and I am struggling with this one. Google was not much of a help.

Here is the stored procedure:

CREATE procedure getEmployeeDetails (@employeeId int, @companyId int)
as
begin
    select firstName, lastName, gender, address
from
        employee et
where
            et.employeeId = @employeeId
            and et.companyId = @companyId
end

Update:

For anyone else having problem calling stored procedure using JPA.

Query query = em.createNativeQuery("{call getEmployeeDetails(?,?)}",EmployeeDetails.class)          
.setParameter(1, employeeId)
.setParameter(2, companyId);

List<EmployeeDetails> result = query.getResultList();

Things I have noticed:

1. parameter names didn't work for me, so try using parameter index.
2. correct sql statement "{call sp_name(?,?)}" instead of "call sp_name(?,?)"
3. If stored procedure is returning a result set, even if you know with only one row, getSingleResult wont work
4. pass a resultSetMapping name or result class details

Answer 1

To call stored procedure we can use Callable Statement in java.sql package.

Answer 2

See Calling Stored Procedures in JPA

Answer 3

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.

Answer 4

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