HSQLDB

What is HSQLDB?
HSQLDB is the leading SQL relational database engine written in Java. It has a JDBC driver and supports a rich subset of ANSI-92 SQL ( BNF tree format) plus SQL 99 and 2003 enhancements. It offers a small (less than 100k in one version for applets), fast database engine which offers both in-memory and disk-based tables and supports embedded and server modes. Additionally, it includes tools such as a minimal web server, in-memory query and management tools (can be run as applets) and a number of demonstration examples.

The product is currently being used as a database and persistence engine in many Open Source Software projects and even in commercial projects and products. In it's current version it is extremely stable and reliable. It is best known for its small size, ability to execute completely in memory, its flexibility and speed.

Preparation of the Development Environment
1) Download the latest version of HSQLDB from http://www.hsqldb.org/

2) Extract the files and place all required libraries found in the /lib directory of HSQLDB into the /lib directory of your new project. It should look like this:


 * lib
 * hsqldb.jar
 * servlet.jar

The HSQLDB jar package is located in the /lib directory and contains several components and programs. Different commands are used to run each program.

Components of the Hsqldb jar package


 * HSQLDB RDBMS
 * HSQLDB JDBC Driver
 * Database Manager (Swing and AWT versions)
 * Query Tool (AWT)
 * Sql Tool (command line)

The HSQLDB RDBMS and JDBC Driver provide the core functionality. The rest are general-purpose database tools that can be used with any database engine that has a JDBC driver.

Running HSQLDB
HSQLDB can be run in a number of different ways. In general these are divided into Server Modes and In-Process Mode (also called Standalone Mode). A different sub-program from the jar is used to run HSQLDB in each mode.

Each HSQLDB database consists of between 2 to 5 files, all named the same but with different extensions, located in the same directory. For example, the database named "test" consists of the following files:


 * test.properties
 * test.script
 * test.log
 * test.data
 * test.backup

The properties files contains general settings about the database. The script file contains the definition of tables and other database objects, plus the data for non-cached tables. The log file contains recent changes to the database. The data file contains the data for cached tables and the backup file is a zipped backup of the last known consistent state of the data file. All these files are essential and should never be deleted. If the database has no cached tables, the test.data and test.backup files will not be present. In addition to those files, HSQLDB database may link to any formatted text files, such as CSV lists, anywhere on the disk.

While the "test" database is operational, a test.log file is used to write the changes made to data. This file is removed at a normal SHUTDOWN. Otherwise (with abnormal shutdown) this file is used at the next startup to redo the changes. A test.lck file is also used to record the fact that the database is open. This is deleted at a normal SHUTDOWN. In some circumstances, a test.data.old is created and deleted afterwards.

Server Modes

 * In most server modes, the server can serve up to 10 databases that are specified at the time of running the server.

Hsqldb Server
This is the preferred way of running a database server and the fastest one. A proprietary communications protocol is used for this mode. A command similar to those used for running tools and described above is used for running the server. The following example of the command for starting the server starts the server with one (default) database with files named.

java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 file:mydb -dbname.0 xdb

The command line argument -? can be used to get a list of available arguments. Example:

++-+--+--+ ++-+--+--| ++-+--+--+
 * OPTION     |    TYPE     | DEFAULT  |         DESCRIPTION          |
 * -?            | --          | --       | prints this message          |
 * -address      | name|number | any      | server inet address          |
 * -port         | number      | 9001/544 | port at which server listens |
 * -database.i   | [type]spec  | 0=test   | name of database i           |
 * -dbname.i     | alias       | --       | url alias for database i     |
 * -silent       | true|false  | true     | false => display all queries |
 * -trace        | true|false  | false    | display JDBC trace messages  |
 * -tls          | true|false  | false    | TLS/SSL (secure) sockets     |
 * -no_system_exit| true|false | false    | do not issue System.exit   |

The server looks for a file in the current directory and loads properties from it if it exists.

Here is an example file:

