H2 Database Engineのリファレンスを見ていると、LINKED TABLEという
機能についての解説がありました。
Linked Tablesどうやら、別のデータベースのテーブルを仮想的にH2にあるテーブルのように見せる機能みたいです。
これはおもしろいかも。
MySQLとのLINKED TABLEを作って見ました。
MySQLにデータベースとテーブルの作成
H2からリンクするデータベースとテーブルをMySQLに作成します。
データベースとテーブル、テストデータの投入に使用したクエリは以下のとおり。
CREATE DATABASE linkedtest CHARACTER SET utf8 COLLATE utf8_general_ci;
use linkedtest;
CREATE TABLE T_MYSQL (id integer, val varchar(20));
INSERT INTO T_MYSQL(id, val) value (1, 'mysqlval_1');
INSERT INTO T_MYSQL(id, val) value (2, 'mysqlval_2');
INSERT INTO T_MYSQL(id, val) value (3, 'mysqlval_3');
INSERT INTO T_MYSQL(id, val) value (4, 'mysqlval_4');
INSERT INTO T_MYSQL(id, val) value (5, 'mysqlval_5');
INSERT INTO T_MYSQL(id, val) value (6, 'mysqlval_6');
INSERT INTO T_MYSQL(id, val) value (7, 'mysqlval_7');
INSERT INTO T_MYSQL(id, val) value (8, 'mysqlval_8');
INSERT INTO T_MYSQL(id, val) value (9, 'mysqlval_9');
INSERT INTO T_MYSQL(id, val) value (10, 'mysqlval_10');
linkedtestというデータベースにT_MYSQLというテーブルを作成。
適当にサンプルのデータをインサートしました。
MySQLのJDBCドライバ
LINKED TABLEを使うには、別データベースに接続するためJDBCドライバが必要なようです。
今回はMySQLとLINKED TABLEしてみるので、MySQLのJDBCドライバをダウンロードしました。
http://dev.mysql.com/downloads/connector/j/ここからmysql-connector-java-5.1.18.tar.gzをダウンロードして解凍し、
mysql-connector-java-5.1.18-bin.jarを取得します。
H2起動時のclasspath指定
H2はDebianで動かしています。
環境はここで作成した時と同様。
DebianでH2 Database Engineを起動し、別端末からJavaで接続する/opt/h2/binにh2-1.3.162.jarがある状態です。
MySQLのサイトからダウンロードして取得したmysql-connector-java-5.1.18-bin.jarを
/opt/h2/libにコピーしました。
このコピーしたjarをクラスパスに含めて、H2を起動します。
起動するときのオプションはこんな感じ。
# java -cp h2-1.3.162.jar:/opt/h2/lib/mysql-connector-java-5.1.18-bin.jar
org.h2.tools.Server -webAllowOthers -tcpAllowOthers -baseDir /opt/h2/data
※実際は一行。Windowsの場合は、-cp以降のjarファイルの区切り文字が「;」になるかと思います。
これで準備は完了です。
Javaのサンプルプログラム
別端末からJavaのプログラムで接続してLINKED TABLEを作成し、検索してみます。
なお、LINKED TABLEを作成する時の構文は
CREATE LINKED TABLE [H2内で使用するテーブル名]('[JDBCドライバクラス]', '[接続文字列]', '[ユーザー名]', '[パスワード]', '[リンクするテーブル名]');
そのため、今回MySQLに作成したlinkedtestデータベースのT_MYSQLテーブルを
T_LINKという名称でLINKED TABLEにするには
CREATE LINKED TABLE T_LINK
('com.mysql.jdbc.Driver', 'jdbc:mysql://localhost/linkedtest',
'[MySQLのユーザー名]', '[MySQLのパスワード]', 'T_MYSQL')
となりまkす。
これらを踏まえ、
・MySQLとT_LINKというLINKED TABLEを作成
・H2にT_H2というテーブルを作成
・T_LINK(MySQLのテーブル)とT_H2(H2のテーブル)を結合し、検索結果表示
というサンプルはこんな感じになりました。
※実行するクライアントにMySQLのJDBCドライバは不要です。
H2のJDBCドライバだけあればOK
package com.fc2.blog68.symfoware.h2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SampleLinked {
public static void main(String[] args) throws Exception {
//JDBCドライバをロード
Class.forName("org.h2.Driver");
//コネクション取得
Connection conn = DriverManager.getConnection("jdbc:h2:tcp://192.168.1.4/test", "sa", "");
Statement stmt = conn.createStatement();
//テーブル作成
stmt.execute("CREATE TABLE IF NOT EXISTS T_H2 (id integer, val varchar(20))");
stmt.execute("DELETE FROM T_H2");
//データの登録
stmt.execute("INSERT INTO T_H2 (id, val) values (1, 'h2val_1')");
stmt.execute("INSERT INTO T_H2 (id, val) values (2, 'h2val_2')");
stmt.execute("INSERT INTO T_H2 (id, val) values (3, 'h2val_3')");
stmt.execute("INSERT INTO T_H2 (id, val) values (4, 'h2val_4')");
stmt.execute("INSERT INTO T_H2 (id, val) values (5, 'h2val_5')");
stmt.execute("INSERT INTO T_H2 (id, val) values (6, 'h2val_6')");
stmt.execute("INSERT INTO T_H2 (id, val) values (7, 'h2val_7')");
stmt.execute("INSERT INTO T_H2 (id, val) values (8, 'h2val_8')");
stmt.execute("INSERT INTO T_H2 (id, val) values (9, 'h2val_9')");
stmt.execute("INSERT INTO T_H2 (id, val) values (10, 'h2val_10')");
//リンクテーブル作成
stmt.execute("CREATE LINKED TABLE T_LINK('com.mysql.jdbc.Driver', 'jdbc:mysql://localhost/linkedtest', 'root', 'password', 'T_MYSQL')");
//登録したデータの検索
StringBuffer sql = new StringBuffer();
sql.append("SELECT");
sql.append(" T_H2.id AS H2_ID,");
sql.append(" T_H2.val AS H2_VAL,");
sql.append(" T_LINK.id AS MYSQL_ID,");
sql.append(" T_LINK.val AS MYSQL_VAL");
sql.append(" FROM");
sql.append(" T_H2, T_LINK");
sql.append(" WHERE");
sql.append(" (T_H2.id = T_LINK.id)");
ResultSet rs = stmt.executeQuery(sql.toString());
while(rs.next()) {
System.out.print(rs.getString("H2_ID") + " ： ");
System.out.print(rs.getString("H2_VAL") + " ： ");
System.out.print(rs.getString("MYSQL_ID") + " ： ");
System.out.println(rs.getString("MYSQL_VAL"));
}
stmt.close();
conn.close();
}
}
実行してみると、ちゃんとMySQLの表とH2の表の結合結果が表示されます。
1 ： h2val_1 ： 1 ： mysqlval_1
2 ： h2val_2 ： 2 ： mysqlval_2
3 ： h2val_3 ： 3 ： mysqlval_3
4 ： h2val_4 ： 4 ： mysqlval_4
5 ： h2val_5 ： 5 ： mysqlval_5
6 ： h2val_6 ： 6 ： mysqlval_6
7 ： h2val_7 ： 7 ： mysqlval_7
8 ： h2val_8 ： 8 ： mysqlval_8
9 ： h2val_9 ： 9 ： mysqlval_9
10 ： h2val_10 ： 10 ： mysqlval_10
もちろん、
stmt.execute("INSERT INTO T_LINK(id, val) values (11, 'mysqlval_11')");
として、MySQLのテーブルにデータの追加、更新も可能です。
これ、複数のデータベースが混在している環境で上手く使えば、強力な機能かも。
