Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

概要

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

参考

サンプルコード

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

サンプルの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)
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした