HSQLDB   HSQLDBのセットアップ
  2020/05/02


HSQLDBのインストール

HSQLDBはHyperSQLとも呼ばれる、Javaで書かれたオープンソースのRDBMSです。
Javaで書かれたRDBMSとしては、 JavaDB(Apache Derby、その前にはCloudscapeと呼ばれていたもの)と並んで、 最もよく知られたソフトウェアです。

  1. ホームページからZipファイルをダウンロードします。 私が使用したのは2.3.3から2.5.0の各バージョンです。
  2. 展開すると、hsqldbというディレクトリがありますが、基本的な操作はその中のlibの下にある、 「hsqldb.jar」と「sqltool.jar」だけで可能です。これらをどこかにコピーして配置します。
  3. データベース・ファイルを置くディレクトリも任意に決めて作ります。
    hsqldb.jarを置いたディレクトリのそば(例えば./databaseというディレクトリを自分で作る) でも、全く関係ない場所に作ってもOKです。管理しやすい場所でいいでしょう。


In-Process(Standalone)モードでの使用

hsqldbには、大きく二つのモードがあります。

簡単なのは後者の方なので、先にこちらを説明します。
  1. GUIでデータベースを操作するツールとして、 Database ManagerというJavaアプリケーションが付属しています。 これは以下のようにコマンドラインで起動できます。 (Windowsの例。UNIX系OSでも同様だと思います)
    % java -cp \Wintools\hsqldb\lib\hsqldb.jar org.hsqldb.util.DatabaseManager
    
    この内容を書いたバッチファイルを作っておいても便利でしょう。 少しだけアレンジして以下のように書くもよし!
    @java -classpath ..\lib\hsqldb.jar org.hsqldb.util.DatabaseManager %1 %2 %3 %4 %5 %6 %7 %8 %9
    
  2. 接続情報を入力するダイアログが出るので、URLを打ちます。 インプロセスでのURLの形状は、以下のようになります。
    jdbc:hsqldb:file:/usr/dbms/hsqldb/data/testdb
    file:の後ろがデータベース・ファイルのパスで、 Windowsでもこのようにスラッシュで区切ることができます。 (Windowsの場合は、C:などドライブ名の指定も必要です) そのファイルはまだ作られていないので、 このDatabaseManagerが起動した際に新しく作られます; /usr/dbms/hsqldb/dataディレクトリにtestdb.data、testdb.properties、testdb.script という3つのファイルが出来ます(データ量等によっては作られないものもある)。 「testdb」の部分がいわゆるデータベース名で、自由に決めることができます。
    なおデフォルトの設定では、ユーザーは"sa"、パスワード""(空文字列)で接続できます。
  3. DatabaseManagerツールは簡単なインターフェースで、 SQLを打ち込むとそれを実行してくれます。 表を作ると、左のツリーに現在存在する表と列が表示されます。 (メニューのREFRESH TREEを実行すると最新に更新されます)
    なお、DatabaseManagerSwingというツールもあります。 使い方はDatabaseManagerをDatabaseManagerSwingに置き換えるだけ。 DatabaseManagerがAWTで作られているのに対し、同SwingはSwingで作られている、 というだけが違いのようです。


コマンドラインツールの使用

OracleのSQL*Plusにあたる、コマンドラインツールもあります。 それは「SqlTool」というものですが、使うには前準備と、何点か注意があります。

  1. まず、hsqldbディレクトリの中にある sample/sqltool.rc というファイルを D:\usr\dbms\hsqldb にコピーして、編集します。
    urlid sadb
    url jdbc:hsqldb:file:./database/sadb
    #url jdbc:hsqldb:file:D:/usr/dbms/hsqldb/sadb
    username SA
    password
    
    urlid localhost-sa
    url jdbc:hsqldb:hsql://localhost/
    username SA
    password
    
    "urlid"の行が接続情報のIDで、名前を適当につけることができ、 その後ろに続く内容がその接続情報です。
    上の例の上半分はIn-Processモードでデータベースに接続する方法で、 下半分がServerモードで動いているデータベースに接続する方法です。 In-Processの場合のURLを相対パスで指定すると、 SqlToolのJava VM(言い忘れましたが、SqlToolもまた、Javaで書かれたツールです) を起動したディレクトリからの相対パスになります。
    sqltool.rcはどこに配置してもokです。
  2. 次のようにして、SqlToolを起動します。
    % java -jar ./sqltool.jar --rcfile ./sqltool.rc sadb creatab.sql
    
    sqltool.rcを自分のホームディレクトリ以外に置いた場合、 --rcfileオプションでその位置を指定します。
    その次のパラメータがsqltool.rcの中に書いたurlidです。
    末尾にファイル名を指定すると、そのファイルをSQLスクリプトとして実行してくれます。 上記のcreatab.sqlはこんな感じです。
    (hsqldbのsampleディレクトリにも似たようなサンプルが入っていますね)
    \c true
    DROP TABLE AREA IF EXISTS;
    DROP TABLE SHITEN IF EXISTS;
    \c false
    
    \p Creating table AREA and SHITEN
    CREATE TABLE AREA (
      AREA_CODE     INT NOT NULL,
      AREA_NAME     VARCHAR(30),
      CONSTRAINT AREA_PK PRIMARY KEY(AREA_CODE)
    );
    
    CREATE TABLE SHITEN (
      SHITEN_CODE  VARCHAR(5) NOT NULL,
      SHITEN_NAME  VARCHAR(20),
      AREA_CODE    INT,
      ADDRESS      VARCHAR(200),
      EMPLOYEE_NUM INT,
      CONSTRAINT SHITEN_PK PRIMARY KEY(SHITEN_CODE)
    );
    
    \p Inserting test records
    INSERT INTO AREA (AREA_CODE, AREA_NAME)
     VALUES ('0', 'テスト');
    
    COMMIT;
    SHUTDOWN;
    
    \cや\pというのはSqlToolの命令です。
    前者はSQLがエラーになった場合、後続を実行するかやめるかを指定するオプションで、 trueが実行、falseがやめるになり、デフォルトはfalseです。
    \pは単にコメントを表示する命令です。
    そして重大な注意ですが、まずDML文はCOMMIT;でコミットしないとデータベースに反映されません。 そしてIn-Processモードでデータベースを開いた場合はさらに、 SHUTDOWN;でデータベースを閉じないと、DDL文すら保存されません。 これに気づかないと、表を作ってデータも追加したのに、 sadb.logにもsadb.scriptにも全く定義やデータが反映されていない!!と延々悩むことになります。 (というか既に悩みました)
    または、urlidに書く接続URLの最後に";shutdown=true"という追加属性をつけておくと、 終了時にSHUTDOWNもしてくれます。
なお、実行するSQLスクリプトに日本語を含む場合には、javaのコマンドラインで "-Dsqlfile.charset=Windows-31J" のように文字コードを指定すればOKです。


SqlToolを対話的に使う

java -jar ./sqltool.jar --inlineRc=url=jdbc:hsqldb:hsql://localhost/sadb,user=sa
このように起動すると、パスワードを尋ねるプロンプトが現れます。 何も入力せずにENTERを押すと、対話的な実行が可能です。

または、rcfileを使って、

java -jar ./sqltool.jar --rcfile sqltool.rc sadb
のようにしても起動できます。

使い方ですが、とりあえず「\?」と打つとヘルプが出ます。
よく使うコマンドは以下の通りです。

いきなりですが終了は\qです。(最初に覚えておくことですね)

「\i SQLファイル」を実行すると、SQLファイルに書かれたSQLが実行されます。 CREATE TABLE文などのDDLを実行するのに使います。 このとき、処理結果のメッセージ等は何も出ないので、慌てないように。 そうそうこれらのコマンドを打つときはSQLを打つのと異なり、「;」は不要です。 改行と同時に実行されます。

