如何从 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();

我注意到的事情:

1. 参数名称对我不起作用，因此请尝试使用参数索引.
2. 正确的sql语句{call sp_name(?,?)}而不是call sp_name(?,?)
3. 如果存储过程返回一个结果集，即使你知道只有一行，getSingleResult 也不会工作
4. 传递resultSetMapping名称或结果类详细信息

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

推荐答案

JPA 2.1 现在支持存储过程，请阅读 Java 文档 此处.

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屋！