1. Overview

1.概述

Stored Procedures are sets of compiled SQL statements residing in the database. They are used to encapsulate and share logic with other programs, and benefit from database-specific features like index hints or specific keywords.

存储程序是一组编译过的SQL语句，存放在数据库中。它们被用来封装并与其他程序共享逻辑，并受益于数据库的特定功能，如索引提示或特定关键词。

This article demonstrates how to use Hibernate to call a stored procedure in a MySQL database.

本文演示了如何使用Hibernate在MySQL数据库中调用存储过程。

2. Stored Procedures in MySQL

2.MySQL中的存储程序

Before we discuss how to call a stored procedure from Hibernate, we need to create it.

在我们讨论如何从Hibernate调用存储过程之前，我们需要创建它。

For this quick MySQL example, we’ll create a stored procedure to get all the records from a foo table.

在这个快速的MySQL例子中，我们将创建一个存储过程，从foo 表中获取所有记录。

To create a stored procedure, we make use of the CREATE PROCEDURE statement:

为了创建一个存储过程，我们使用CREATE PROCEDURE语句。

DELIMITER //
    CREATE PROCEDURE GetAllFoos()
        LANGUAGE SQL
        DETERMINISTIC
        SQL SECURITY DEFINER
        BEGIN
            SELECT * FROM foo;
        END //
DELIMITER;

Before the BEGIN statement, we can define optional statements. You can drill down into the details of these statements by following the official MySQL documentation link.

在BEGIN语句之前，我们可以定义可选语句。你可以按照官方的MySQL文档链接来深入了解这些语句的细节。

We can use the CALL statement to make sure that our procedure behave in the desired way:

我们可以使用CALL语句来确保我们的过程以期望的方式表现出来。

CALL GetAllFoos();

Now that we have our stored procedure up and running let’s jump straight to how to call it from Hibernate.

现在我们的存储过程已经启动并运行，让我们直接跳到如何从Hibernate调用它。

3. Call a Stored Procedure With Hibernate

3.用Hibernate调用一个存储过程

Starting from Hibernate 3, we have the possibility to use raw SQL statement including stored procedures to query a database.

从Hibernate 3开始，我们有可能使用包括存储过程在内的原始SQL语句来查询数据库。

In this section, we are going to walk through a seemingly basic example that will illustrate how to call the GetAllFoos() procedure using Hibernate.

在本节中，我们将通过一个看似基本的例子，说明如何使用Hibernate调用GetAllFoos()过程。

3.1. Configuration

3.1.配置

Before we start writing code that can run, we need to have configured Hibernate in our project.

在我们开始编写可以运行的代码之前，我们需要在项目中配置好Hibernate。

And of course for all of that – the Maven dependencies, MySQL configuration, Hibernate configuration and SessionFactory instantiation – you can check out the Hibernate article.

当然，对于所有这些–Maven依赖性、MySQL配置、Hibernate配置和SessionFactory实例化–你可以查看Hibernate文章。

**3.2. Call a Stored Procedure Using the CreateNativeSQL Method**

**3.2.使用CreateNativeSQL方法调用一个存储过程**

Hibernate allows to express queries in native SQL format directly. Therefore, we can straightforwardly create a native SQL query, and use the CALL statement to call the getAllFoos() stored procedure:

Hibernate允许直接以本地SQL格式表达查询。因此，我们可以直接创建一个本地SQL查询，并使用CALL语句来调用getAllFoos()存储过程。

Query query = session.createSQLQuery("CALL GetAllFoos()").addEntity(Foo.class);
List<Foo> allFoos = query.list();

The above query returns a list where each element is a Foo object.

上述查询返回一个列表，其中每个元素是一个Foo o对象。

We use the addEntity() method to get entity objects from the native SQL query, otherwise, a ClassCastException will be thrown whenever a stored procedure returns a non-raw value.

我们使用addEntity()方法从本地SQL查询中获取实体对象，否则，只要存储过程返回非原始值，就会抛出ClassCastException。

3.3. Call a Stored Procedure Using @NamedNativeQueries

3.3.使用@NamedNativeQueries调用一个存储过程

Another way to call a stored procedure is to use the @NamedNativeQueries annotation.

另一种调用存储过程的方法是使用@NamedNativeQueries注解。

@NamedNativeQueries is used to specify an array of native SQL named queries scoped to the persistence unit:

@NamedNativeQueries用于指定一个本地SQL命名的查询数组，范围为持久化单元：。

@NamedNativeQueries({ 
  @NamedNativeQuery(
    name = "callGetAllFoos", 
    query = "CALL GetAllFoos()", 
    resultClass = Foo.class) 
})
@Entity
public class Foo implements Serializable {
    // Model definition
}

Each named query has obviously a name attribute, the actual SQL query, and the resultClass which refers to the Foo mapped entity.

