HSQLDB - プログラミングに関する私的メモ

HSQLDB

HSQLDBは純java製のデータベースマネージメントシステム(DBMS)で、hsqldb.jarファイル一つで提供されているので手軽に扱える。またコマンドラインパラメータで以下の四つの起動モードを選択できる。

インメモリモード

ファイルに書かずにメモリ内で動作するモード。アプリケーションを終了するとデータは消える。そんなものの使い道としては、データをSQLで取り扱いたいときで、クライアントアプリケーション側でデータの供給や保存を受け持つようなことが考えられる。

スタンドアロンモード(インプロセスモード)

アプリケーション中から起動するモード。ファイルに書くのでアプリケーションを終了させてもデータは消えない。

サーバーモード

一般のDBMSと同様に、TCP/IPでリクエストを受け付けるサーバーとして起動するモード。クライアントアプリケーションとは別にあらかじめ起動しておかなければならない。

Webサーバーモード

HTTPでSQLリクエストを受け付けるサーバーモード。

用語

カタログ

HSQLDBではデータの保存場所をカタログという。カタログには以下の三種類ある。

メモリ

データベースをファイルではなくオンメモリで保持する。JavaVMが再起動すれば消える。

ファイル

データベースをファイルで持つ。

リソース

データベースを.ZIPや.jarファイル内のリソースとして持つ。リードオンリーでアプリケーションと一緒に配布するのに適する。

HSQLDBの付属ツール

hsqldb.jarには、データベースマネージャーだけでなく色々なツールも含まれている。コマンドラインパラメータでそれらツールを起動できる。

GUIで操作するDatabase Manager

データベースを操作するためのGUIツール。HSQLDBだけでなく一般的にPC上あるいはネットワーク上のデータベースに接続して操作するための汎用ツール。とりあえずどんなことが出来るか試したい時にいいかも。

コマンドラインで次のようにして起動すると(ちなみにjarファイルをダブルクリックするとSwingバージョンが起動する)

java -classpath ..\lib\hsqldb.jar org.hsqldb.util.DatabaseManager

次のようなデータベースに接続するためのダイアログが開く。

Recent

最近接続した履歴から設定を選択できる。

Clr

最近接続した設定履歴を消去する。

Setting Name

データベースとの接続名。アプリケーションから識別するための名前をつける。

Type

接続するデータベースのタイプを選択。HSQLDBに対しては上記で説明した四つの起動モードが選択でき、さらにDB2やMySQL等他のデータベースも選択できる。選択したデータベースに応じて下記DriverとURLが変化する。

Driver

データベースへアクセスするためのデータベースドライバーを指定する。Typeの選択により自動的に変更されるが、オプションが必要なドライバーもある。HSQLDBを使うときは"org.HSQLDB.jdbcDriver"である。

URL

データベースのファイル位置で、ドライバーにより書式が異なる。

User

データベースに接続するには、データベースに設定されているユーザー名とパスワードが必要である。このGUIからHSQLDBを使って新規に作成する場合はデフォルトユーザー名はsaである。

Password

データベースに接続するには、データベースに設定されているユーザー名とパスワードが必要である。このGUIからHSQLDBを使って新規にデータベースを作成する場合はデフォルトパスワードは空である。

HSQLDBでCSVファイルを取り扱う

次のようにしてテキストファイルをデータベースとして使用できる。

CREATE TEXT TABLE test01 (ID INT, NAME VARCHAR(50))
SET TABLE test01 SOURCE "test.tsv;fs=\t;encoding=Shift_JIS"

HSQLDB Ver.1.7.2以降では何もないデータフィールドに関して次のような仕様がある。

• テキストテーブルに割り当てるファイルは、リードオンリーや空であってはならない。ただ、新規にファイルを作ることは出来る模様。
• 何もないフィールドは(ただスペースだけがあるフィールドも)nullである。
• ダブルクォーテーション二連続(つまり"")は空文字列である。

またSET TABLE SOURCE文にて、CSV等を使うときに便利な次のブール値オプションが使える。

ignore_first (デフォルトはfalse)

ファイルの最初の行を無視する。

quoted (デフォルトはtrue)