server.port=1234 server.database.0=file:/mydatbasedir/test server.dbname.0=filetest server.database.1=res:/mypackage/test server.dbname.1=restest server.database.2=mem:test server.dbname.2=memtest ... server.database.n=... server.dbname.n=... server.silent=true
 * 1) mounts a file-based (persistent) database with alias 'filetest'
 * 2) database connection url would be 'jdbc:hsqldb:hsql://host:1234/filetest'
 * 3) but '/filetest' can be omitted because database.0 is the default
 * 1) mounts a 'file_in_jar' database with alias 'restest'
 * 2) database connection url would be 'jdbc:hsqldb:hsql://host:1234/restest'
 * 1) mounts a 100% in-memory (transient) database with alias 'memtest'
 * 2) database connection url would be 'jdbc:hsqldb:hsql://host:1234/memtest'


 * 1) end of 'server.properties' file

Command line options override those loaded from the file.

Hsqldb Web Server
This mode is used when access to the computer hosting the database server is restricted to the HTTP protocol. The only reason for using the Web Server mode is restrictions imposed by firewalls on the client or server machines and it should not be used where there are no such restrictions. The HSQLDB Web Server is a special web server that allows JDBC clients to connect via HTTP. From 1.7.2 this mode also supports transactions.

To run a web server, replace the main class for the server in the example command line above with the class org.hsqldb.WebServer :

java -cp ../lib/hsqldb.jar org.hsqldb.WebServer -database.0 file:mydb -dbname.0 xdb

The command line argument -? can be used to get a list of available arguments.

Hsqldb Servlet
This uses the same protocol as the Web Server. It is used when a separate servlet engine (or application server) such as Tomcat or Resin provides access to the database. The Servlet Mode cannot be started independently from the servlet engine. The hsqlServlet class, in the HSQLDB jar, should be installed on the application server to provide the connection. The database is specified using an application server property. Refer to the source file to see the details.

Both Web Server and Servlet modes can only be accessed using the JDBC driver at the client end. They do not provide a web front end to the database. The Servlet mode can serve only a single database.

Please note that you do not normally use this mode if you are using the database engine in an application server.

In-Process (Standalone) Mode
This mode runs the database engine as part of your application program in the same Java Virtual Machine. For most applications this mode can be faster, as the data is not converted and sent over the network. The main drawback is that it is not possible by default to connect to the database from outside your application. As a result you cannot check the contents of the database with external tools such as Database Manager while your application is running. In 1.8.0, you can run a server instance in a thread from the same virtual machine as your application and provide external access to your in-process database. The recommended way of using the in-process mode in an application is to use an HSQLDB Server instance for the database while developing the application and then switch to In-Process mode for deployment.

An In-Process Mode database is started from JDBC, with the database file path specified in the connection URL. For example, if the database name is testdb and its files are located in the same directory as where the command to run your application was issued, the following code is used for the connection:

Connection c = DriverManager.getConnection("jdbc:hsqldb:file:testdb", "sa", "");

The database file path format can be specified using forward slashes in Windows hosts as well as Linux hosts. So relative paths or paths that refer to the same directory on the same drive can be identical. For example if your database path in Linux is /opt/db/testdb and you create an identical directory structure on the C: drive of a Windows host, you can use the same URL in both Windows and Linux:

Connection c = DriverManager.getConnection("jdbc:hsqldb:file:/opt/db/testdb", "sa", "");

When using relative paths, these paths will be taken relative to the directory in which the shell command to start the Java Virtual Machine was executed. Refer to Javadoc for jdbcConnection for more details.

Memory-Only Databases
It is possible to run HSQLDB in a way that the database is not persistent and exists entirely in random access memory. As no information is written to disk, this mode should be used only for internal processing of application data, in applets or certain special applications. This mode is specified by the mem: protocol.

Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:aname", "sa", "");

You can also run a memory-only server instance by specifying the same URL in the server.properties. This usage is not common and is limited to special applications where the database server is used only for exchanging information between clients, or for non-persistent data.

Individual Database Properties
Each database has its own file as part of a small group of files which also includes and. The properties files contain key/value pairs for some important settings.

