Db2:レコードが登録されたらスマートフォンに通知したい

19629 ワード
Line LineNotify db2 SQL データベース SQL テキストリンク

背景と状況

データベースにデータが登録されたらすぐに知りたいと思ったことはありませんか。ユースケースとしては電子カルテに検査結果が登録されたらスマートフォンに通知するなどがあります。

やりたいこと

レコードが登録されたらスマートフォンに通知したい。具体的にはレコードのインサート、アップデートが発生したら、通知プログラムを実行したい。今回はLINEを使いますがSlackなどほかのアプリにも応用可能です。

やり方

Db2の ユーザー定義関数(User Defined Functions 以下UDF) を使って、LINEにメッセージを送ります。LINEへのメッセージはLINE Notifyを使います。また、インサート、アップデートなどの イベントを検知するのにDb2のトリガー を使います。

  1. LINE Notifyのトークン取得
  2. LINE Notifyの稼働確認
  3. JavaでLINE Notifyを実装
  4. UDFの作成
  5. トリガーの定義
  6. レコードのインサートによる稼働確認

環境

  • Windows 11
  • IBM Db2 V11.5 Windows

LINE Notifyのトークン取得

こちらの記事を参考にLINE Notifyのサイトからトークンを取得します。

LINE Notifyの稼働確認

こちらの記事を参考に取得したトークンでLINEにメッセージが送付できるかを確認してください。

JavaでLINE Notifyを実装

こちらのGitHubにあるJavaのソースを参考にLineNotify.javaを用意します。

LineNotify.java
import java.io.*;
import java.net.*;
import java.util.regex.Pattern;

public class LineNotify {
	public static String callEvent(String token, String message) {
		String result = "false";
   		try {
			String strUrl = "https://notify-api.line.me/api/notify";  // 通知用エンドポイント
			message = URLEncoder.encode(message, "UTF-8");
			URL url = new URL( strUrl );
			HttpURLConnection connection = (HttpURLConnection)url.openConnection();
			connection.addRequestProperty("Authorization",  "Bearer " + token);
			connection.setRequestMethod( "POST" );
			connection.addRequestProperty( "Content-Type", "application/x-www-form-urlencoded" );
			connection.setDoOutput( true );
			String parameterString = new String("message=" + message);
			PrintWriter printWriter = new PrintWriter(connection.getOutputStream());
			printWriter.print(parameterString);
			printWriter.close();
			connection.connect();
            
			int statusCode = connection.getResponseCode();
		        if ( statusCode == 200 ) {
		        	result = "true";
		        } else {
		        	throw new Exception( "Error:(StatusCode)" + statusCode + ", " + connection.getResponseMessage() );
		        }
		        connection.disconnect();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	private String replaceProcess(String txt){
    		txt = replaceAllRegex(txt, "\\\\", "￥");		// \
		return txt;
	}
	private String replaceAllRegex(String value, String regex, String replacement) {
		if ( value == null || value.length() == 0 || regex == null || regex.length() == 0 || replacement == null )
			return "";
		return Pattern.compile(regex).matcher(value).replaceAll(replacement);
	}
}

稼働確認用にmain.javaも用意します。

Main.java
public class Main {

    public static void main(String[] args) {

        String result ;

        LineNotify objetct1 = new LineNotify();
        result = objetct1.callEvent(args[0],args[1]);

        System.out.print(result);
    }
}

Db2にインストールされているJDKを使ってコンパイルします。
D:\SQLLIB\java\jdk\bin\javac Main.java

作成したクラスの稼働確認をします。
D:\SQLLIB\java\jdk\bin\java Main 取得したトークン "JavaからLINE Notify"

jarファイルを作成します。
D:\SQLLIB\java\jdk\bin\jar -cvf LineNotify.jar LineNotify.class

jarファイルを格納するフォルダーにコピー、Db2にインストールします。
copy LineNotify.jar D:\myapp_jar
db2 connect to データベース名 user ユーザー名
db2 "CALL SQLJ.INSTALL_JAR('file:D:/myapp_jar/LineNotify.jar', 'LINENOTIFY')"

jarは以下のフォルダーに格納されます。
D:\DB2PROFS\IBM\DB2\DB2COPY1\function\jar\DB2ADMIN

格納したjarを更新、削除したい場合は以下のコマンドを使用します。

置換
db2 "CALL SQLJ.REPLACE_JAR('file:D:/myapp_jar/LineNotify.jar', 'LINENOTIFY')"
db2 "CALL SQLJ.REFRESH_CLASSES()"
除去
db2 "CALL SQLJ.REMOVE_JAR('LINENOTIFY')"

UDFの作成

UDF定義用のDDLを準備、以下のコマンドでUDFを定義します。
db2 -tvf LineNotifyUDF.ddl

LineNotifyUDF.ddl
DROP   PROCEDURE LINENOTIFY ;
DROP   FUNCTION LINENOTIFY ;

CREATE FUNCTION LINENOTIFY (IN TOKEN CHAR(100) , IN MESSAGE CHAR(200)) 
RETURNS CHAR(200)
EXTERNAL NAME 'LineNotify.callEvent' 
LANGUAGE JAVA 
PARAMETER STYLE JAVA 
SPECIFIC LINENOTIFY 
CALLED ON NULL INPUT
NO SQL
;

プロシージャーとファンクションが事前定義されていない場合はエラーになりますが問題ありません。

UDFを稼働確認します。
db2 "CALL SQLJ.REFRESH_CLASSES()"
db2 select LINENOTIFY('取得したトークン','DB2からLINE Notify') FROM SYSIBM.SYSDUMMY1

うまくいくと以下のようにLINEに通知が行きます。

UDFを確認、除去したい場合は以下のコマンドを使用します。

確認
db2 SELECT ROUTINESCHEMA, ROUTINENAME, FENCED, LANGUAGE FROM SYSCAT.ROUTINES WHERE ROUTINESCHEMA='DB2ADMIN'
除去
db2 drop FUNCTION LINENOTIFY

トリガーの定義

トリガーのレコードがインサートされたらUDFが動くトリガーを用意、以下のコマンドで定義します。テーブル名は各自の環境に合わせて変更してください。
db2 -tvf TriggerLineNotifyUDF.ddl

TriggerLineNotifyUDF.ddl
CREATE TRIGGER LINENOTIFY
　　AFTER INSERT ON TKNTIDX
　　REFERENCING NEW AS N_ROW
　　FOR EACH ROW MODE DB2SQL
　　
    select LINENOTIFY('取得したトークン', 
                       rtrim(KJ_KAN_NM) || 'さんの検査結果が出ました。')  
      from tjptkhn where pt_id = N_ROW.PT_ID
;

レコードのインサートによる稼働確認

レコードをインサートするとLINEにメッセージが送られます。

insert.sql
insert into tkntidx select 
'000001', 
'00000001', 
current date, 
current time, 
'999999', 
current date ,
'',
'',
'',
'',
'',
'',
'',
current date, 
current time, 
'',
'',
'',
'',
'',
'',
current time, 
'',
''
from sysibm.sysdummy1 ;

正しく設定ができると、以下のようなメッセージがLINEに届きます。

Javaでアプリに通知するロジックを記述すれば、UDFに定義できます。UDFに定義後、select文で簡単にプログラムが起動します。

、