Node-REDからJDBCドライバを用いてデータベースに接続する方法

　日立製作所OSSソリューションセンタの横井一仁です。今回は、Node-REDのfunctionノードからJDBCドライバを用いて、データベースに接続する手順をご紹介します。

## 背景
　データベースを扱った事がある方であればご存知のように、JDBCはJavaからデータベースへ接続するための仕組みです。そのため、Node.js上で動くNode-REDから、Javaを用いてデータベースに接続することは少しトリッキーに感じるかもしれません。しかし、データベース製品やデータベースのOSS開発コミュニティから、データベースと接続するNode.jsのライブラリが提供されていない場合は、この方法を用いてデータベースに接続できるようになります。歴史のあるデータベースであればJDBCドライバを提供しているため、ほぼ全てのデータベースに適用できる方法です。本記事では、例としてPostgreSQLデータベースを使用します。

## PostgreSQLの利用環境構築
　今回用いたUbuntu環境には、Node-REDやNode.jsの他に、OpenJDKとDocker環境をインストールしてあります。まずカレントディレクトリにJDBCドライバの入ったJARファイルをダウンロードします。Node-REDのプロセスから呼び出せる様に、JARファイルはカレントディレクトリに格納しました。

```
wget https://jdbc.postgresql.org/download/postgresql-42.3.1.jar --no-check-certificate
```

PostgreSQL環境は、以下のDockerコマンドを用いて構築しました。

```
docker run --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=password -d postgres
```

その後、Node-REDを起動し、フローエディタを開きます。

```
node-red
```

## functionノードでデータベースに接続するフロー作成
　それでは、早速フローを開発してゆきましょう。まずパレットからinjectノード、functionノード、debugノードをドラッグ＆ドロップし、ワイヤーでつないで以下の様なフローを作成します。

![undeployedflow2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/9ddee4b0-60e2-b995-a27c-7043b727ac20.png)

functionノードのプロパティ設定には、「設定」、「初期化処理」、「コード」、「終了処理」の4つのタブが存在します。各タブ上で以下の設定やコードを記載します。

### (1) 設定タブ
　外部npmモジュールとして、javaモジュールとjdbcモジュールを利用するため、以下の様にモジュール一覧の中に追加します。

![config2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/6bba9bd2-dfa4-8752-3f39-148059c15252.png)

　javaモジュールは、Node.js上でJavaのコードを記載できるようにするためのnpmモジュールです。今回はクラスパスを設定するだけのために用いました。またjdbcモジュールは、その名前の通り、JDBCドライバを用いてNode.jsからデータベースに接続するためのnpmモジュールです。

### (2) 初期化処理タブ
　初期化処理のタブのコードは、デプロイボタンを押してフローが開始された時に実行されます。ここにデータベースと接続するための初期化処理を記載します。

![init2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/e991be9d-c0b0-0c24-1731-f6a80de3c3fb.png)

**記載するコード**

```JavaScript
cn = null;

// JDBCドライバの入ったJARファイルをクラスパスに追加
if (!java.classpath.includes('postgresql-42.3.1.jar')) {
    java.classpath.push.apply(java.classpath, ['postgresql-42.3.1.jar']);
}

// データベースの接続設定
var db = new jdbc({
    url: 'jdbc:postgresql://127.0.0.1/', // 接続先
    user: 'postgres', // ユーザ名
    password: 'password' // パスワード
});

// データベース接続処理
db.reserve(function (err, conn) {
    if (err) {
        // エラー内容をデバック出力
        node.error(err);
        // エラー時はステータスに「disconnected」と表示
        node.status({fill: 'red', shape: 'ring', text: 'disconnected' });
    } else {
        cn = conn;
        db.initialize(function (err) {
            // 接続時はステータスに「connected」と表示
            node.status({fill: 'green', shape: 'dot', text: 'connected' });
        });
    }
});
```
　4、5行名「postgresql-42.3.1.jar」は、前の手順でカレントディレクトリに配置したPostgreSQLのJDBCドライバが入ったJARファイルです。ます。もし、OracleデータベースなどPostgreSQL以外のデータベースと接続したい場合は、データベース用のJDBCドライバが入ったJARファイルを用意し、そのファイルパスを指定してください。

　また、10～12行目には接続先のURL「jdbc:postgresql://127.0.0.1/」、ユーザ名「postgres」、パスワード「password」を指定しています。こちらも他のデータベースを用いる場合は、そのデータベースに合った設定に書き換えてください。

### (3) コードタブ
　「コード」タブ内には、SQL文を実行するコードを記載します。

![code2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/0c1ff872-bde4-464a-fe8d-eccb2ff93d3b.png)

**記載するコード**

