HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

java.sql.Array を使ってみる

Java DB2

DB2 v9.5 から ARRAY 型がサポートされるようになった。ただし、自分が認識している範囲では次の制限がある。

  • プロシジャ(Procedure)のパラメータでのみ指定できる。
    • (DB2 v9.7以降)関数(Function)のパラメータか戻り値にも指定できる。(とオンラインマニュアルには書いてある)
  • "CREATE TYPE"文であらかじめ配列型を生成しなければならない。

2つ目の制限はどういうことかというと、

paramName VARCHAR(100) ARRAY[64]

とはできず、

CREATE TYPE VARCHAR_100_ARRAY AS VARCHAR(100) ARRAY[64]

などとしてから

paramName VARCHAR_100_ARRAY

とする必要があるということ。
しかし、これは java.sql.Array を使用するチャンス(何)なので、ARRAY 型に関する機能を(見つけた限り)一通り試してみる。

出力として ARRAY 値を受け取る

ARRAY 型とプロシジャの定義は次のとおり。(プロシジャ定義にセミコロン(';')が含まれるので、文セパレータをエクスクラメーションマーク('!')にしている。)

CREATE TYPE "TYPE_VARCHAR_100_ARRAY_64" AS VARCHAR(100) ARRAY[64]
!
CREATE PROCEDURE "PROCEDURE_HOGE_01"
    (IN INPUT VARCHAR(100),
     OUT OUTPUT_ARRAY "TYPE_VARCHAR_100_ARRAY_64")
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    BEGIN
        SET OUTPUT_ARRAY[1] = 'Good morning, ' || INPUT;
        SET OUTPUT_ARRAY[2] = 'Good afternoon, ' || INPUT;
        SET OUTPUT_ARRAY[3] = 'Good evening, ' || INPUT;
    END
!

で、これを使用する Java プログラムは次のとおり。

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class Main1 {

    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 {
            CallableStatement cstmt = conn.prepareCall("{CALL PROCEDURE_HOGE_01(?, ?)}");
            try {
                cstmt.setString(1, "hhelibex");
                cstmt.registerOutParameter(2, Types.ARRAY);
                cstmt.execute();
                Array array = cstmt.getArray(2);
                String[] values = (String[]) array.getArray();
                System.out.println("values:");
                for (int i = 0; i < values.length; ++i) {
                    System.out.printf("    [%2d]: %s\n", i, values[i]);
                }
            } finally {
                cstmt.close();
            }
        } finally {
            conn.close();
        }
    }

}

で、これを実行すると、次のような出力を得る。

values:
    [ 0]: Good morning, hhelibex
    [ 1]: Good afternoon, hhelibex
    [ 2]: Good evening, hhelibex

ちなみに、関数も試そうと思って DB2 v9.7 でも試したが、プロシジャと基本的に同じ内容の定義を書いたところ怒られた

SQL0104N  "HAR_100_ARRAY_64";  " に続いて予期しないトークン "SET"
が見つかりました。予期されたトークンに "<if>" が含まれている可能性があります。
LINE NUMBER=10.  SQLSTATE=42601

ので、エラーになるところを削っていくと、最終的に次のようになり、

CREATE FUNCTION "FUNCTION_HOGE_01"
    (INPUT VARCHAR(100))
    RETURNS "TYPE_VARCHAR_100_ARRAY_64"
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    BEGIN ATOMIC
--        DECLARE OUTPUT_ARRAY "TYPE_VARCHAR_100_ARRAY_64";

--        SET OUTPUT_ARRAY[1] = 'Good morning, ' || INPUT;
--        SET OUTPUT_ARRAY[2] = 'Good afternoon, ' || INPUT;
--        SET OUTPUT_ARRAY[3] = 'Good evening, ' || INPUT;

--        RETURN OUTPUT_ARRAY;
        RETURN NULL;
    END
!

次のように怒られる。

