H2 でユーザー定義関数を使用する

H2 Database Engine ではユーザー定義関数(Java で実装)をサポートしており、以下のような 2通りの登録方法が用意されています。

  • Java クラスメソッドをユーザー定義関数として登録 (Referencing a Compiled Method)
  • ソースコードを指定してユーザー定義関数を登録 (Declaring Functions as Source Code)

今回は、Oracle の TO_CHAR 関数 *1 をユーザー定義関数として登録・利用する簡単なサンプルを Groovy で実装してみました。

サンプルソースは http://github.com/fits/try_samples/tree/master/blog/20120205/

概要

サンプルの実装内容としては以下の CSV から TDATA テーブルを作成して、

testdata.csv
"NO","TITLE","CREATE_DATETIME","CREATE_DATE"
1,"test1","2011-02-01 15:30:00.000","2011-02-01"
2,"test2","2011-02-02 16:30:00.000","2011-02-02"
3,"test3",,

TO_CHAR を用いた SQL で検索します。

select
    TO_CHAR(no) as no, 
    title,
    TO_CHAR(create_datetime, 'yyyy/mm/dd hh24:mi:ss') as cdatetime,
    TO_CHAR(create_date, 'yyyy/mm/dd') as cdate
from TDATA

なお、今回のサンプルでは TO_CHAR 関数の仕様の一部しか対応していない点にご注意ください。

Java クラスメソッドをユーザー定義関数として登録

この方法では、クラスメソッドを Java で実装し以下の SQL を使ってユーザー定義関数を登録します。

CREATE ALIAS <ユーザー定義関数名> FOR "<クラス名>.<メソッド名>"

メソッドのオーバーロードが使えるので、以下のサンプルでは数値と日付の文字列化をそれぞれ行う toChar メソッドを TO_CHAR として登録しています。

ただし、パラメータ数の同じオーバーロードが定義されていると登録時に例外 *2 が発生するのでご注意下さい。

また、オーバーロードを使わずに String の可変長引数を用いた単一メソッドで処理する事も可能です。(次項目のサンプル参照)

h2_userfunc_tochar.groovy
@Grapes([
    @Grab("com.h2database:h2:1.3.164"),
    @GrabConfig(systemClassLoader = true)
])
import groovy.sql.Sql

def db = Sql.newInstance("jdbc:h2:mem:", "org.h2.Driver")

db.execute("create table TDATA as select * from CSVREAD('testdata.csv')")
//以下でも可
//db.execute("create table TDATA as select * from CSVREAD('classpath:/testdata.csv')")

class Func {
    //数値の文字列化
    static String toChar(int value) {
        println "toChar : int ${value}"
        value.toString()
    }
    /* 上記は以下でも可
    static String toChar(String value) {
        println "toChar : ${value}"
        value
    }
    */

    //日付の文字列化
    static String toChar(Date value, String dateFormat) {
        if (value == null) {
            return value
        }

        //Oracle の日付フォーマットを Java 用に変換(一部のみ対応)
        dateFormat = dateFormat.toLowerCase()
                                .replaceAll("mm", "MM")
                                .replaceAll("hh24", "HH")
                                .replaceAll("mi", "mm")

        println "toChar : ${value}, ${dateFormat}"

        value.format(dateFormat)
    }
}

// Func.toChar を TO_CHAR ユーザー定義関数として登録
db.execute('CREATE ALIAS TO_CHAR FOR "Func.toChar"')

def sql = '''
    select
        TO_CHAR(no) as no, 
        title,
        TO_CHAR(create_datetime, 'yyyy/mm/dd hh24:mi:ss') as cdatetime,
        TO_CHAR(create_date, 'yyyy/mm/dd') as cdate
    from TDATA
'''

db.eachRow(sql) {r ->
    println "検索結果 : ${r.no}, ${r.title}, ${r.cdatetime}, ${r.cdate}"
}
実行結果
> groovy h2_userfunc_tochar.groovy
toChar : int 1
toChar : 2011-02-01 15:30:00.0, yyyy/MM/dd HH:mm:ss
toChar : 2011-02-01 00:00:00.0, yyyy/MM/dd
toChar : int 2
toChar : 2011-02-02 16:30:00.0, yyyy/MM/dd HH:mm:ss
toChar : 2011-02-02 00:00:00.0, yyyy/MM/dd
toChar : int 3
検索結果 : 1, test1, 2011/02/01 15:30:00, 2011/02/01
検索結果 : 2, test2, 2011/02/02 16:30:00, 2011/02/02
検索結果 : 3, test3, null, null

ソースコードを指定してユーザー定義関数を登録

こちらの方法では SQL 内に Java メソッドのソースコードを記載してユーザー定義関数を登録します。
$$ から $$; までのブロック内にソースコードを記載し、import とメソッドの区切りに @CODE を使います。

CREATE ALIAS <ユーザー定義関数名> AS $$
<import 定義>
@CODE
<メソッド定義>
$$;

この方法ではメソッドのオーバーロードをサポートしていないので、単一のメソッドで対応する事になります。

h2_userfunc_tochar2.groovy
・・・
/*
 TO_CHAR をユーザー定義関数として定義
 オーバーロードをサポートしていないので String の可変長パラメータを使う
*/
db.execute('''
CREATE ALIAS TO_CHAR AS $$
import java.text.SimpleDateFormat;
import java.util.Date;
@CODE
String toChar(String... param) {
    String value = param[0];
    String dateFormat = (param.length > 1)? param[1]: null;

    if (value == null || dateFormat == null) {
        System.out.println("toChar : " + value);
        return value;
    }

    //Oracle の日付フォーマットを Java 用に変換(一部のみ対応)
    dateFormat = dateFormat.toLowerCase()
                    .replaceAll("mm", "MM")
                    .replaceAll("hh24", "HH")
                    .replaceAll("mi", "mm");

    System.out.println("toChar : " + value + ", " + dateFormat);

    Date date = (value.length() > 10)? java.sql.Timestamp.valueOf(value): java.sql.Date.valueOf(value);

    return new SimpleDateFormat(dateFormat).format(date);
}
$$;
''')

def sql = ・・・

db.eachRow(sql) {r ->
    println "検索結果 : ${r.no}, ${r.title}, ${r.cdatetime}, ${r.cdate}"
}
実行結果
> groovy h2_userfunc_tochar2.groovy
toChar : 1
toChar : 2011-02-01 15:30:00.000, yyyy/MM/dd HH:mm:ss
toChar : 2011-02-01, yyyy/MM/dd
toChar : 2
toChar : 2011-02-02 16:30:00.000, yyyy/MM/dd HH:mm:ss
toChar : 2011-02-02, yyyy/MM/dd
toChar : 3
toChar : null
toChar : null
検索結果 : 1, test1, 2011/02/01 15:30:00, 2011/02/01
検索結果 : 2, test2, 2011/02/02 16:30:00, 2011/02/02
検索結果 : 3, test3, null, null

*1:H2 1.3 では未サポートだが 1.4 でサポートされる模様

*2:「org.h2.jdbc.JdbcSQLException: 適合するJavaメソッドは異なるパラメータ数である必要があります: ・・・」という内容の例外が発生