How to use HSQLDB for the first time

This blog post is a beginner-level tutorial explaining how to use HSQLDB, an SQL relational database system (RDBMS) written in Java.

HSQLDB supports both the client-server model (i.e. the server opens the database files) and the embedded model (i.e. opening the database files directly). In both cases we mean use the term client meaning the process which initiates the SQL statement.

Each HSQLDB table can have storage type memory, cached and text. Data in memory table is loaded to memory (from the .script file, parsed as SQL INSERT INTO statements at load time) when the database is opened, and saved (written back to the .script file) when the client disconnects. Data in cached table isn't loaded to memory in its entirety, but parts of it are read from the binary .data file (also called as the cache file) at query time; subsequent changes are kept in memory, and are written to the .data file when the client disconnects. Thus a huge number of inserts done by a single client uses a huge amount of memory. Data in a .text table is stored in a separate CSV file.

HSQLDB stores the database in a few files with different suffixes: .lck, .script, .properties, .data, .log, .backup and .lobs . Uncommitted data is not stored in any of the files. Committed data is first appended to the .log file as SQL INSERT INTO and DELETE FROM statements. When the client disconnects (either by exiting or invoking the SHUTDOWN; SQL statement), committed data is moved the the .script file (for table type memory, as SQL INSERT INTO statements with deleted rows omitted) or the .data file (for table type cached, as binary data which is faster to query). The .log and .script files are text files containing SQL statements, separated by newlines, no semicolon at the end. Data definition statements are first stored in the .log file (e.g. CREATE TABLE), and when the client disconnects, they are moved to the .script file (normalized as CREATE CACHED TABLE and CREATE MEMORY TABLE, table and column names uppercased).

Follow these steps to run your first few SQL statements:

• Install Java (the JRE). The following command (without the leading $) should work after successful installation:

  $ java -version
  openjdk version "11.0.11" 2021-04-20
  ...
  

• Download HSQLDB. When writing the blog post, .jar files were downloaded from http://hsqldb.org/download/hsqldb_251_jdk6/. You may use a later version. You need 2 .jar files: hsqldb-*.jar (not the hsqldb-*-sources.jar) and sqltool-*.jar. Rename the downloaded files to hsqldb.jar and sqltool.jar. It's important that the filename of the former is exactly hsqldb.jar, because sqltool.jar tries to find it by that name.
• In your download directory, create a text file named sqltool.rc containing these 2 lines:

  urlid first
  url jdbc:hsqldb:file:/tmp/first.hsql;hsqldb.default_table_type=cached;hsqldb.script_format=3
  

  On Windows, use C:/Windows/Temp (with forward slashes) or something similar instead of /tmp as the directory name.

  There is no need to create the files, HSQLDB will create them automatically.

• Open a terminal window, cd to your download directory, and run:

  java -jar sqltool.jar --rcFile=sqltool.rc first

  It displays a long welcome message, and shows you the sql> prompt.
• Type some SQL statements (without the leading sql> prompt):

  sql> CREATE TABLE names (first VARCHAR(255), last VARCHAR(255));
  sql> INSERT INTO names VALUES ('first1', 'last1');
  sql> INSERT INTO names VALUES ('first2', 'last2');
  sql> COMMIT;
  sql> INSERT INTO names VALUES ('first3', 'last3');
  sql> COMMIT;
  sql> SELECT * FROM names;
  FIRST   LAST
  ------  -----
  first1  last1
  first2  last2
  first3  last3
  
  Fetched 3 rows.
  sql> DELETE FROM names WHERE last='last2';
  sql> CREATE INDEX myindex ON names (last);
  sql> COMMIT;
  sql> SELECT * FROM names;
  FIRST   LAST
  ------  -----
  first1  last1
  first3  last3
  
  Fetched 2 rows.
  sql> SHUTDOWN;
  sql> \q
  

• At this point, metadata will be saved to compressed file /tmp/first.hsql.script, and data will be saved to binary file /tmp/first.hsql.data.
• See this example .java file on using a HSQLDB database from Java code. The JDBC connection string (starting with jdbc:hsqldb:file: in the sqltool.rc file above) should be specified in the first argument of DriverManager.getConnection.