「\d 表などのオブジェクト名」を実行すると、 OracleのDESCRIBE(DESC)のように定義情報を見ることができます。
また「\dt」と打つとテーブルの一覧が、「\dv」と打つとビューの一覧が表示されます。 他にもオブジェクト種別ごとに「\d+1文字」の形で一覧が見れます。 それらはヘルプに書いてあります。
データファイルから表へのインポート、その逆のエクスポートは、 それぞれ\m、\xで行えます。\m?、\x?と打つとそれぞれ詳細な説明が見れます。
インポートについてこの下で詳しくご紹介します。


SqlToolによるデータのインポート

まずインポートですが、デフォルトでは列区切りは|、 インポート先のテーブル名はファイル名から拡張子以降を除いた物、 NULLを表現する文字列は"[null]"になっています。 これらを変更するには、「* コマンド」で「*変数」を変更します。

sql>* *DSV_COL_SPLITTER = \t
sql>* *DSV_TARGET_TABLE = table1
sql>* *NULL_REP_TOKEN = 
ここで、行頭は「* *」(アスタリスク・スペース・アスタリスク)です。 つまり、最初の*は*というコマンドで、次の*DSV_COL_SPLITTERは、頭に*もついて*DSV_COL_SPLITTERという変数名です。 間違って*が足りないと「SqlSyntaxErrorException」など意味不明なエラーが出て延々悩むことになるので、 注意が必要です。

タブ文字は\tと表現できます。またNULL_REP_TOKENの上の設定は空文字列を表します。
設定した「*変数」の値を確認するには、「* listvalues」と打てばokです。
そしてインポートは「\m データファイル名」で行えます。

sql>\m /the/path/of/shiten.dat
Import summary ('#'-skips / rejects / inserts):  0 / 0 / 18.
Insertions will be lost of you don't commit.
sql>commit;
インポート時のデータファイルについていくつか注意があります。
HSQLDBのマニュアルでは、データファイルのことをDSV(Delimiter Separated Value)ファイルと呼んでいますが、 そのファイルの拡張子は何でも構いません。上の例のように.datもokです。
つぎに、ファイルの1行目には必ず列名を書く必要があります。
以下がshiten.datの例です。
SHITEN_CODE	SHITEN_NAME	ADDRESS	AREA_CODE	EMPLOYEE_NUM
00100	東京	東京都港区北青山9-99-998	3	40
00120	さいたま	さいたま市桜田99-99	3	200
00200	横浜	横浜市中区根岸町999	3	222
またデータファイルの日本語のエンコーディングですが、 WindowsでHSQLDBを使っている際には、データファイルはシフトJIS (MS932、Windows-31Jとも言う)で普通に書けばOKです。 こちらのマニュアルを見ると、***.rcファイルの設定で変更もできるようです。

最後に改行文字ですが、デフォルト設定では「*DSV_ROW_SPLITTER」の値は、\r\n|\r|\nとなっています。 つまり今日一般的なLF(UNIX)、CR+LF(Windows)のどちらでも、改行文字は正しく認識されます。

\mコマンドでデータをロードする命令を、スクリプトに書いて流すこともできます。 以下はその例です。

* *DSV_COL_SPLITTER = \t
* *DSV_TARGET_TABLE = CARD_PRICES
* *NULL_REP_TOKEN =
* *DSV_REJECT_REPORT = import_err.html

DELETE FROM CARD_PRICES;

\m input_card_prices.txt
COMMIT;
SHUTDOWN;
\p end.
というスクリプトファイル "load.sql" を作って
% java -jar ./sqltool.jar --rcfile ./sqltool.rc sadb load.sql
でロードします。

インポートが長くなったので、エクスポートは別の機会にしましょう…