SQL20441N  データ・タイプ "ARRAY"
は、使用されているコンテキストではサポートされません。  LINE NUMBER=17.
SQLSTATE=428H2

‥えっ！？

入力として ARRAY 値を渡す

ARRAY 型とプロシジャの定義は次のとおり。(プロシジャ定義にセミコロン(';')が含まれるので、文セパレータをエクスクラメーションマーク('!')にしている。)

CREATE TYPE "TYPE_VARCHAR_100_ARRAY_64" AS VARCHAR(100) ARRAY[64]
!
CREATE PROCEDURE "PROCEDURE_HOGE_02"
    (IN INPUT_ARRAY "TYPE_VARCHAR_100_ARRAY_64",
     OUT OUTPUT VARCHAR(6464))
    LANGUAGE SQL
    DETERMINISTIC
    READS SQL DATA
    BEGIN
        DECLARE i INTEGER;
        SET i = 1;
        SET OUTPUT = '';
        WHILE i <= CARDINALITY(INPUT_ARRAY) DO
            IF i > 1 THEN
                SET OUTPUT = OUTPUT || ',';
            END IF;
            SET OUTPUT = OUTPUT || INPUT_ARRAY[i];
            SET i = i + 1;
        END WHILE;
    END
!

で、これを使用する Java プログラムは次のとおり。

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Map;

public class Main2 {

    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 {
            {
                CallableStatement cstmt = conn.prepareCall("{CALL PROCEDURE_HOGE_02(?, ?)}");
                try {
                    Array array;
                    array = new TestArray();
                    cstmt.setArray(1, array);
                    cstmt.registerOutParameter(2, Types.VARCHAR);
                    cstmt.execute();
                    String value = cstmt.getString(2);
                    System.out.println("value: " + value);
                } finally {
                    cstmt.close();
                }
            }
        } finally {
            conn.close();
        }
    }

    private static class TestArray implements Array {

        public Object getArray() throws SQLException {
            // TODO 自動生成されたメソッド・スタブ
System.out.println("### getArray()");
            return new String[] { "C", "C++", "Java", "Ruby", "Phthon", "Go", };
        }

        public Object getArray(Map<String, Class<?>> map)
                throws SQLException {
            // TODO 自動生成されたメソッド・スタブ
System.out.println("### getArray(Map<String, Class<?>> map)");
            return null;
        }

        public Object getArray(long index, int count)
                throws SQLException {
            // TODO 自動生成されたメソッド・スタブ
System.out.println("### getArray(long index, int count)");
            return null;
        }

        public Object getArray(long index, int count,
                Map<String, Class<?>> map) throws SQLException {
            // TODO 自動生成されたメソッド・スタブ
System.out.println("### getArray(long index, int count, Map<String, Class<?>> map)");
            return null;
        }

        public int getBaseType() throws SQLException {
            return Types.VARCHAR;
        }

        public String getBaseTypeName() throws SQLException {
            return "VARCHAR";
        }

        public ResultSet getResultSet() throws SQLException {
            // TODO 自動生成されたメソッド・スタブ
System.out.println("### getResultSet()");
            return null;
        }

        public ResultSet getResultSet(Map<String, Class<?>> map)
                throws SQLException {
            // TODO 自動生成されたメソッド・スタブ
System.out.println("### getResultSet(Map<String, Class<?>> map)");
            return null;
        }

        public ResultSet getResultSet(long index, int count)
                throws SQLException {
            // TODO 自動生成されたメソッド・スタブ
System.out.println("### getResultSet(long index, int count)");
            return null;
        }

        public ResultSet getResultSet(long index, int count,
                Map<String, Class<?>> map) throws SQLException {
            // TODO 自動生成されたメソッド・スタブ
System.out.println("### getResultSet(long index, int count, Map<String, Class<?>> map)");
            return null;
        }
    }

}

TestArray クラスは java.sql.Array インタフェースの実装だが、かなり適当(汗)。「とりあえず動けばいいや」的な ^^;;
で、これを実行すると、次のような出力を得る。

