如何从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屋！