JPQLからデータベースのユーザー定義関数を利用する

概要

Spring BootとSpring Data JPA(ORMはHibernate)で実装したアプリケーションで、JPQLからデータベースのユーザー定義関数(create function xxx ...)を利用するサンプルコードです。

環境

• Windows10 Professional
• Java 1.8.0_144
• Spring Boot 1.5.6
  • Spring Data JPA 1.11.6
  • Hibernate 5.0.1
• MySQL 5.6.25

参考

• [How to call custom database functions with JPA and Hibernate] (https://www.thoughts-on-java.org/database-functions/)
• [How to use a custom function in a jpa query?] (https://stackoverflow.com/questions/42484199/how-to-use-a-custom-function-in-a-jpa-query)
• [MySQL 5.6 リファレンスマニュアル / 13.1.15 CREATE PROCEDURE および CREATE FUNCTION 構文] (https://dev.mysql.com/doc/refman/5.6/ja/create-procedure.html)

サンプルコード

データベースのユーザー定義関数

サンプルのJPQLで利用するユーザー定義関数です。2つの引数の合計を返します。

DELIMITER //

DROP FUNCTION IF EXISTS calculate//

CREATE FUNCTION calculate(x INT, y INT) RETURNS INT
DETERMINISTIC
CONTAINS SQL
BEGIN
    RETURN x + y;
END
//

SHOW WARNINGS//

DELIMITER ;

select calculate(1,1);
+----------------+
| calculate(1,1) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

Dialect

Dialectをカスタマイズしてユーザー定義関数を登録します。
この実装はユーザー定義関数をselect句で使用する場合に必要です。where句で使う分には必要ありません。

package com.example.domain.datasource;

import org.hibernate.dialect.MySQL5Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.IntegerType;

public class CustomMySQLDialect extends MySQL5Dialect {
    public CustomMySQLDialect() {
        super();
        registerFunction("calculate",
                new StandardSQLFunction("calculate", new IntegerType()));
    }
}

application.yml

カスタマイズしたDialectを使用するようにapplication.ymlで指定します。

spring:
  jpa:
    properties:
      hibernate:
        dialect: com.example.domain.datasource.CustomMySQLDialect

JPQLでユーザー定義関数を使う

ユーザー定義関数を使用するにはFUNCTION('関数名', 引数1, 引数2, ...)と記述します。

Integer result = (Integer) entityManager.createQuery("SELECT FUNCTION('calculate', i.price, 100) FROM Item AS i WHERE i.id=:id")
        .setParameter("id", 1L)
        .getSingleResult();
System.out.println(result);
// → 300

発行されるSQL

select
    calculate(item0_.price,
    100) as col_0_0_ 
from
    item item0_ 
where
    item0_.id=?

ちなみに、サンプルコードにはありませんがItemエンティティのID=1のpriceは200です。

select price from item where id = 1;
+-------+
| price |
+-------+
|   200 |
+-------+
1 row in set (0.00 sec)