Spring JPAで、MYSQL依存関数(GROUP_CONCAT)を呼び出す

Posted on by さつま芋ふじた

今回はSpring Boot + Spring Data JPA環境で、MYSQL依存関数(今回はGROUP_CONCAT)を動かす方法を記載します。

Spring JPAでDBに依存した処理を行う場合

データベース操作をSpring Data JPAで行う場合、JPQLを使用してDBとやりとりすることになりますが、その場合、スルッとは、MYSQL依存関数は使用できません。いくつか方法があります

  • 依存するのは仕方ないと諦めてJPQLではなくSQLで書く
  • function()と記述して呼び出す(GROUP_CONCATは複雑なので使えません)
  • 方言に追加する

色々あります。nativeなSQLで書く場合は、データ⇔オブジェクト間のフォローが必要になるようです。基本の方式から外れる以上、自由ですが、便利さも享受できなくなります。

参考

今回は、方言を設定して実現したいと思います。以下のページで回答されていた内容をベースにしています。他にも読んだページがあったと思うのですが忘れてしまいました。（Androidのときもそうでしたが、有用な記事は大体、英語で書かれています。日本語の記事は、起動までのチュートリアル的な記事か、古くて参考にならない記事ばかりです。探し方が悪いのでしょうか…。それとも今後、英語が読めないと探し物ができない時代が来るのでしょうか）

参考

hibernateに方言を足す

方言というのは、hibernateのDialect(方言)になります。hibernateは(おそらく)JPAとDBのやりとりをしてくれるライブラリです。JPAの抽象化を現実にしてくれているのでしょうか？判りませんが、ともかく橋渡しをしています。DialectがDBの違いを受け持つ緩衝材です。ここに依存関数を設定してやるのです。

MYSQLのGROUP_CONCAT関数について

そもそもGROUP_CONCAT関数はご存知でしょうか。私はつい最近知りました。これが便利な関数で、おおざっぱに説明すると、GROUP BYの結果をグループ毎に１レコードに収めることができ、その際、並びや要素間の接続文字も指定できるのです。これには感動しました。

参考

流れ

  1. GROUP_CONCATが呼び出されたときのSQL文字列を返す処理を作成
  2. もともと使っていたMysqlのDialectを継承したDialectを用意する
  3. 用意したDialectに用意した関数を追加する
  4. 用意したDialectを使用するため設定する

実装

GROUP_CONCATが呼び出されたときのSQL文字列を返す処理を作成

import java.util.List;

import org.hibernate.QueryException;
import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.engine.spi.Mapping;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;

import lombok.val;

public class GroupConcatFunction implements SQLFunction {

    @Override
    public boolean hasArguments() {
        return true;
    }
    
    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }
    
    @Override
    public Type getReturnType(Type firstArgumentType, Mapping mapping)
            throws QueryException {
        return StandardBasicTypes.STRING;
    }
    
    @Override
    public String render(Type firstArgumentType, List arguments,
            SessionFactoryImplementor factory) throws QueryException {
        val sb = new StringBuilder();
        
        // 引数が指定されていない場合、エラー
        if (arguments.size() < 1) {
            throw new QueryException(new IllegalArgumentException(
                    "group_concat shoudl have one arg"));
        }
        
        sb.append("group_concat(" + arguments.get(0));

        // ORDER BY 対応
        if (arguments.size() >= 3) {
            sb.append(" ORDER BY " + arguments.get(1)
                + " " + ((String)arguments.get(2)).substring(1, ((String)arguments.get(2)).length()-1));
        }
        
        // 2016/05/18 現状ORDER BY 複数条件に対応していない
        
        // SEPARATOR 対応
        if (arguments.size() >= 4) {
            sb.append(" SEPARATOR " + arguments.get(3));
        }
        
        sb.append(")");
        
        return sb.toString();
    }
}

もともと使っていたMysqlのDialectを継承したDialectを用意する

import org.hibernate.dialect.MySQL5Dialect;

public class CustomMySQL5Dialect extends MySQL5Dialect {
	public CustomMySQL5Dialect() {
        super();
        // GroupConcat関数を追加する
        registerFunction("group_concat", new GroupConcatFunction());
    }
}

用意したDialectを使用するため設定する

spring.jpa.database-platform=jp.regrex.rista.dialect.CustomMySQL5Dialect

今回のソースの問題点について

ソース自体雑なのもそうなのですが、ORDER BYの複数条件に対応できていません。今回の引数２～(最後-1)まではORDER BY指定としてGroupConcatFunctionを修正してあげればよいと思うのですが、現時点では今回の簡易なつくりで事足りたのでこのようになっています。

終わり

以上です。そう頻繁に必要になることはないと思いますが、何かの折に役に立てたなら幸いです。