OracleのトリガでJavaの処理を起動する

OracleのトリガでJavaの処理を起動する

JavaとPL/SQLを組み合わせて開発していると、「OracleのトリガでJavaの処理を起動させたいな」という局面に出会うことがあります。単にJavaで書いたプログラムを動かしたいだけならば、OracleのトリガそのものをJavaで書くということもできるし、そもそもPL/SQL で大概のことが済んでしまいます。そうではなく、特定の実行空間(サーブレット実行環境)などで、スタティック変数やスレッドなどにアクセスしたいというような場合が、この文書のテーマです。私の場合、マスタテーブルをスタティック変数の配列に読み込んで利用しているのですが、そのマスタテーブルを SQL*PlusやAccessで変更した場合に配列を再読み込みさせたい、という問題にぶつかりました。
このような場合、一定間隔でテーブルをチェックする、ということになりがちですが、そのためのシステム負荷は馬鹿になりません。なんとかトリガを上手く使ったスマートな方法を取りたいところです。
DBMS_ALERTパッケージ

いろいろ調べたところ、Oracleの標準PL/SQLパッケージ "DBMS_ALERT" をトリガと組み合わせて使う方法が定番だろうということが分かりました。このDBMS_ALERTパッケージは、メッセージを非同期に送受信する機能をまとめたパッケージで、これによってアプリケーションの通信が可能になります。"DBMS_ALERT"は実際にはテーブルアクセスを行いませんので、データベースへの負荷はほぼ気にする必要はありません。もちろん、ポーリングを行なうわけではないのでシステム負荷もわずかです。

Javaとトリガの連携での具体的な利用法は、実行空間の中でメッセージの受信とそれに伴う処理を行なうスレッドを走らせておき、表の更新時にメッセージの送信を行うようなトリガを作成する、ということになります。
サンプルコード

ここでは、マスタ表 "PLACE"と"TEAM"の変更を検出し、それに対応した処理を行なうような例を取り上げます。

まず、DBMS_ALERTパッケージが利用できるように、SYSユーザでログインして実行DBユーザにDBMS_ALERTのEXECUTE権限を与えておく必要があります。
次に、"PLACE"と"TEAM"が変更になった際にDBMS_ALERTのアラートを送信するようなトリガを作成します。そのためのSQL文は以下のようになります。

CREATE OR REPLACE TRIGGER T_CHGTEAM AFTER INSERT OR UPDATE OR DELETE ON TEAM
    BEGIN
        DBMS_ALERT.SIGNAL('TEAM','UPDATED!');
    END;
/
CREATE OR REPLACE TRIGGER T_CHGPLACE AFTER INSERT OR UPDATE OR DELETE ON PLACE
    BEGIN
        DBMS_ALERT.SIGNAL('PLACE','UPDATED!');
    END;
/

DBMS_ALERT.SIGNALの一つ目の引数がアラートの名前、二つ目の引数がメッセージの内容になります（ここではメッセージの内容は利用しません)。

続いて、このALERTを受け取るJavaプログラムを作成します。スレッド廻りの部分を書くと長くなりますので、ここではアラートの受け取り部分だけを記述します。プログラム全体はダウンロードして眺めてください(TableWatchDog.java)。

/**
* マスタテーブルの開始を監視する
*/
public void run() {
    String dicttype;
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        Connection con = DriverManager.getConnection(url, "test", "test");

         // DBMS_ALERT.REGISTER
         //   - 指定した名前のアラートをこのセッションで受け取ることを定義する
        String storedproc = "{call DBMS_ALERT.REGISTER(?)}";
        CallableStatement cstmt1 = con.prepareCall(storedproc);
        cstmt1.setString(1, "TEAM");
        cstmt1.executeUpdate();
        cstmt1.setString(1, "PLACE");
        cstmt1.executeUpdate();

         // DBMS_ALERT.WAITANY
         //   - メッセージの受信待ち
         //     ここでは、一つの待ちプロセスで複数の種類のメッセージを受け取り、
         //     メッセージの内容で処理を分ける。
        storedproc = "{call DBMS_ALERT.WAITANY(?,?,?,?)}";
        cstmt2 = con.prepareCall(storedproc);
         // 第1パラメータ(OUT) - アラートの名前
        cstmt2.registerOutParameter(1, Types.VARCHAR);
         // 第2パラメータ(OUT) - メッセージの内容
        cstmt2.registerOutParameter(2, Types.VARCHAR);
         // 第3パラメータ(OUT) - 実行結果。0ならアラート受信。1ならタイムアウト。
        cstmt2.registerOutParameter(3, Types.INTEGER);
         // 第4パラメータ(IN)  - タイムアウト時間(秒数)
        cstmt2.setInt(4, 300);
        System.err.println("WAIT START!");

         // stopSyncが実行されると completeフラグがセットされる。
         // それまでは無限ループとなる。
        while (complete == false) {
            try {
                 // アラートはトランザクションがコミットした時点で送受信される
                con.commit();
                 // 上で指定したDBMS_ALERT.WAITANYプロシジャの実行
                cstmt2.executeUpdate();
                if (cstmt2.getInt(3) == 1) {
                     //タイムアウトによって終了した場合の処理
                    System.err.println("TABLES NO UPDATED!");
                } else {
                     //アラート受信で終了した場合、アラートの名前で処理を分ける
                    dicttype = cstmt2.getString(1);
                    if (dicttype.equals("TEAM") == true) {
                         // チーム配列の初期化処理がここに入る
                        System.err.println("TEAM TABLE UPDATED!");
                    }
                    if (dicttype.equals("PLACE") == true) {
                         // 地名配列の初期化処理がここに入る
                        System.err.println("PLACE TABLE UPDATED!");
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
                break;
            }
        }
       
         // DBMS_ALERT.REGISTER
         //   - 指定した名前のアラート受信定義をこのセッションから削除する
        storedproc = "{call DBMS_ALERT.REMOVE(?)}";
        CallableStatement cstmt3 = con.prepareCall(storedproc);
        cstmt3.setString(1, "TEAM");
        cstmt3.executeUpdate();
        cstmt3.setString(1, "PLACE");
        cstmt3.executeUpdate();

        con.commit();
        con.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

なお、待機している間、つまりほぼ動作中ずっとコネクションを一つ掴んだままになります。サーブレットなどでスレッドプールを使える場合でも、このスレッド用にはプールのコネクションを割り当てないほうが良いでしょう。