1. rubytomato@github

    Posted

    rubytomato@github
Changes in title
+JPQLからデータベースのユーザー定義関数を利用する
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,122 @@
+# 概要
+
+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つの引数の合計を返します。
+
+```sql
+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 ;
+```
+
+```sql
+select calculate(1,1);
++----------------+
+| calculate(1,1) |
++----------------+
+| 2 |
++----------------+
+1 row in set (0.00 sec)
+```
+
+## Dialect
+
+Dialectをカスタマイズしてユーザー定義関数を登録します。
+この実装はユーザー定義関数をselect句で使用する場合に必要です。where句で使う分には必要ありません。
+
+```java
+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で指定します。
+
+```yaml
+spring:
+ jpa:
+ properties:
+ hibernate:
+ dialect: com.example.domain.datasource.CustomMySQLDialect
+```
+
+## JPQLでユーザー定義関数を使う
+
+ユーザー定義関数を使用するには`FUNCTION('関数名', 引数1, 引数2, ...)`と記述します。
+
+```java
+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
+
+```sql
+select
+ calculate(item0_.price,
+ 100) as col_0_0_
+from
+ item item0_
+where
+ item0_.id=?
+```
+
+ちなみに、サンプルコードにはありませんがItemエンティティのID=1のpriceは200です。
+
+```sql
+select price from item where id = 1;
++-------+
+| price |
++-------+
+| 200 |
++-------+
+1 row in set (0.00 sec)
+```