Serverモードでの使用

  1. 今度は、hsqldbをサーバープロセスとして起動します。
    % java -classpath ./hsqldb.jar org.hsqldb.Server -database.0 /usr/dbms/hsqldb/testdb
    
    対象とするデータベース・ファイルのパスは-database.{N}オプションで指定します。 正式にはfile:を頭につけるのだと思いますが、上のように省略もできるようです。
    バージョン1.7.2から、hsqldbのサーバーは複数のデータベースを同時に開いて待つことができます。 その場合は、個々のデータベースに別名(alias)をつけます。 それは、-dbname.{N}オプションで行います。
    % java -classpath ./hsqldb.jar org.hsqldb.Server -database.0 testdb -dbname.0 testdb \
     -database.1 mydb -dbname.1 mydb
    
    あんまり長すぎる場合、コマンドラインに書くのではなく、 server.propertiesファイルを起動するディレクトリに書いておく方がよいでしょう。
    database.0=file:/opt/db/accounts
    dbname.0=an_alias
    
    database.1=file:/opt/db/mydb
    dbname.1=enrollments
    
    database.2=mem:adatabase
    dbname.2=quickdb
    
    それで、インプロセスモードのときと同様、このパスのデータベースファイルがまだ存在しなければ、 サーバープロセスの起動時に作られます。
    このサーバープロセスは、Ctrl+Cを押すことで終了できますが、それはAbortという位置づけで、 変更は保存されません。 変更を保存して終了するには、自作なり、SqlToolなり何らかのクライアントで接続して、 SHUTDOWN;コマンドを発行する必要があります。 例えば、以下のようなSQLスクリプトをSqlToolで使うという方法があります。 (そのままやんけ)
    SHUTDOWN;
    
  2. クライアントから接続して試してみます。先にも使ったDatabaseManagerを使う場合
    % java -cp hsqldb.jar org.hsqldb.util.DatabaseManager
    
    ツール「QueryTool」を使う場合、
    % java -cp hsqldb.jar org.hsqldb.util.QueryTool
    
    QueryToolの詳細は-?オプションを見ます。上記のコマンドラインでは、 勝手にデータベースにサンプルテーブルを作ってしまいます。 これが不要の場合は、忘れずに-test falseをつけること。
    URLの形状は以下のようになります:
    jdbc:hsqldb:hsql://localhost/sadb
    sadbの部分は、サーバーを起動したときのdbname.{N}のいずれかの値を指定します。 全く省略した場合、つまり
    jdbc:hsqldb:hsql://localhost
    jdbc:hsqldb:hsql://localhost/
    のように指定すると、-dbname.{N}が空文字列、 またはそのオプションを指定していないデータベースに接続しにいくことになります。


パスワードを設定する

ユーザーSAのデフォルトのパスワードは""(空文字列)です。 パスワードを設定するには、次のようにSQLToolを起動して、 SET PASSWORDコマンドを使います。

java -jar ./sqltool.jar --rcfile ./sqltool.rc sadb
sql> set password "hello01"
sql> shutdown;
sql> \q
ここで、パスワードは必ず二重引用符""で囲む必要があります。
また一度パスワードを設定してしまうと、 同じsqltool.rcでは接続できなくなります。 編集して、passwordの値を合わせる必要があります。
urlid sadb
url jdbc:hsqldb:file:./sadb
username SA
password hello01


データ型

hsqldbの表(テーブル)の各列に指定できるデータ型には以下の種類があります。

整数型にはTINYINT、SMALLINT、INTEGER、BIGINTがあります。 これらはJava世界のそれぞれbyte、short、int、longの精度に対応します。 これらのうちbyteはHSQLDB独自の精度ですが、 それ以外はSQL標準に則ったデータ型名と範囲になっています。

小数部を持つことも持たないこともできる数値型としては、NUMERICとDECIMALがあります。 これらは全く同じもので、java.math.BigDecimalの精度に対応します。 これらは DECIMAL とだけ指定できる他に、 DECIMAL(10)やDECIMAIL(12,3)のように精度を指定できます。

