如何从Java和JPA调用存储过程 [英] How to call a stored procedure from Java and JPA
问题描述
我正在编写一个简单的Web应用程序以调用存储过程并检索一些数据. 它是一个非常简单的应用程序,可以与客户的数据库进行交互.我们传递员工ID和公司ID,存储过程将返回员工详细信息.
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应用程序无法更新/删除数据,并且正在使用SQL Server.
Web application cannot update/delete data and is using SQL Server.
我正在Jboss AS中部署我的Web应用程序.我应该使用JPA访问存储过程还是CallableStatement
.在这种情况下使用JPA的任何优势.
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.
调用该存储过程的sql语句也将是什么.我以前从未使用过存储过程,因此我正在为此而苦苦挣扎. Google帮不上什么忙.
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.
这是存储过程:
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
更新:
对于任何其他使用 JPA 调用存储过程的人.
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();
我注意到的事情
- 参数名称对我不起作用,因此请尝试使用参数索引.
- 更正sql语句
{call sp_name(?,?)}
而不是call sp_name(?,?)
- 如果存储过程正在返回结果集,即使您只知道一行,
getSingleResult
也将无法工作 - 传递
resultSetMapping
名称或结果类详细信息
- Parameter names didn't work for me, so try using parameter index.
- Correct sql statement
{call sp_name(?,?)}
instead ofcall sp_name(?,?)
- If stored procedure is returning a result set, even if you know with only one row,
getSingleResult
wont work - Pass a
resultSetMapping
name or result class details
推荐答案
JPA 2.1 now support Stored Procedure, read the Java doc here.
示例:
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");
在此处中查看详细示例.
这篇关于如何从Java和JPA调用存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!