```JavaScript
if (cn) {
    // データベース問合せ中は、ステータスに「requesting」と表示
    node.status({fill: 'blue', shape: 'dot', text: 'requesting' });
    cn.conn.createStatement(function (err, statement) {
        if (typeof msg.payload === 'string') {
            if (msg.payload.match(/^select/i)) { // SELECT文の場合
                // データベースの問合せ実行
                statement.executeQuery(msg.payload, function (err, resultset) {
                    if (err) {
                        // エラー内容をデバック出力
                        node.error(err);
                        // エラー時はステータスに「error」と表示
                        node.status({fill: 'red', shape: 'ring', text: 'error' });
                    } else {
                        resultset.toObjArray(function (err, result) {
                            msg.payload = result;
                            // 次のノードに問合せ結果を渡す
                            node.send(msg);
                            // 問合せ後、ステータス表示をクリア
                            node.status({});
                        });
                    }
                });
            } else { // SELECT文以外の場合
                // データベースの問合せ実行
                statement.executeUpdate(msg.payload, function (err, result) {
                    if (err) {
                        // エラー内容をデバック出力
                        node.error(err);
                        // エラー時はステータスに「error」と表示
                        node.status({fill: 'red', shape: 'ring', text: 'error' });
                    } else {
                        msg.payload = result;
                        // 次のノードに問合せ結果を渡す
                        node.send(msg);
                        // 問合せ後、ステータス表示をクリア
                        node.status({});
                    }
                });
            }
        } else {
            // エラー内容をデバック出力
            node.error(err);
            // エラー時はステータスに「error」と表示
            node.status({fill: 'red', shape: 'ring', text: 'error' });
        }
    });
}
```

　前のノードから受け取ったメッセージ中の変数msg.payload内のSQL文を実行し、結果を次のノードに渡す処理となっています。

### (4) 終了処理タブ
　終了処理は、フローの再デプロイやNode-REDのプロセスの終了の前に、実行されるコードです。ここに、データベースとの切断処理を記載します。

![finalize2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/8379db98-1ea6-8c23-c930-e6b6b9935430.png)

**記載するコード**

```JavaScript
if (cn) {
    // データベースとの接続をクローズ
    cn.conn.close(function() {
        // 正常終了時に、ステータス表示をクリア
        node.status({});
    });
}
```

　デプロイボタンを押してフローをデプロイすると、javaモジュール、jdbcモジュールが自動的にインストールされます。データベースとの接続が正常に行われると、functionノードの下のステータスが緑色になり「connected」と表示されます。

![connectedflow.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/9a7a8877-46ff-a1b2-805b-6f96d44c57f5.png)

## functionノードを動かしてみる

　functionノードの設定が終わりましたので、早速を動かしてみましょう。injectノードのプロパティ設定で、変数msg.payloadにSQL文を書いてゆきます。

　まずは、表を作成するCREATE文です。「日時」をクリックしてプルダウンメニューを表示した後、型として「文字列」を選択します。その後、以下の様にSQL文を記載します。

![createtable.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/bd1c9656-8db0-afae-5cd5-7d1e21ec29b3.png)

**記載するSQL文**

```
CREATE TABLE postalcode (code INTEGER, address VARCHAR(255));
```

　ここでは、郵便番号と住所の対応のデータを格納する表postalcodeを作成しました。codeに郵便番号の数値を、addressに住所の文字列を格納できます。

　デプロイボタンを押した後、injectノードの左側のボタンをクリックします。正常に表を作成できると、デバッグタブには「0」という数値が表示されるでしょう。

　次は、データを登録するINSERT文です。同様に、injectノードのプロパティ設定に、郵便番号と住所を登録する以下のINSERT文を記載します。

![insert.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/88eef604-7f8b-63bf-2993-60b68b1e4f80.png)

**記載するSQL文**

```
INSERT INTO postalcode VALUES ('2440817', 'Yokohama');
```

　デプロイ後、injectノードのボタンをクリックすると、先ほどと同様に、デバックタブに「1」と表示されます。これでデータの登録が完了です。

　最後に、表を検索するSELECT文です。以下の様に、表の内容を全て出力するSELECT文を記載します。

![select.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/1acc06f9-22c2-bf62-f8c9-1fe90c8122ec.png)

**記載するSQL文**

```
SELECT * FROM postalcode;
```

デプロイ後、injectノードのボタンをクリックすると、デバッグタブに、表の内容が表示されます。

![selectresult.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/13f30cf1-69ca-a0e7-67a4-c16185e5b5fd.png)

　もし、最初のCREATE TABLE文からやり直したい場合は、表を削除する以下のDROP TABLE文を使ってください。

**記載するSQL文**

```
DROP TABLE postalcode;
```

## 最後に
　今回は、functionノードのプロパティ画面にある「設定」、「初期化処理」、「コード」、「終了処理」の4つのタブ内の機能を使いこなして、データベースに接続するためのfunctionノードを開発しました。

　今回作成したfunctionノードをさらにサブフローでラップし、オリジナルノードを作成して公開もしておりますので、ぜひ使ってみてください。

https://flows.nodered.org/node/node-red-contrib-jdbc

　このオリジナルノードは、下のフローの様に今回作成したfunctionノードと同じ方法でデータベースと接続できるノードです。

![jdbcnode2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/1d9452a5-bbd3-29a1-2f84-820162c0d48c.png)

　このノードは、以下の様なプロパティ設定画面から、JDBCドライバを自由に変更でき、接続先、ユーザ名や、パスワードをプロパティから設定できるようになっています。

![jdbcnode1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287766/c3d269ba-0344-116d-c48c-983e910ce4c7.png)

　そのため、PostgreSQL、Oracleデータベース、その他の歴史のあるデータベースでもJDBCドライバが存在すれば、どんなデータベースとも接続することができます。試してみた方、機能改善案のある方は、ぜひ[日立のGitHubリポジトリ](https://github.com/Hitachi/node-red-contrib-jdbc)のIssueにフィードバックを頂けると嬉しいです。

**商標について**

- Node-REDは、米国その他の諸国におけるOpenJS Foundationの登録商標です。
- OracleとJavaは、米国その他の諸国におけるOracle Corporationの登録商標です。
- その他記載の会社名、製品名などは、それぞれの会社の商標もしくは登録商標です。