JPAでストアドプロシージャを実行する方法

概要

JPAでストアドプロシージャを実行する方法を確認しました。

環境

• Windows 10 Professional
• Oracle JDK 1.8.0_162
• Spring Data JPA 2.0.5
  • Hibernate ORM 5.2.14
• MySQL 5.7.19

参考

• [Spring Data JPA - Reference Documentation - 4.4. Stored procedures] (https://docs.spring.io/spring-data/jpa/docs/2.0.5.RELEASE/reference/html/#jpa.stored-procedures)
• [MySQL 5.7 Reference Manual / Stored Programs and Views] (https://dev.mysql.com/doc/refman/5.7/en/stored-programs-views.html)
• [PostgreSQL 9.6.5文書 - 第41章 PL/pgSQL - SQL手続き言語] (https://www.postgresql.jp/document/9.6/html/plpgsql.html)

ストアドプロシージャを実行する

デモプロシージャ

procdemo1.sql

DELIMITER //

DROP procedure IF EXISTS procdemo1//

CREATE PROCEDURE procdemo1(
    IN inArg INT
  , OUT outArg INT
  , INOUT varcharArg VARCHAR(80)
  , INOUT dateArg DATETIME)
COMMENT 'stored procedure call demo1'
LANGUAGE SQL
DETERMINISTIC CONTAINS SQL
BEGIN
   SET outRes = inArg + 1;
   SET varcharArg = UPPER(varcharArg);
   SET dateArg = CAST(CAST(dateArg AS DATE) AS DATETIME);
   SELECT dateArg AS 'current_time';
END;
//

SHOW WARNINGS//

DELIMITER ;

MySQL Clientからコンパイル、ストアドプロシージャを実行して動作確認します。

> source procdemo1.sql

> set @varg = 'demo';
> set @darg = now();

> call procdemo1(10, @result, @varg, @darg);

> select @result, @varg, @darg;
+---------+-------+---------------------+
| @result | @varg | @darg               |
+---------+-------+---------------------+
|      11 | DEMO  | 2018-03-25 00:00:00 |
+---------+-------+---------------------+
1 row in set (0.00 sec)

ストアドプロシージャのメタデータの定義

ストアドプロシージャの実行にメタデータの定義は必須ではありませんが、定義しておくことでコードを減らすことができます。
メタデータ定義方法にはエンティティクラスにアノテーションで定義する方法とマッピングファイルに定義する方法があります。

アノテーションで定義

関連のあるエンティティクラスにアノテーションで定義します。
（このデモプロシージャとMemoエンティティは関連がありませんが、便宜的にこのエンティティに定義しています。）

@Entity
@Table(name = "memo")
@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(name = "Memo.demo1", procedureName = "procdemo1", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "inArg", type = Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "outArg", type = Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.INOUT, name = "varcharArg", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.INOUT, name = "dateArg", type = LocalDateTime.class)
    })
})
public class Memo implements Serializable {

}

マッピングファイルで定義

Hibernateの場合は、resources/META-INF/orm.xmlに配置するマッピングファイルに定義することもできます。

<?xml version="1.0" encoding="UTF-8" ?>
<entity-mappings version="2.1"
    xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd">

    <named-stored-procedure-query name="Memo.demo1" procedure-name="procdemo1">
        <description>demo stored procedure</description>
        <parameter name="inArg" class="java.lang.Integer" mode="IN" />
        <parameter name="outArg" class="java.lang.Integer" mode="OUT" />
        <parameter name="varcharArg" class="java.lang.String" mode="INOUT" />
        <parameter name="dateArg" class="java.time.LocalDateTime" mode="INOUT" />
    </named-stored-procedure-query>

</entity-mappings>

repository

ストアドプロシージャの呼び出しを示すProcedureアノテーションを付けたメソッドを実装します。
メソッドの実装時に定義する引数はIN及びINOUT型です。OUT型の引数は不要です。
プロシージャの引数にINOUT及びOUT型がある場合、メソッドの戻り値として受け取る引数を1つだけoutputParameterName属性で指定します。
INOUT及びOUT型の引数が複数ある場合は、repositoryでストアドプロシージャを呼び出す方法ではすべて受け取ることはできないようです。

public interface MemoRepository extends JpaRepository<Memo, Long> {
    @Procedure(name = "Memo.demo1", outputParameterName = "outArg")
    Integer demo1(@Param("inArg") Integer inArg, @Param("varcharArg") String vArg, @Param("dateArg") LocalDateTime dArg);
}

定義したメタデータの名前とメソッド名が一致している場合はname属性は省略できます。

@Procedure(outputParameterName = "outArg")
Integer demo1(@Param("inArg") Integer inArg, @Param("varcharArg") String vArg, @Param("dateArg") LocalDateTime dArg);

createNamedStoredProcedureQuery

EntityManager.createNamedStoredProcedureQuery

public StoredProcedureQuery createNamedStoredProcedureQuery(String name);

createNamedStoredProcedureQueryに渡す名前はメタデータで定義したストアドプロシージャの名前です。
repositoryでは戻り値は1つしか選択できませんでしたが、EntityManagerから実行する方法であればすべて受け取ることができます。
受け取りたいINOUT及びOUT型の引数の値をgetOutputParameterValueで指定します。

Integer inArg = 1;
String varcharArg = "demo";
LocalDateTime dateArg = LocalDateTime.now();

