如何从Java和JPA中调用存储过程

Question

我正在编写一个简单的Web应用程序，调用一个存储过程并检索一些数据。 这是一个与客户数据库交互的非常简单的应用程序。 我们传递员工ID和公司ID，存储过程将返回员工详细信息。

Web应用程序无法更新/删除数据，并且正在使用SQL Server。

我将我的Web应用程序部署在Jboss AS上。 在这种情况下，我应该使用JPA访问存储过程还是CallableStatement？使用JPA的任何优点吗？

此外，调用此存储过程的SQL语句将是什么？ 我以前从未使用过存储过程，这个让我很困惑。 谷歌并没有提供太多帮助。

以下是存储过程：

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调用存储过程遇到问题的人。

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 名称或结果类的详细信息。

- user431514

---

在 native 查询中你不能使用命名参数，命名参数仅支持JPQL查询。如果你喜欢命名参数，你可以编写自己的类来将命名参数转换为数字参数。 - Viliam Búr

我一直都是使用具名参数与createNativeQueries，并且从未遇到任何问题。我刚刚看了一下我一直在工作的当前系统，有大量的带有命名参数的本地查询。您能为您的断言提供一些参考资料吗？我们的设定是JPA 2和Hibernate 4+。 - Jaumzera

Answer 1

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

示例：

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");

请到此链接查看详细示例。

- Pau Kiat Wee

---

示例使用函数而不是存储过程。 - Sergio Marsilli

这对我有用。它适用于Oracle存储过程。 - Brian Akumah

您还可以将所有配置级联起来，在最后调用execute以使其更简洁。每次调用storedProcedure。 - Brian Akumah

Answer 2

我正在将我的Web应用程序部署在Jboss AS上。在访问存储过程或CallableStatement时，我应该使用JPA吗？在这种情况下使用JPA有什么优势吗？

JPA并不是真正支持的，但是可行。但我不会选择这条路：

- 仅为了将存储过程调用的结果映射到一些bean中而使用JPA实在太过于复杂， - 特别是考虑到JPA不太适合调用存储过程（语法将非常冗长）。

因此，我更倾向于考虑使用Spring JDBC数据访问支持，或者像MyBatis这样的数据映射器，或者根据您的应用程序的简单性，使用原始的JDBC和CallableStatement。 实际上，JDBC可能是我的首选。 下面是一个基本的示例：

CallableStatement cstmt = con.prepareCall("{call getEmployeeDetails(?, ?)}");
cstmt.setInt("employeeId", 123);
cstmt.setInt("companyId", 456);
ResultSet rs = cstmt.executeQuery();

参考文献

• JDBC 文档：Java SE 6

- Pascal Thivent

---

如下面的答案所述，它是被支持的 - 你可能想要编辑。 - Mr_and_Mrs_D

Answer 3

1. For a simple stored procedure that using IN/OUT parameters like this

   CREATE OR REPLACE PROCEDURE count_comments (  
      postId IN NUMBER,  
      commentCount OUT NUMBER )  
   AS 
   BEGIN 
       SELECT COUNT(*) INTO commentCount  
       FROM post_comment  
       WHERE post_id = postId; 
   END;
   

   You can call it from JPA as follows:

   StoredProcedureQuery query = entityManager
       .createStoredProcedureQuery("count_comments")
       .registerStoredProcedureParameter(1, Long.class, 
           ParameterMode.IN)
       .registerStoredProcedureParameter(2, Long.class, 
           ParameterMode.OUT)
       .setParameter(1, 1L);
   
   query.execute();
   
   Long commentCount = (Long) query.getOutputParameterValue(2);
   

2. For a stored procedure which uses a SYS_REFCURSOR OUT parameter:

   CREATE OR REPLACE PROCEDURE post_comments ( 
      postId IN NUMBER, 
      postComments OUT SYS_REFCURSOR ) 
   AS 
   BEGIN
       OPEN postComments FOR
       SELECT *
       FROM post_comment 
       WHERE post_id = postId; 
   END;
   

   You can call it as follows:

   StoredProcedureQuery query = entityManager
       .createStoredProcedureQuery("post_comments")
       .registerStoredProcedureParameter(1, Long.class, 
            ParameterMode.IN)
       .registerStoredProcedureParameter(2, Class.class, 
            ParameterMode.REF_CURSOR)
       .setParameter(1, 1L);
   
   query.execute();
   
   List<Object[]> postComments = query.getResultList();
   

3. For a SQL function that looks as follows:

   CREATE OR REPLACE FUNCTION fn_count_comments ( 
       postId IN NUMBER ) 
       RETURN NUMBER 
   IS
       commentCount NUMBER; 
   BEGIN
       SELECT COUNT(*) INTO commentCount 
       FROM post_comment 
       WHERE post_id = postId; 
       RETURN( commentCount ); 
   END;
   

   You can call it like this:

   BigDecimal commentCount = (BigDecimal) entityManager
   .createNativeQuery(
       "SELECT fn_count_comments(:postId) FROM DUAL"
   )
   .setParameter("postId", 1L)
   .getSingleResult();
   

   At least when using Hibernate 4.x and 5.x because the JPA StoredProcedureQuery does not work for SQL FUNCTIONS.

