H2 でユーザー定義関数を使用する
H2 Database Engine ではユーザー定義関数（Java で実装）をサポートしており、以下のような 2通りの登録方法が用意されています。
- Java クラスメソッドをユーザー定義関数として登録 （Referencing a Compiled Method）
- ソースコードを指定してユーザー定義関数を登録 （Declaring Functions as Source Code）
今回は、Oracle の TO_CHAR 関数 *1 をユーザー定義関数として登録・利用する簡単なサンプルを Groovy で実装してみました。
- H2 Database Engine 1.3.164
- Groovy 1.8.5 (java 1.7.0_02 64bit)
サンプルソースは 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