小数型にはREAL、FLOAT、DOUBLEがあります。 これらは全てJava世界のdoubleに相当し、全く同じ物です。 これらには精度は指定できません。またSqlToolなどで値を表示すると、 「12.0000000…」と延々と0が並ぶ表記になります。 したがって、アプリケーション側で表示を工夫する必要が多くの場合あります。

真偽値を保持するBOOLEANという型は、SQL標準にならい、TRUE、FALSE、 そしてUNKNOWNという値をとりえます。 これに対してBITは、バージョン1台ではBOOLEANと全く同じ物でしたが、 バージョン2では単に1ビットの値(つまり0か1)を持つ型として、BOOLEANと分離しました。

テキスト文字列型には、SQL標準と同じ名前のデータ型としては、 固定長のCHARACTER、可変長のCHARACTER VARYING、 大量データを保持できるCLOB(Character Large OBject)があります。 先頭から2つのよく知られた商用RDBMSでの別名、CHARとVARCHARも使えます。 またHSQLDBでは、LONGVARCHARは長くないVARCHARと全く同じ物です。

バイナリ文字列型には、 固定長のBINARY、可変長のBINARY VARING、そしてBLOB(Binary Large OBject)があります。

日付/時刻型にはDATE、TIME、TIMESTAMPがあります。


DEFAULTと現在時刻の設定

多くのRDBMSと同様、hsqldbにも組み込み関数が用意されていて、 その中にはこれも定番の、現在時刻を返すものも用意されています。 組み込み関数の一覧はこちらに載っています。 現在時刻を返す関数は NOW()で、これはLOCALTIMESTAMPも同じ意味です。 これを使って、表定義のDEFAULT句に現在時刻を設定できます。

CREATE TABLE ITEMS (
  ITEM_CODE VARCHAR(8) NOT NULL,
/* 中略 */
  CDATE     DATE DEFAULT NOW(),
  UDATE     DATE DEFAULT NOW(),
  CONSTRAINT ITEMS_PK PRIMARY KEY(ITEM_CODE)
);
そういえば、DEFAULTといえば一つ注意があります。 DEFAULTとNOT NULLを両方使うとき、HSQLDBの文法では順番に制約があります。 Oracleだと
COL1 VARCHAR(3) NOT NULL DEFAULT 'ZZZ'
と書けたのが、hsqldbでは「unexpected token: DEFAULT」というエラーになります。 正しくは次の順序です。
COL1 VARCHAR(3) DEFAULT 'ZZZ' NOT NULL


列定義の変更

Oracleと違うといえば、hsqldbのALTER TABLE文もOracleとかなり違います。 例えば列のデータ型を変更する際、Oracleでは

ALTER TABLE TAB1 MODIFY COL1 VARCHAR(12);
と書きますが、hsqldbではこう書きます。
ALTER TABLE TAB1 ALTER COLUMN COL1 SET VARCHAR(12);


参照ビューがあると表を削除できない

もう一つ。表Aを参照するビューBがあるとき、AをDROPできるでしょうか?
Oracleではできて、Bは「INVALID」になります。
hsqldbではできません。「dependent objects exist」というエラーになります。 また参照するビューがあると表をDROPできないだけでなく、上のALTER TABLEもできません。


JDBCクライアントの開発

hsqldb.jarにはJDBCドライバも入っているので、JDBCクライアントを開発することができます。 ドライバのクラス名はorg.hsqldb.jdbcDriverです。
JDBCクライアントの作り方は、ほとんどごく普通の書き方でいいのですが、上と同じ注意点が1点。 In-Processモードでデータベースを開いた場合、 アプリケーションの終了時に "SHUTDOWN" というSQLステートメントを実行してから終了する必要があります。 これはコミットされるかという話以前のことで、 "SHUTDOWN" を実行しないとDML文だけでなくDDL文さえ実行が保存されません。 なおそれは、個々の接続(Connection)を切る際に実行する必要があるのではなく、 1回のJava VMで同じデータベースに複数回接続をするのであれば、最後の1回だけでよいということです。 毎回接続の度にSHUTDOWNが必要だというのなら世にも恐ろしいことですが、 そうでないのは若干救いといえます。 また、先述の通り、JDBC接続のURLに";shutdown=true"を追加でつけておくと、 そのJava VMで最後の接続が閉じられたときにSHUTDOWN処理も行ってくれます。