必要な場合だけ"で囲む。例えば値にセパレーター文字列が含まれている場合。falseにすると"もデータの文字として扱われる。

all_quoted (デフォルトはfalse)

ファイルへの書き込み時に、文字データはすべて"で囲む。

例
CREATE TEXT TABLE mytable (ID int, NAME VARCHAR(50))
SET TABLE mytable SOURCE "myfile.tsv;ignore_first=true;all_quoted=true;fs=\t"

Ver.1.8.0.8以降では、テキストテーブルとの接続を切断する方法がある。

SET TABLE mytable SOURCE OFF

接続を切断すると、mytableは空になって書き込めなくなる。

ただ、接続するための情報は残っているので、次のようにして再度接続できる。

SET TABLE mytable SOURCE ON

テキストファイル使用時の注意事項

• プロパティファイルのtextdb.allow_full_pathプロパティがtrueに設定されない限り、テキストファイルの場所は相対パスになる?
• 空行は無視される。
• 次の文で作られたテキストテーブルに対応するテキストファイルの場所は、

SELECT <select list> INTO TEXT <tablename> FROM

データベースのあるディレクトリで、かつ、ファイル名はテーブル名から生成され、テーブル名に非英数文字があればアンダースコアに置き換えられ、小文字に変換され、最後に".csv"がついたファイル名になる。

• テキストテーブルにもプライマリーキーが設定できる。

以上はHSQLDBのページ(英文)からの抜粋。

テキストファイルから日付データを読む時の注意

株価のCSVを読む場合等で、日付部分が2010/4/21等/で区切られている場合、テーブルをテキストファイルに割り当てした時点でエラーになる。また、月日は二桁でないと読めない。2011年現在、ISO標準で日付は-で区切り月日は二桁で表すことになっていて、HSQLDBもこれにしたがっているため。

テキストファイルを読み込み専用にする場合は、日付部分を単に文字列データとして読み込み、プログラム内部で日付に変換するという手が使える。

javaで使うときの変更事項

これはjava側の変更だが。1.5以降ではClass.forName(ドライバ名)が不要になった。

クラスパスさえhsqldb.jarに通っていれば、いきなり

java.sql.Connection con = java.sql.DriverManager
.getConnection("jdbc:hsqldb:file:c:/test/javatest/hsqlsbtest");

と書いて接続できる。

javaで使うときの注意事項

• SQLは大文字小文字区別しないが、javaはするので、SQLからの応答が大文字に勝手に変換されると困ることがある。そうなっても困らないようにテーブル名などリテラルは大文字で書いておくのが無難。逆にリテラルをダブルクォーテーションで囲めばSQL側でも大文字小文字区別するし日本語でもOKになる。だが、場合により文字列の中にダブルクォーテーションを含める必要が出てきたり面倒になる。
• create table とすると、デフォルトではインメモリモードでテーブルが作成されてしまう。ファイルとしてデータを残すには、create cached table としなければならない。もしくはデフォルトのモードを変更しておく。
• インメモリモードでも.dataファイルができないだけで、.scriptファイルは生成され、ここにSQLが記録されるので実はデータが残るかもしれない。データが残るかどうかはまだテストしていない。
• テーブルの存在確認は次のようにすればよい(詳しくはgetTables()を参照)が、めんどうだ。もっとスマートな方法は無いか。

ResultSet rs = connection.getMetaData().getTables(null, null, "TABLE01", null);
while (rs.next()) {
    // ここは  rs.getString(3).equal("TABLE01") とも書ける。
    if (rs.getString("TABLE_NAME").equal("TABLE01")) {
        // TABLE01が存在した、フラグでも立てておく。
     }
} // ここにそのまま抜けてくると、存在しなかった

• SQL文の中で文字列を囲むつもりでエスケープ-ダブルクォーテーションを使ったら不明なトークンとみなされてエラー。この場合はシングルクォーテーションで囲む。

String sql = "insert into TBL01 (ID, NAME) values (1, \"てすと\");";   // NG
statment.executeUpdate(sql);

String sql = "insert into TBL01 (ID, NAME) values (1, 'てすと');";   // OK
statment.executeUpdate(sql);

---