Connecting to a Database running as a Server
Once an HSQLDB server is running, client programs can connect to it using the HSQLDB JDBC Driver contained in hsqldb.jar. Full information on how to connect to a server is provided in the Java Documentation for jdbcConnection (located in the directory of the HSQLDB distribution). A common example is connection to the default port (9001) used for the hsql protocol on the same machine:

''Example 1.1. Java code to connect to the local Server above'' try { Class.forName("org.hsqldb.jdbcDriver" ); } catch (Exception e) { System.out.println("ERROR: failed to load HSQLDB JDBC driver."); e.printStackTrace; return; }   Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb", "sa", "");

In some circumstances, you may have to use the following line to get the driver.

Class.forName("org.hsqldb.jdbcDriver").newInstance;

Note in the above connection URL, there is no mention of the database file, as this was specified when running the server. Instead, the value defined for is used. Also, see the ''Chapter 4. Advanced Topics'' for the connection URL when there is more than one database per server instance.

Closing the Database
All databases running in different modes can be closed with the SHUTDOWN command, issued as an SQL query. From version 1.7.2, in-process databases are no longer closed when the last connection to the database is explicitly closed via JDBC, a SHUTDOWN is required. In 1.8.0, a connection property, SHUTDOWN=true, can be specified on the first connection to the database (the connection that opens the database) to force a shutdown when the last connection closes.

When SHUTDOWN is issued, all active transactions are rolled back. A special form of closing the database is via the SHUTDOWN COMPACT command. This command rewrites the file that contains the information stored in CACHED tables and compacts it to size. This command should be issued periodically, especially when lots of inserts, updates or deletes have been performed on the cached tables. Changes to the structure of the database, such as dropping or modifying populated CACHED tables or indexes also create large amounts of unused file space that can be reclaimed using this command.

Using Multiple Databases in One JVM
In the above examples each server serves only one database and only one in-memory database can be created. However, from version 1.7.2, HSQLDB can serve several databases in multiple server modes and allow simultaneous access to multiple in-process and memory-only databases. These capabilities are covered in the ''Chapter 4. Advanced Topics''.

Creating a New Database
When a server instance is started, or when a connection is made to an in-process database, a new, empty database is created if no database exists at the given path.

This feature has a side effect that can confuse new users. If a mistake is made in specifying the path for connecting to an existing database, a connection is nevertheless established to a new database. For troubleshooting purposes, you can specify a connection property ifexists=true to allow connection to an existing database only and avoid creating a new database. In this case, if the database does not exist, the getConnection method will throw an exception.

Security Considerations
When HSQLDB is run as a server, network access should be adequately protected. Source IP addresses may be restricted by use of TCP filtering or firewall programs, or standalone firewalls. If the traffic will cross an unprotected network (such as the Internet), the stream should be encrypted (for example by VPN, ssh tunneling, or Chapter 7. TLS using the SSL enabled HSQLS and HTTPS variants of the server and web server modes). Only secure passwords should be used-- most importantly, the password for the default system user should be changed from the default empty string. If you are purposefully providing data to the public, then the wide-open public network connection should be used exclusively to access the public data via read-only accounts. (I.e., neither secure data nor privileged accounts should use this connection). These considerations also apply to HSQLDB servers run with the HTTP protocol.

JDBC Client Example
Connection conn;                                               //our connnection to the db - presist for life of program

// we dont want this garbage collected until we are done public Testdb(String db_file_name_prefix) throws Exception {   // note more general exception

// Load the HSQL Database Engine JDBC driver // hsqldb.jar should be in the class path or made part of the current jar Class.forName("org.hsqldb.jdbcDriver"); // connect to the database. This will load the db files and start the // database if it is not alread running. // db_file_name_prefix is used to open or create files that hold the state // of the db. // It can contain directory names relative to the // current working directory conn = DriverManager.getConnection("jdbc:hsqldb:"                                          + db_file_name_prefix,    // filenames                                           "sa",                     // username                                           "");                      // password }

Hibernate Client Example
Set the following properties in the file :

org.hsqldb.jdbcDriver jdbc:hsqldb:hsql://localhost sa  org.hibernate.dialect.HSQLDialect