如需了解在使用 JPA 和 Hibernate 时如何调用存储过程和函数的更多细节，请查看以下文章：

• 如何在 JPA 和 Hibernate 中调用 Oracle 存储过程和函数
• 如何在 JPA 和 Hibernate 中调用 SQL Server 存储过程和函数
• 如何在 JPA 和 Hibernate 中调用 PostgreSQL 函数（存储过程）
• 如何在 JPA 和 Hibernate 中调用 MySQL 存储过程和函数

- Vlad Mihalcea

---

我一直收到“调用时参数数量或类型错误”的错误消息。我意识到我正在调用createNativeQuery。我切换到createStoredProcedureQuery。然后，哇！ - Ahmet

Answer 4

您需要将参数传递给存储过程。

它应该像这样工作：

    List result = em
      .createNativeQuery("call getEmployeeDetails(:employeeId,:companyId)")
      .setParameter("emplyoyeeId", 123L)
      .setParameter("companyId", 456L)
      .getResultList();

---

更新：

或许也不应该。

在书籍EJB3 in Action的第383页上，它说JPA不支持存储过程（该页仅为预览，您无法获得完整文本，整本书可以在多个地方下载，包括这里，但我不知道是否合法）。

无论如何，文字如下：

JPA和数据库存储过程

如果您是SQL的忠实粉丝，则可能愿意利用数据库存储过程的功能。不幸的是，JPA不支持存储过程，您必须依赖于持久性提供程序的专有功能。但是，您可以使用简单的存储函数（没有输出参数）与本机SQL查询。

- Sean Patrick Floyd

---

我尝试了一下，但是出现了以下错误信息：java.sql.SQLException: @P0 附近的语法不正确。 - user431514

针对 SQL Server，应该写成 "{call getEmployeeDetails(:employeeId,:companyId)}"，必须使用花括号。 - Vedran

@Vedran 确实。我只对参数设置部分感兴趣。 - Sean Patrick Floyd

Answer 5

如何使用JPA检索存储过程输出参数（2.0需要导入EclipseLink，而2.1不需要）

尽管本答案详细阐述了如何从存储过程返回记录集，但我还是在这里发帖，因为我花费了很长时间才弄清楚，并且这个主题对我有所帮助。

我的应用程序使用的是Eclipselink-2.3.1，但我将强制升级到Eclipselink-2.5.0，因为JPA 2.1对存储过程有更好的支持。

使用EclipseLink-2.3.1/JPA-2.0：实现相关

此方法需要从“org.eclipse.persistence”导入EclipseLink类，因此它特定于Eclipselink实现。

我在"http://www.yenlo.nl/en/calling-oracle-stored-procedures-from-eclipselink-with-multiple-out-parameters"找到了这个方法。

StoredProcedureCall storedProcedureCall = new StoredProcedureCall();
storedProcedureCall.setProcedureName("mypackage.myprocedure");
storedProcedureCall.addNamedArgument("i_input_1"); // Add input argument name.
storedProcedureCall.addNamedOutputArgument("o_output_1"); // Add output parameter name.
DataReadQuery query = new DataReadQuery();
query.setCall(storedProcedureCall);
query.addArgument("i_input_1"); // Add input argument names (again);
List<Object> argumentValues = new ArrayList<Object>();
argumentValues.add("valueOf_i_input_1"); // Add input argument values.
JpaEntityManager jpaEntityManager = (JpaEntityManager) getEntityManager();
Session session = jpaEntityManager.getActiveSession();
List<?> results = (List<?>) session.executeQuery(query, argumentValues);
DatabaseRecord record = (DatabaseRecord) results.get(0);
String result = String.valueOf(record.get("o_output_1")); // Get output parameter

使用EclipseLink-2.5.0/JPA-2.1: 实现独立 (已在本主题中记录)

此方法与具体实现无关（不需要导入Eclipslink）。

