The HSQLDB jar package consists of several programs:
HSQLDB RDBMS
HSQLDB JDBC Driver
Database Manager (Swing and AWT versions)
Transfer Tool (AWT version)
Query Tool (AWT)
Script Tool
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.
All tools can be run in the standard way for archived Java classes. In the following example the AWT version of the Database Manager, the hsqldb.jar is located in the directory ../lib relative to the current directory.
java -cp ../lib/hsqldb.jar org.hsqldb.util.DatabaseManager
If hsqldb.jar is in the current directory, the command would change to:
java -cp hsqldb.jar org.hsqldb.util.DatabaseManager
The list of main classes for the tools are follows:
org.hsqldb.util.DatabaseManager org.hsqldb.util.DatabaseManagerSwing org.hsqldb.util.Transfer org.hsqldb.util.QueryTool org.hsqldb.util.ScriptTool
Some tools, such as the Database Manager or Script Tool, can use command line arguments or entirely rely on them. You can add the command line argument -? to get a list of available arguments for these tools. Database Manager and Transfer Tool feature a graphical user interface and can be explored interactively.
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 4 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.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 data file contains the data for cached tables
and the backup file is a zipped backup of the last known consistent state of
the cache. 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 SHUDOWN. 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.
When
the engine closes the database at a shutdown, it creates temporary files with
the extension .new which it then renames to those listed
above.
Server modes provide the maximum accessibility. The database engine runs in a JVM and listens for connections from programs on the same computer or other computers on the network. Several different programs can connect to the server and retrieve or update information. Applications programs (clients) connect to the server using the HSQLDB JDBC driver. In all server modes, the server serves only one database that is specified at the time of running the server. If you need to provide connectivity to several databases, you should run several instances of the server each on a different port.
Server modes can use preset properties or command line arguments detailed in hsqlAdvancedGuide.html. There are three server modes, based on the protocol used for communications between the client and 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 "mydb.*".
java -cp ../lib/hsqldb.jar org.hsqldb.Server -database mydb
The command line argument -? can be used to get a list of available
arguments.
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 following:
org.hsqldb.WebServer
The command line argument -? can be used to get a list of available arguments.
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 hsqlServlet.java 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.
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 /doc/src directory of HSQLDB distribution. A common
example is connection to the default port (9001) used for the hsql protocol
on the same machine:
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", "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. Also, see the Advanced Guide for the connection URL when there is more than one database per server instance.
This mode runs the database engine as part of your application program in the same Java Virtual Machine. For some 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 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. The recommended way of using this 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: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:/opt/db/testdb", "sa", "");
Refer to Javadoc for jdbcConnection for more details.
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 using a dot "." as the database file path.
Connection c = DriverManager.getConnection ("jdbc:hsqldb:.", "sa", "");
You can also run a memory only server instance by specifying a dot as the name of the database. This usage is not common and is limited to special applications where the database server is used only for exchanging information between clients.
All databases running in different modes can be closed with the SHUTDOWN command,
issued as an SQL query. In 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. 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 .data
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 are performed on the cached tables. Changes to the structure
of the database, such as dropping or modifying tables or indexes also create
large amounts of unused space that can be reclaimed using this command.
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 hsqlAdvancedGuide.html.
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.
Once a connection is established to a database in any mode, JDBC methods are
used to interact with the database. The Javadoc for jdbcConnection,
jdbcDriver, jdbcDatabaseMetadata, jdbcResultSet,
jdbcStatement and jdbcPreparedStatement list all the
supported JDBC methods together with information that is specific to HSQLDB.
JDBC methods are broadly divided into: connection related methods, metadata
methods and database access methods. The database access methods use SQL commands
to perform actions on the database and return the results either as a Java primitive
type or as an instance of the java.sql.ResultSet class.
You can use Database Manager or other Java database access tools to explore your database and update it with SQL commands. These programs use JDBC internally to submit your commands to the database engine and to display the results in a human readable format.
The SQL dialect used in HSQLDB is as close to the SQL92 and SQL200n standards as it has been possible to achieve so far in a small-footprint database engine. The full list of SQL commands is in hsqlSyntax.html.
HSQLDB supports TEMP tables and three types of persistent tables.
TEMP tables are not written to disk and last only for the lifetime of the Connection object. Each TEMP table is visible only from the Connection that was used to create it; other concurrent connections to the database will not have access to the table.
The three types of persistent tables are MEMORY tables, CACHED tables and TEXT tables.
Memory tables are the default type when the CREATE TABLE command is used. Their
data is held entirely in memory but any change to their structure or contents
is written to the <dbname>.script file. The script file is
read the next time the database is opened, and the MEMORY tables are recreated
with all their contents. So unlike TEMP table, the default, MEMORY tables are persistent.
CACHED tables are created with the CREATE CACHED TABLE command. Only part of their data or indexes is held in memory, allowing large tables that would otherwise take up to several hundred megabytes of memory. Another advantage of cached tables is that the database engine takes less time to start up when a cached table is used for large amounts of data. The disadvantage of cached tables is a reduction in speed. Do not use cached tables if your data set is relatively small. In an application with some small tables and some large ones, it is better to use the default, MEMORY mode for the small tables.
TEXT tables are new to version 1.7.0 and use a CSV (Comma Separated Value) or other delimited text file as the source of their data. You can specify an existing CSV file, such as a dump from another database or program, as the source of a TEXT table. Alternatively, you can specify an empty file to be filled with data by the database engine. TEXT tables are efficient in memory usage as they cache only part of the text data and all of the indexes. The Text table data source can always be reassigned to a different file if necessary. Two commands are needed to set up a TEXT table as detailed in hsqlTextTables.html document.
With memory-only databases (see above), both MEMORY table and CACHED table declarations are treated as declarations for non-persistent memory tables. TEXT table declarations are not allowed in this mode.
HSQLDB supports PRIMARY KEY, NOT NULL, UNIQUE, CHECK and FOREIGN KEY constraints. In addition, it supports UNIQUE or ordinary indexes. This support is fairly comprehensive and covers multi-column constraints and indexes, plus cascading updates and deletes for foreign keys.
HSQLDB creates indexes internally to support PRIMARY KEY, UNIQUE and FOREIGN KEY constraints: a unique index is created for each PRIMARY KEY or UNIQUE constraint; an ordinary index is created for each FOREIGN KEY constraint. Because of this, you should not create duplicate user-defined indexes on the same column sets covered by these constraints. This would result in unnecessary memory and speed overheads. See the discussion in hsqlSQLGuide.html for more information.
Indexes are crucial for adequate query speed. When queries joining multiple tables are used, there must be an index on each joined column of each table. When range or equality conditions are used e.g. SELECT ... WHERE a >10 AND b = 0, an indexes is required on column used in the condition. Indexes have no effect on LIKE conditions or ORDER BY clauses.
As a rule of thumb, HSQLDB is capable of processing queries and returning over 100,000 rows per second. Any query that runs into several seconds should be checked and indexes should be added to the relevant columns of the tables if necessary.
The SQL syntax supported by HSQLDB is essentially that specified by the SQL Standard (92 and 200n). Not all the features of the Standard are supported and there are some proprietary extensions. In 1.7.2 the behaviour of the engine is far more compliant with the Standards than with older versions. The main changes are
The supported commands are listed in hsqlSyntax.html . For a well written basic guide to SQL you can consult PostgreSQL: Introduction and Concepts by Bruce Momjian, which is available on the web. Most of the SQL coverage in the book applies also to HSQLDB. There are some differences is keywords supported by one and not the other engine (ALL, ANY, OUTER, OID's, etc.) or used differently (IDENTITY/SERIAL, LIMIT, TRIGGER, SEQUENCE, etc.).
In 1.7.2, support for JDBC2 has been significantly extended and some features of JDBC3 are also supported. The relevant classes are thoroughly documented. See the JavaDoc for org.hsqldb.jdbcXXXX classes.
Author: Fred Toussi - 14 July 2002 - updated 01 Feb 2004
Copyright 2002-2003 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQLDB Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.