A Guide to Stored Procedures with JPA – 使用JPA的存储程序指南

最后修改: 2016年 3月 13日

中文/混合/英文(键盘快捷键：t)

1. Introduction

1.介绍

In this quick tutorial we’ll explore the use of Stored Procedures within the Java Persistence API (JPA).

在这个快速教程中，我们将探讨在Java Persistence API（JPA）中使用存储程序。

2. Project Setup

2.项目设置

2.1. Maven Setup

2.1.Maven设置

We first need to define the following dependencies in our pom.xml:

我们首先需要在我们的pom.xml：中定义以下的依赖。

  • javax.javaee-api – as it includes the JPA API
  • a JPA API implementation – in this example we will use Hibernate, but EclipseLink would be an OK alternative as well
  • a MySQL Database
<properties>
    <jee.version>7.0</jee.version>
    <mysql.version>11.2.0.4</mysql.version>
    <hibernate.version>5.1.38</hibernate.version>
</properties>
<dependencies>
    <dependency>
        <groupId>javax</groupId>
        <artifactId>javaee-api</artifactId>
        <version>${jee.version}</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>${hibernate.version}</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>${mysql.version}</version>
    </dependency>
</dependencies>

2.2. Persistence Unit Definition

2.2.持久性单元的定义

The second step is the creation of src/main/resources/META-INF/persistence.xml file – which contains the persistence unit definitions:

第二步是创建src/main/resources/META-INF/persistence.xml文件–它包含持久化单元的定义。

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
    http://java.sun.com/xml/ns/persistence/persistence_2_1.xsd"
    version="2.1">

    <persistence-unit name="jpa-db">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <class>com.baeldung.jpa.model.Car</class>
        <properties>
            <property name="javax.persistence.jdbc.driver" 
              value="com.mysql.jdbc.Driver" />
            <property name="javax.persistence.jdbc.url" 
              value="jdbc:mysql://127.0.0.1:3306/baeldung" />
            <property name="javax.persistence.jdbc.user" 
              value="baeldung" />
            <property name="javax.persistence.jdbc.password" 
              value="YourPassword" />
            <property name="hibernate.dialect" 
              value="org.hibernate.dialect.MySQLDialect" />
            <property name="hibernate.show_sql" value="true" />
        </properties>
    </persistence-unit>

</persistence>

All Hibernate properties defined are not needed if you refer to a JNDI DataSource (JEE environments):

如果你引用JNDI数据源（JEE环境），则不需要定义所有Hibernate属性。

<jta-data-source>java:jboss/datasources/JpaStoredProcedure</jta-data-source>

2.3. Table Creation Script

2.3.表格创建脚本

Let’s now create a Table ( CAR ) – with three attributes: ID, MODEL and YEAR:

让我们现在创建一个表( CAR ) – 有三个属性。ID, MODEL 和 YEAR:

CREATE TABLE `car` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `MODEL` varchar(50) NOT NULL,
  `YEAR` int(4) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

The assumption was, of course, that the DB schema and permissions are already in place.

当然，我们的假设是，数据库模式和权限已经到位了。

2.4. Stored Procedure Creation on DB

2.4.在DB上创建存储过程

The very last step before jump to the java code is the stored procedure creation in our MySQL Database:

在跳到java代码之前的最后一步是在我们的MySQL数据库中创建存储过程。

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `FIND_CAR_BY_YEAR`(in p_year int)
begin
SELECT ID, MODEL, YEAR
    FROM CAR
    WHERE YEAR = p_year;
end
$$
DELIMITER ;

3. The JPA Stored Procedure

3.JPA存储过程

We are now ready to use JPA to communicate with the database and execute the stored procedure we defined.

我们现在已经准备好使用JPA与数据库进行通信并执行我们定义的存储过程。

Once we do that, we’ll also be able to iterate over the results as a List of Car.

一旦我们做到这一点，我们也将能够以List of Car.的形式遍历结果。

3.1. The Car Entity

3.1.汽车实体

Below the Car entity that well be mapped to the CAR database table by the Entity Manager.

下面是 Car实体，它可以被实体管理器映射到CAR数据库表。

Notice that we’re also defining our stored procedure directly on the entity by using the @NamedStoredProcedureQueries annotation:

注意，我们还通过使用@NamedStoredProcedureQueries注解，直接在实体上定义我们的存储过程。

@Entity
@Table(name = "CAR")
@NamedStoredProcedureQueries({
  @NamedStoredProcedureQuery(
    name = "findByYearProcedure", 
    procedureName = "FIND_CAR_BY_YEAR", 
    resultClasses = { Car.class }, 
    parameters = { 
        @StoredProcedureParameter(
          name = "p_year", 
          type = Integer.class, 
          mode = ParameterMode.IN) }) 
})
public class Car {

    private long id;
    private String model;
    private Integer year;

    public Car(String model, Integer year) {
        this.model = model;
        this.year = year;
    }

    public Car() {
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", unique = true, nullable = false, scale = 0)
    public long getId() {
        return id;
    }

    @Column(name = "MODEL")
    public String getModel() {
        return model;
    }

    @Column(name = "YEAR")
    public Integer getYear() {
        return year;
    }
    
    // standard setter methods
}

3.2. Accessing the Database

3.2.访问数据库

Now, with everything defined and in place, let’s write a couple of tests actually using JPA to execute the procedure.

现在，所有的东西都定义好了，让我们写几个测试，实际使用JPA来执行这个过程。

We are going to retrieve all Cars in a given year:

我们将检索给定年的所有汽车：

public class StoredProcedureTest {

    private static EntityManagerFactory factory = null;
    private static EntityManager entityManager = null;

    @BeforeClass
    public static void init() {
        factory = Persistence.createEntityManagerFactory("jpa-db");
        entityManager = factory.createEntityManager();
    }

    @Test
    public void findCarsByYearWithNamedStored() {
        StoredProcedureQuery findByYearProcedure = 
          entityManager.createNamedStoredProcedureQuery("findByYearProcedure");
        
        StoredProcedureQuery storedProcedure = 
          findByYearProcedure.setParameter("p_year", 2015);
        
        storedProcedure.getResultList()
          .forEach(c -> Assert.assertEquals(new Integer(2015), ((Car) c).getYear())); 
    }

    @Test
    public void findCarsByYearNoNamedStored() {
        StoredProcedureQuery storedProcedure = 
          entityManager
            .createStoredProcedureQuery("FIND_CAR_BY_YEAR",Car.class)
            .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
            .setParameter(1, 2015);
       
        storedProcedure.getResultList()
          .forEach(c -> Assert.assertEquals(new Integer(2015), ((Car) c).getYear()));
    }

}

Notice that in the second test, we’re no longer using the stored procedure we defined on the entity. Instead, we’re are defining the procedure from scratch.

注意，在第二个测试中，我们不再使用我们在实体上定义的存储过程。相反，我们正在从头开始定义这个存储过程。

That can be very useful when you need to use stored procedures but you don’t have the option to modify your entities and recompile them.

当你需要使用存储过程时，这可能非常有用，但你不能选择修改你的实体并重新编译它们。

4. Conclusion

4.结论

In this tutorial we discussed using Stored Procedure with the Java Persistence API.

在本教程中，我们讨论了使用存储过程与Java持久性API的问题。

The example used in this article is available as a sample project in GitHub.

本文中使用的示例可作为GitHub中的示例项目。