How to call a stored procedure from Java and JPA

Question

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

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.