每个命名的查询显然都有一个name属性，实际的SQL查询，以及resultClass，它指向Foo映射的实体。

Query query = session.getNamedQuery("callGetAllFoos");
List<Foo> allFoos = query.list();

The resultClass attribute plays the same role as the addEntity() method in our previous example.

resultClass属性的作用与我们前面例子中的addEntity()方法相同。

Both of these approaches can be used interchangeably, as there are no real differences between the two when it comes to performance or productivity.

这两种方法可以互换使用，因为在性能或生产力方面，两者之间没有真正的区别。

3.4. Call a Stored Procedure Using @NamedStoredProcedureQuery

3.4.使用@NamedStoredProcedureQuery调用一个存储过程

If you are using JPA 2.1 and the Hibernate implementation of the EntityManagerFactory and EntityManager.

如果您使用的是JPA 2.1和Hibernate实现的EntityManagerFactory和EntityManager。

The @NamedStoredProcedureQuery annotation can be used to declare a stored procedure:

@NamedStoredProcedureQuery注解可用于声明一个存储过程。

@NamedStoredProcedureQuery(
  name="GetAllFoos",
  procedureName="GetAllFoos",
  resultClasses = { Foo.class }
)
@Entity
public class Foo implements Serializable {
    // Model Definition 
}

To call our named stored procedure query, we need to have instantiated an EntityManager, and then call the createNamedStoredProcedureQuery() method to create the procedure:

为了调用我们的命名存储过程查询，我们需要实例化一个EntityManager，，然后调用createNamedStoredProcedureQuery()方法来创建存储过程：。

StoredProcedureQuery spQuery = 
  entityManager.createNamedStoredProcedureQuery("getAllFoos");

We can directly get the list of Foo entities by calling the execute() method on StoredProcedureQuery object.

我们可以通过调用Foo对象的execute()方法直接获得StoredProcedureQuery实体列表。

4. Stored Procedures With Parameters

4.带参数的存储程序

Almost all our stored procedures will require parameters. In this section, we are going to show how to call a stored procedure with parameters from Hibernate.

几乎所有的存储过程都需要参数。在本节中，我们将展示如何从Hibernate调用带参数的存储过程。

Let’s create a getFoosByName() stored procedure in MySQL.

让我们在MySQL中创建一个getFoosByName()存储过程。

This procedure returns a list of Foo objects where the name attribute matches the fooName parameter:

这个过程返回一个Foo对象的列表，其中name属性与fooName参数匹配。

DELIMITER //
    CREATE PROCEDURE GetFoosByName(IN fooName VARCHAR(255))
        LANGUAGE SQL
        DETERMINISTIC
        SQL SECURITY DEFINER
        BEGIN
            SELECT * FROM foo WHERE name = fooName;
        END //
DELIMITER;

To call the GetFoosByName() procedure we will use named parameters:

为了调用GetFoosByName()程序，我们将使用命名参数。

Query query = session.createSQLQuery("CALL GetFoosByName(:fooName)")
  .addEntity(Foo.class)
  .setParameter("fooName","New Foo");

Similarly, the named parameter :fooName can be used with the @NamedNativeQuery annotation:

同样，命名参数:fooName可以与@NamedNativeQuery注解一起使用。

@NamedNativeQuery(
  name = "callGetFoosByName", 
  query = "CALL GetFoosByName(:fooName)", 
  resultClass = Foo.class
)

The named query would be called as follows:

命名的查询将被调用，如下所示。

Query query = session.getNamedQuery("callGetFoosByName")
  .setParameter("fooName","New Foo");

When using the @NamedStoredProcedureQuery annotation, we can specify parameters using the @StoredProcedureParameter annotation:

当使用@NamedStoredProcedureQuery注解时，我们可以使用@StoredProcedureParameter注解指定参数。

@NamedStoredProcedureQuery(
  name="GetFoosByName",
  procedureName="GetFoosByName",
  resultClasses = { Foo.class },
  parameters={
    @StoredProcedureParameter(name="fooName", type=String.class, mode=ParameterMode.IN)
  }
)

We can make use of the setParameter() method to call our stored procedure with the fooName parameter:

我们可以利用setParameter()方法，用fooName参数调用我们的存储过程。

StoredProcedureQuery spQuery = entityManager.createNamedStoredProcedureQuery("GetFoosByName")
  .setParameter("fooName", "NewFooName");

5. Conclusion

5.结论

This article demonstrated how to use Hibernate to call a stored procedure in a MySQL database using different approaches.

本文演示了如何使用Hibernate在MySQL数据库中调用存储过程，使用了不同的方法。

It is worth mentioning that not all RDBMS support stored procedures.

值得一提的是，并非所有RDBMS都支持存储过程。

You can checkout the examples provided in this article in the linked GitHub project.

你可以在链接的GitHub项目中查看本文提供的例子。