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:
{call sp_name(?,?)}
instead of call sp_name(?,?)
getSingleResult
wont workresultSetMapping
name or result class detailsJPA 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.