StoredProcedureQuery query = getEntityManager().createStoredProcedureQuery("mypackage.myprocedure");
query.registerStoredProcedureParameter("i_input_1", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("o_output_1", String.class, ParameterMode.OUT);
query.setParameter("i_input_1", "valueOf_i_input_1");
boolean queryResult = query.execute();
String result = String.valueOf(query.getOutputParameterValue("o_output_1"));

- Malcolm Boekhoff

---

啊，我的眼睛疼。这比JDBC好不了多少，是吧？ - Lukas Eder

哈哈，是的，我明白了。然而使用这些工具的好处在于，你不必输入大量代码来获取数据对象类，也不必将所有数据从记录集转移到数据类中。仍然有一个数据对象（实体），但是Eclipse向导会为您生成它。 - Malcolm Boekhoff

是的，你可以。但我是作为 jOOQ 的开发人员在说这个，其中一切都是自动生成的。唯一剩下要做的就是实际调用过程/函数。 - Lukas Eder

你实际上尝试过底部的例子（独立于实现）吗？我尝试了一下，只是将过程定义在一个 xml 文件中，但它没有起作用。我无法读取 OUT 参数。 - Roland

对于JPA-2.1实现，命名参数在我的情况下无法工作。相反，我必须传递它们在存储过程中的位置索引，并成功地获得了输出参数的结果。当我有多个结果集返回的存储过程时，就会出现这种情况。对于一个结果集，我只需使用@Query即可。 - Radhesh Khanna

Answer 6

对于我来说，只有以下方法适用于Oracle 11g和Glassfish 2.1（Toplink）：

Query query = entityManager.createNativeQuery("BEGIN PROCEDURE_NAME(); END;");
query.executeUpdate();

使用花括号的变量导致了ORA-00900错误。

- Dmitry Chornyi

---

在我的环境中，使用Oracle 11g和Hibernate JPA提供程序可以正常工作。 - David Mann

这让我们从一个极其严重的麻烦中解脱出来了。我们使用的是java6、oracle11g、Jboss6和Hibernate。感谢@Chornyi。 - Abdullah Khan

Answer 7

如果使用EclipseLink，您可以使用@NamedStoredProcedureQuery或StoreProcedureCall来执行任何存储过程，包括具有输出参数或输出游标的存储过程。还支持存储函数和PLSQL数据类型。

See, http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#Stored_Procedures

- James

---

EclipseLink的哪个版本有EntityManager.createNamedStoredProcedureQuery()方法？ - Mircea Ion

Answer 8

以下对我有效：

Query query = em.createNativeQuery("BEGIN VALIDACIONES_QPAI.RECALC_COMP_ASSEMBLY('X','X','X',0); END;");
query.executeUpdate();

- roberto quevedo

---

OUT和INOUT参数在使用此API时无法工作。请参见http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#Stored_Procedures_in_JPA_2.0 - Valentin Jacquemin

Answer 9

这个对我有用。

@Entity
@Table(name="acct")
@NamedNativeQueries({
 @NamedNativeQuery(callable=true, name="Account.findOne", query="call sp_get_acct(?), resultClass=Account.class)})
public class Account{
 // Code 
}

注意：如果将来决定使用默认版本的findOne，则只需注释掉NamedNativeQueries注释，JPA将切换到默认版本。

- Amit

---

如果我想调用特定包中的过程，我应该这样调用：call {package}.{procedure}吗？ - Jeff Bootsholz

Answer 10

也许对于使用Sql Server的人来说情况不同，但对于使用Oracle和EclipseLink的人来说，这对我很有用。

例如：一个过程有一个输入参数（类型为CHAR）和两个输出参数（NUMBER和VARCHAR）。

在persistence.xml中声明持久性单元：

<persistence-unit name="presistanceNameOfProc" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>jdbc/DataSourceName</jta-data-source>
    <mapping-file>META-INF/eclipselink-orm.xml</mapping-file>
    <properties>
        <property name="eclipselink.logging.level" value="FINEST"/>
        <property name="eclipselink.logging.logger" value="DefaultLogger"/>
        <property name="eclipselink.weaving" value="static"/>
        <property name="eclipselink.ddl.table-creation-suffix" value="JPA_STORED_PROC" />
    </properties>
</persistence-unit>

并在eclipselink-orm.xml中声明过程的结构

<?xml version="1.0" encoding="UTF-8"?><entity-mappings version="2.0"
xmlns="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm orm_2_0.xsd">
<named-stored-procedure-query name="PERSIST_PROC_NAME" procedure-name="name_of_proc" returns-result-set="false">
    <parameter direction="IN" name="in_param_char" query-parameter="in_param_char" type="Character"/>
    <parameter direction="OUT" name="out_param_int" query-parameter="out_param_int" type="Integer"/>
    <parameter direction="OUT" name="out_param_varchar" query-parameter="out_param_varchar" type="String"/>
</named-stored-procedure-query>

在代码中，你只需像这样调用你的proc:

try {
        final Query query = this.entityManager
                .createNamedQuery("PERSIST_PROC_NAME");
        query.setParameter("in_param_char", 'V'); 
        resultQuery = (Object[]) query.getSingleResult();

    } catch (final Exception ex) {
        LOGGER.log(ex);
        throw new TechnicalException(ex);
    }

获取两个输出参数：

Integer myInt = (Integer) resultQuery[0];
String myStr =  (String) resultQuery[1];

- Réda