検索処理のサンプルです。「SHITEN」テーブルから「AREA_CODE」が「3」の行を検索するという想定です。

public class HsqldbTester {
    public static void main(String[] args) throws SQLException {
        String user = "sa";
        String password = "";
        String dburl = "jdbc:hsqldb:file:/D:/usr/dbms/hsqldb/database/sadb;shutdown=true";
        String sql =
                "SELECT SHITEN_CODE, SHITEN_NAME, ADDRESS, EMPLOYEE_NUM FROM SHITEN " +
                "WHERE AREA_CODE = ? ORDER BY SHITEN_CODE";

        Connection conn = DriverManager.getConnection(dburl, user, password);
        PreparedStatement st = conn.prepareStatement(sql);
        st.setString(1, "3");
        ResultSet rset = st.executeQuery();

        while (rset.next()){
            System.out.println(rset.getString(1) + "\t" +
              rset.getString(2) + "\t" + rset.getString(3) + "\t" + rset.getString(4));
        }
        rset.close();
        st.close();
        conn.close();
    }
}


Webアプリケーションの起動・停止とhsqldbサーバの起動・停止を連動する

これは、ServletのListenerの仕組を使って可能です。 Project Amaterasのこのページ にそのようなServletContextListenerの実装例が載っています。 私もほぼそのまま参考にして期待通りの動作を確認しました。

<Resource name="jdbc/sadbREMOTE" auth="Container" type="javax.sql.DataSource"
    driverClassName="org.hsqldb.jdbcDriver"
    url="jdbc:hsqldb:hsql://localhost/"
    factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
    username="sa" password=""
    maxWait="1000" maxActive="30" maxIdle="10"
    removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true" />


standaloneモードでWebアプリケーションに内蔵する

上記の例は、Webアプリケーションサーバの起動・停止に連動して、 hsqldb「サーバ」を起動・停止するものでしたが、 それとは別に、以下のようなJNDIリソースを定義すれば、 WebアプリケーションのJava VMの中からstandaloneモードのhsqldbと接続を開いて、 操作を行うことができます。 (この例では、DataSource FactoryとしてJakarta Commons DBCPを使っています)

<Resource name="jdbc/sadb" auth="Container" type="javax.sql.DataSource"
    driverClassName="org.hsqldb.jdbcDriver"
    url="jdbc:hsqldb:file:C:/the/path/of/database/sadb"
    factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
    username="sa" password=""
    maxWait="1000" maxActive="30" maxIdle="10"
    removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true" />


standaloneモードのJDBCクライアント開発時のエラーについて

standaloneモード、つまりURLが「jdbc:hsqldb:file〜」でHSQLDBに接続に行く JDBCアプリケーションを実行すると、 「〜unexpected token: CONFLICT required: DEADLOCK」 とか「unexpected token: AVG」というエラーが出ることがあります。 意味はよく分かりませんが、データベースの「**db.script」 をテキストエディタで次のように変更するとエラーが出なくなりました。
まず、「〜unexpected token: CONFLICT required: DEADLOCK」 というエラーが出たら、**db.scriptの

SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE
という行を
SET DATABASE TRANSACTION ROLLBACK ON DEADLOCK TRUE
に変更して保存し、再度Javaプログラムを実行します。すると今度は 「unexpected token: AVG」というエラーになると思うので、
SET DATABASE SQL AVG SCALE 0
という行を削除します。
(後日注:両方同時にやってもOKのようです)

  もくじ
HSQLDB   (C) 2002-2020 MISUMI URANO (2003/12/05 - 2020/05/02)
Ads by TOK2