StoredProcedureQuery demo1 = entityManager.createNamedStoredProcedureQuery("Memo.demo1");

demo1.setParameter("inArg", inArg)
     .setParameter("varcharArg", varcharArg)
     .setParameter("dateArg", dateArg);

demo1.execute();

Integer resOutArg = (Integer) demo1.getOutputParameterValue("outArg");
String resVarcharArg = (String) demo1.getOutputParameterValue("varcharArg");
LocalDateTime resDateArg = (LocalDateTime) demo1.getOutputParameterValue("dateArg");

System.out.println("outArg : " + resOutArg + " : varcharArg : " + resVarcharArg + " : dateArg : " + resDateArg);
// outArg : 2 : varcharArg : DEMO : dateArg : 2018-03-25T00:00

createStoredProcedureQuery

EntityManager.createStoredProcedureQuery

public StoredProcedureQuery createStoredProcedureQuery(String procedureName);

事前にメタデータを定義しておかなくても、実行時にメタデータを定義してストアドプロシージャを実行することもできます。
createStoredProcedureQueryに渡す名前はデータベース上のストアドプロシージャ名です。
ストアドプロシージャへ渡す引数は引数名ではなく引数の位置を指定する必要があります。

Integer inArg = 1;
String varcharArg = "demo";
LocalDateTime dateArg = LocalDateTime.now();

StoredProcedureQuery demo1 = entityManager.createStoredProcedureQuery("procdemo1")
    .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
    .registerStoredProcedureParameter(2, Integer.class ,ParameterMode.OUT)
    .registerStoredProcedureParameter(3, String.class, ParameterMode.INOUT)
    .registerStoredProcedureParameter(4, LocalDateTime.class, ParameterMode.INOUT);

demo1.setParameter(1, inArg)
     .setParameter(3, varcharArg)
     .setParameter(4, dateArg);

demo1.execute();

Integer resOutArg = (Integer) demo1.getOutputParameterValue(2);
String resVarcharArg = (String) demo1.getOutputParameterValue(3);
LocalDateTime resDateArg = (LocalDateTime) demo1.getOutputParameterValue(4);

System.out.println("outArg : " + resOutArg + " : varcharArg : " + resVarcharArg + " : dateArg : " + resDateArg);
// outArg : 2 : varcharArg : DEMO : dateArg : 2018-03-25T00:00

トランザクションの確認

アプリケーションのトランザクション内からデータ更新を伴うストアドプロシージャを呼び出した場合の確認です。

デモプロシージャ

memoテーブルを1件更新するデモ用のストアドプロシージャです。SELECT SLEEP(8);はタイムアウト検証用です。

procdemo2.sql

DELIMITER //

DROP procedure IF EXISTS procdemo2//

CREATE PROCEDURE procdemo2 (IN id INT, IN description VARCHAR(200))
COMMENT 'stored procedure call demo2'
LANGUAGE SQL
DETERMINISTIC MODIFIES SQL DATA
BEGIN

    UPDATE memo
       SET memo.description = CONCAT(memo.description, ' + ', description)
         , memo.done = true
         , memo.updated = NOW()
     WHERE memo.id = id;

    SELECT SLEEP(8);

END;
//
SHOW WARNINGS//

DELIMITER ;

データを更新する

ストアドプロシージャを実行して下記のデータを更新します。

> select * from memo where id = 18;
+----+------------+------------------+------+-------------------------+
| id | title      | description      | done | updated                 |
+----+------------+------------------+------+-------------------------+
| 18 | test title | test description |    0 | 2018-03-25 12:51:58.000 |
+----+------------+------------------+------+-------------------------+
1 row in set (0.00 sec)

アプリケーションのトランザクション内からデータを更新するストアドプロシージャを実行し、そのデータをEntityManagerで検索すると更新された状態のエンティティが取得できます。

@Transactional
public void callDemo2() {
    StoredProcedureQuery demo2 = entityManager.createNamedStoredProcedureQuery("Memo.demo2");

    demo2.setParameter("id", 18L)
         .setParameter("description", "test");

    demo2.execute();

    Memo memo = entityManager.find(Memo.class, 18L);
    System.out.println(memo.toString());
    // Memo(id=18, title=test title, description=test description + test, done=true, updated=2018-03-25T13:02:31)
}

MySQL Clientで更新結果を確認します。

> select * from memo where id = 18;
+----+------------+-------------------------+------+-------------------------+
| id | title      | description             | done | updated                 |
+----+------------+-------------------------+------+-------------------------+
| 18 | test title | test description + test |    1 | 2018-03-25 13:02:31.000 |
+----+------------+-------------------------+------+-------------------------+
1 row in set (0.00 sec)

タイムアウトした場合

@Transactional(timeout = 5)
public void callDemo2() {
    StoredProcedureQuery demo2 = entityManager.createNamedStoredProcedureQuery("Memo.demo2");

    demo2.setParameter("id", 18L)
         .setParameter("description", "test");

    demo2.execute();
}

実行してから5秒ほどで下記の例外がスローされデータはロールバックされます。

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults

//...省略

Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request

ストアドプロシージャが異常終了した場合

下記の例外がスローされます。

org.hibernate.exception.DataException: Error calling CallableStatement.getMoreResults

// ...省略

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1' for column 'updated' at row 1