ユーザ定義型(構造型)の値をJDBCで扱ってみる
せっかくSQLにユーザ定義型(構造型)を定義する機能があるので(謎)、JDBC経由で参照、更新をしてみる。
使用する環境は以下のとおり。
- DB2 Express-C v9.7.0 (と書いて FP1 に更新しなきゃならないことを思い出したらしいが、それはまた後で)
とりあえず、アクセスする側(Javaプログラム)での面倒から逃げるために、受け渡しは文字列で行い、それをユーザ定義型の値に変換してテーブルに格納する。
まず、ユーザ定義型の作成とその値を保持するテーブルの作成。(文区切り文字はエクスクラメーションマーク('!'))
CREATE TYPE TYPE_HOGE AS ( COL_A INTEGER, COL_B VARCHAR(64), COL_C TIMESTAMP ) MODE DB2SQL ! CREATE TABLE TABLE_HOGE(DATA TYPE_HOGE) !
ユーザ定義型の値はどう返すのか定義されていないので、SELECT文を発行すると、エラーになる。
SQL20015N トランスフォーム・グループ "DB2_PROGRAM" は、データ・タイプ "DB2ADMIN.TYPE_HOGE" に定義されていません。 SQLSTATE=42741
トランスフォーム・グループの定義はこんな感じ。
-- -- ユーザ定義型の値から別の型の値へ変換する関数。 -- CREATE FUNCTION FUNC_FROM (VAL TYPE_HOGE) RETURNS VARCHAR(128) LANGUAGE SQL DETERMINISTIC CONTAINS SQL BEGIN ATOMIC RETURN '{COL_A:<' || RTRIM(CHAR(VAL..COL_A)) || '>, COL_B:<' || VAL..COL_B || '>, COL_C:<' || CHAR(VAL..COL_C) || '>}'; END ! -- -- 別の型の値からユーザ定義型の値へ変換する関数。 -- CREATE FUNCTION FUNC_TO (VAL VARCHAR(128)) RETURNS TYPE_HOGE LANGUAGE SQL DETERMINISTIC CONTAINS SQL BEGIN ATOMIC DECLARE P1B INTEGER; DECLARE P1E INTEGER; DECLARE P2B INTEGER; DECLARE P2E INTEGER; DECLARE P3B INTEGER; DECLARE P3E INTEGER; SET P1B = POSITION('COL_A:<' IN VAL USING CODEUNITS32) + 7; SET P2B = POSITION('COL_B:<' IN VAL USING CODEUNITS32) + 7; SET P3B = POSITION('COL_C:<' IN VAL USING CODEUNITS32) + 7; SET P1E = POSITION('>' IN SUBSTRING(VAL FROM P1B FOR CHARACTER_LENGTH(VAL USING CODEUNITS32) USING CODEUNITS32) USING CODEUNITS32); SET P2E = POSITION('>' IN SUBSTRING(VAL FROM P2B FOR CHARACTER_LENGTH(VAL USING CODEUNITS32) USING CODEUNITS32) USING CODEUNITS32); SET P3E = POSITION('>' IN SUBSTRING(VAL FROM P3B FOR CHARACTER_LENGTH(VAL USING CODEUNITS32) USING CODEUNITS32) USING CODEUNITS32); RETURN ( TYPE_HOGE()..COL_A(INTEGER( SUBSTRING(VAL FROM P1B FOR P1E - 1 USING CODEUNITS32) ))..COL_B( SUBSTRING(VAL FROM P2B FOR P2E - 1 USING CODEUNITS32) )..COL_C(TIMESTAMP( SUBSTRING(VAL FROM P3B FOR P3E - 1 USING CODEUNITS32) )) ); END ! -- -- "DB2_PROGRAM"という名前のトランスフォーム・グループに -- ユーザ定義型に対する変換関数を登録。 -- CREATE TRANSFORM FOR TYPE_HOGE DB2_PROGRAM ( FROM SQL WITH FUNCTION FUNC_FROM, TO SQL WITH FUNCTION FUNC_TO ) !
実際にユーザ定義型の値を返還する際に使用されるトランスフォーム・グループは「CURRENT DEFAULT TRANSFORM GROUP」というレジスタに格納されており、値がセットされていれば指定された名前のトランスフォーム・グループが使用され、値がセットされていなければ"DB2_PROGRAM"という名前のトランスフォーム・グループが使用される。
余談は置いておくとして、これでSELECT文を発行すると、ユーザ定義型の値が文字列型の値として得られる。
次はユーザ定義型の列を持つテーブルの操作を行うJavaプログラム。
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; public class Main { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.ibm.db2.jcc.DB2Driver"); Connection conn = DriverManager.getConnection("jdbc:db2://localhost:50000/sandbox", "db2admin", "db2admin"); try { test(conn); } finally { conn.close(); } } private static void test(Connection conn) throws SQLException { // transform関数を使用して挿入 { String sql = "INSERT INTO TABLE_HOGE(DATA) VALUES(?)"; PreparedStatement pstmt = conn.prepareStatement(sql); try { pstmt.setString(1, "{COL_A:<1234567890>," + " COL_B:<Hello World>," + " COL_C:<" + new Timestamp(System.currentTimeMillis()) + ">}"); pstmt.executeUpdate(); } finally { pstmt.close(); } } // 直接 TYPE_HOGE 型の値を生成して挿入 { String sql = "INSERT INTO TABLE_HOGE(DATA)" + " VALUES(TYPE_HOGE()" + "..COL_A(CAST(? AS INTEGER))" + "..COL_B(CAST(? AS VARCHAR(64)))" + "..COL_C(CAST(? AS TIMESTAMP)))"; PreparedStatement pstmt = conn.prepareStatement(sql); try { pstmt.setInt(1, 987654321); pstmt.setString(2, "Hello World!!"); pstmt.setTimestamp(3, new Timestamp(System.currentTimeMillis())); pstmt.executeUpdate(); } finally { pstmt.close(); } } // TYPE_HOGE 型の各属性を明示して値を取得 { String sql = "SELECT DATA..COL_A, DATA..COL_B, DATA..COL_C FROM TABLE_HOGE"; PreparedStatement pstmt = conn.prepareStatement(sql); try { ResultSet resultSet = pstmt.executeQuery(); while (resultSet.next()) { int val1 = resultSet.getInt(1); String val2 = resultSet.getString(2); Timestamp val3 = resultSet.getTimestamp(3); System.out.printf("%2d: [%s][%s][%s]\n", 1, val1, val2, val3); } } finally { pstmt.close(); } } // transform関数を使用して値を取得 { String sql = "SELECT DATA FROM TABLE_HOGE"; PreparedStatement pstmt = conn.prepareStatement(sql); try { ResultSet resultSet = pstmt.executeQuery(); while (resultSet.next()) { Object val = resultSet.getObject(1); System.out.printf("%2d: %s\n", 2, val); } } finally { pstmt.close(); } } } }
プログラム中にもあるように、トランスフォーム関数を使用しなくても操作はできるが、各メソッドを呼び出す際にいちいちキャストしないといけないのが面倒。まぁ、それを言ったら、文字列表現を生成して渡すのも面倒なのだが。
これを実行すると次のような感じ。
1: [1234567890][Hello World][2009-12-12 00:45:22.027]
1: [987654321][Hello World!!][2009-12-12 00:45:22.518]
2: {COL_A:<1234567890>, COL_B:<Hello World>, COL_C:<2009-12-12-00.45.22.027000>}
2: {COL_A:<987654321>, COL_B:<Hello World!!>, COL_C:<2009-12-12-00.45.22.518000>}