### getArray()
value: C,C++,Java,Ruby,Phthon,Go

ちなみに、関数も試そうと思って DB2 v9.7 でも試したが、プロシジャと基本的に同じ内容の定義を書いたところ怒られた

SQL0440N  互換性のある引数を持つ、タイプ "FUNCTION" の "CARDINALITY"
という名前の許可されたルーチンが見つかりませんでした。  LINE NUMBER=13.
SQLSTATE=42884

ので、エラーになるところを削っていくと、最終的に次のようになり、

CREATE FUNCTION "FUNCTION_HOGE_02"
    (INPUT_ARRAY "TYPE_VARCHAR_100_ARRAY_64")
    RETURNS VARCHAR(6464)
    LANGUAGE SQL
    DETERMINISTIC
    READS SQL DATA
    BEGIN ATOMIC
        DECLARE OUTPUT VARCHAR(6464);
        DECLARE i INTEGER;

        SET i = 1;
        SET OUTPUT = '';
--        WHILE i <= CARDINALITY(INPUT_ARRAY) DO
--            IF i > 1 THEN
--                SET OUTPUT = OUTPUT || ',';
--            END IF;
--            SET OUTPUT = OUTPUT || INPUT_ARRAY[i];
--            SET i = i + 1;
--        END WHILE;

        RETURN OUTPUT;
    END
!

次のように怒られる。

SQL20441N  データ・タイプ "ARRAY"
は、使用されているコンテキストではサポートされません。  LINE NUMBER=23.
SQLSTATE=428H2

‥えっ！？(しつこい)

表への問い合わせ結果から ARRAY 値を生成する

使用する表、ARRAY 型とプロシジャの定義は次のとおり。(プロシジャ定義にセミコロン(';')が含まれるので、文セパレータをエクスクラメーションマーク('!')にしている。)

CREATE TABLE "TABLE_HOGE"
    (KEY VARCHAR(100) NOT NULL, VALUE VARCHAR(100) NOT NULL)
!
INSERT INTO "TABLE_HOGE"
    VALUES
        ('key1', 'value1-3'),
        ('key1', 'value1-1'),
        ('key1', 'value1-2'),
        ('key2', 'value2-2'),
        ('key2', 'value2-1'),
        ('key3', 'value3-1')
!
CREATE TYPE "TYPE_VARCHAR_100_ARRAY_64" AS VARCHAR(100) ARRAY[64]
!
CREATE PROCEDURE "PROCEDURE_HOGE_03"
    (IN INPUT VARCHAR(100),
     OUT OUTPUT_ARRAY "TYPE_VARCHAR_100_ARRAY_64")
    LANGUAGE SQL
    DETERMINISTIC
    READS SQL DATA
    BEGIN
        SET OUTPUT_ARRAY = (
            SELECT ARRAY_AGG(VALUE ORDER BY KEY ASC, VALUE ASC)
                FROM "TABLE_HOGE"
                WHERE KEY = INPUT
        );
    END
!

で、これを使用する Java プログラムは次のとおり。

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class Main3 {

    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 {
            CallableStatement cstmt = conn.prepareCall("{CALL PROCEDURE_HOGE_03(?, ?)}");
            try {
                cstmt.setString(1, "key1");
                cstmt.registerOutParameter(2, Types.ARRAY);
                cstmt.execute();
                Array array = cstmt.getArray(2);
                String[] values = (String[]) array.getArray();
                System.out.println("values:");
                for (int i = 0; i < values.length; ++i) {
                    System.out.printf("    [%2d]: %s\n", i, values[i]);
                }
            } finally {
                cstmt.close();
            }
        } finally {
            conn.close();
        }
    }

}

で、これを実行すると、次のような出力を得る。

values:
    [ 0]: value1-1
    [ 1]: value1-2
    [ 2]: value1-3

ちなみに、関数も(以下略)