Using HyperSQL Database

HSQLDB is a database written in Java that can be embedded in your application or run in client-server mode. HSQLDB is small and fast, faster than Derby. It is included in Grails, Play Framework, and many open-source applications.

Install HSQLDB

  1. Download HSQLDB from It is distributed as a ZIP file.
  2. Unzip the file to a directory where you store software libraries, such as /opt (Linux) or C:\lib (Windows). Don't use a path containing spaces in the name.
  3. Optionally, add the hsqldb/bin directory to your PATH environment variable, to make it easy to run scripts in bin. This isn't required.

HSQLDB Directory Layout and Tools

When you install HSQLDB it will create the following directory structure.

hsqldb-2.x/

bin/Scripts and example HTML pages using HSQLDB.

These scripts aren't very general, they assume your

current directory is the "bin" directory itself.

runManagerSwing.batRun Swing-based database client using "data" dir.

demo/Demo programs with source code, demo databases

doc/

apidocs/Javadoc for API

guide/Users' guide in PDF and HTML formats

util-guide/Guide to utilities such as sqltool

integration/Code for integration with Hibernate, Spring, & JackRabbit

lib/JAR files

hsqldb.jarDatabase engine, JDBC driver, and GUI tools

sqltool.jarCommand line database access tool

sample/Samples using HSQLDB in various languages.

Running the GUI SQL Tools

The SQL tools can be used to connect and interact with any database that has a JDBC driver, not just HSQLDB. There are two GUI tools, using Swing and AWT. The Swing tool has a nicer UI.

cmd> java -cp hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

If you want to connect to a database other than HSQL, you must put that database's JDBC driver on your CLASSPATH before running this command, or add the database's JDBC driver to the -cp argument.

Types of Databases and URLs

HSQLDB can store a database in memory, in files on disk, or as a read-only Java resource.

Database Type / URL
File (embedded mode) / jdbc:hsqldb:file:/path/to/database
In memory / jdbc:hsqldb:mem:databasename
Resource (read-only) / jdbc:hsqldb:res:org.me.app.resource
Connect to HSQLDB server / jdbc:hsqldb:hsql://hostname/database
Connect to server in secure mode / jdbc:hsqldb:hsqls://hostname/database

The JDBC Driver

The JDBC driver class for HSQLDB is org.hsqldb.jdbc.JDBCDriver.

If your program uses JDBC directory, you would write something like:

Class.forName( "org.hsqldb.jdbc.JDBCDriver");

Connection conn =
DriverManager.getConnection("jdbc:hsqldb:file:/path/database","SA","");

If you use JPA, in the persistence.xml file use:

<property name="javax.persistence.jdbc.driver"

value="org.hsqldb.driver.JDBCDriver"/>

<property name="javax.persistence.jdbc.url"

value="jdbc:hsqldb:file:/path/database"/>

<property name="javax.persistence.jdbc.user" value="SA"/>

<property name="javax.persistence.jdbc.password" value=""/>

Starting the Server for Client-Server Mode

To access a database in client-server mode, a server must be running. You don't need to run a server if you are using embedded mode (HSQLDB calls it in-process mode). The command to start a server with only one database (named world) stored in directory /data/world is:

java -cp /opt/hsqldb/lib/hsqldb.jar org.hsqldb.server.Server
-database.0 /data/world -dbname.0 world

The database name (dbname.0) can be different from the directory name in the file system.

In client-server mode the default port is 9001. To change it, use arguments: --port n

For a help message showing server options enter:

java -cp /opt/hsqldb/lib/hsqldb.jar org.hsqldb.server.Server--help

Files in an HSQL Database

HSQLDB creates up to 6 files for a database. It uses the database name you give it as the filename; it doesn't create a separate directory for each database (unlike Derby), so its up to you to specify the directory where files should be created and stored. The files are:

database.propertiesDatabase settings

database.scripttable definitions, definitions of other objects

database.logrecent changes not yet incorporated into data file

database.datatable data

database.backupcompressed backup of data file for last known good state

database.lobslarge objects

Data in the .log file is incorporated into the database (and the log file removed) at normal SHUTDOWN (using a shutdown command). While the database is open, HSQLDB also creates a lock file:

database.lckLock file indicating database is open

Closing a Database

HSQLDB has a SHUTDOWN command to close the database and changes written to the data file. Any uncommitted transactions are rolled back.

The command SHUTDOWN COMPACT closes the database and rewrites the data file to compact it. This command should be used when lots of inserts, updates, and deletes have been performed, and used periodically.

The database is not automatically closed when the last JDBC connection is closed. To specify that the database be closed when last connection is closed add the stutdown=true property to the connection URL, e.g.:

Connection conn = DriverManager.getConnection(
"jdbc>hsqldb:file:/data/mydatabase/world;stutdown=true", "SA", "");

You can issue SHUTDOWN [COMPACT] in the interactive GUI tool or using the execute method of an SQL Statement object.

Statement statement = conn.createStatement( );

statement.execute( "SHUTDOWN" );

Database Catalog and Schema

A HyperSQL database contains one catalog named PUBLIC. By default it also contains one schema named PUBLIC, but you can rename the schema and create additional schema.

Creating a Table, Using Auto-generated Values

CREATE TABLECity (

ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

Name VARCHAR(35) DEFAULT '' NOT NULL,

CountryCode CHAR(3) DEFAULT '' NOT NULL,

District VARCHAR(20) DEFAULT '' NOT NULL,

Population INTEGER DEFAULT 0

);

Other constraints you can place on columns are: UNIQUE, FOREIGN KEY, and CHECK.

The HSQLDB manual describes the options for table creation. Tables can either be held entirely in memory or cached, which allows for larger datasets. (This is not the same as an "mem" type database that is entirely in memory.)

CREATE TABLE MEMORY tablename (... );

CREATE TABLE CACHED tablename ( ... );

The syntax for identity generated values is:

colname [INTEGER | BIGINT | DECIMAL | NUMERIC]

GENERATED { BY DEFAULT | ALWAYS } AS IDENTITY [ '(' option [, option]* ')' ];

where

option := START WITH n | INCREMENT BY m

HSQL requires that "DEFAULT xxx" come before "NOT NULL".

Using Indices

Indices speed up queries and JOINs. An index is created by default for primary keys, foreign keys, and constraints. Don't explicitly create an index for these. For cases where you plan to use a JOIN, you are advised to create an index for the JOIN column of the second table.

If you plan to use:

SELECT .... FROM Table1

JOIN Table2

ON Table1.column1 = Table2.column2

then HSQLDB recommends you create an index for column2 of Table2 using:

CREATE INDEX idx_column2 ON Table2(column2);

Creating the World Database

The following steps will use SQL statements contained in the files City-data.sql, Country-data.sql, and World-schema-hsqldb.sql, so you should download and unzip the world data as instructed in lab (you can probably find a link to it on

1. Create a new directory for the database, so the database files are not comingled with other files. For example,

cmd> mkdir d:\database\hsqldb\world

2. Start the HSQLDB Database Manager tool:

cmd> java -cp hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

3. In the window that appears enter the following:

Type: HSQL Database Engine Standalone

URL: jdbc:hsqldb:file:/database/hsqldb/world/World (use the path you created above)

4. Enter SQL data definition command to create the City table:

CREATE TABLECity (

ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

Name VARCHAR(35) DEFAULT '' NOT NULL,

CountryCode CHAR(3) DEFAULT '' NOT NULL,

District VARCHAR(20) DEFAULT '' NOT NULL,

Population INTEGER DEFAULT 0

);

Click the "Execute" button to execute it. If any errors, then correct them and "Execute" until it executes without error.

5. Choose File -> Script and select the City-data.sql script. Then click "Execute". It should run without error and create 4079 cities.

Clear the SQL window and enter the command:

SELECT count(*) FROM City;

and execute it. The result should be 4079. Verify that the ID are assigned starting from 1 by using:

SELECT TOP 20 * FROM City;

6. Execute the world-schema.sql script to create the other tables.

7. Execute the country-data.sql script to insert data into the Country and CountryLanguage tables.

8. Verify that the Country table contains 239 records and CountryLanguage contains approximately 986 records (answer may differ because of ongoing corrections to CountryLanguage data).

9. Execute a SHUTDOWN COMPACT command to save the data to disk.

Resources

  1. HSQLDB User's Guide included with the HSQLDB distribution is easy to read and the only documentation you need to use HyperSQL.
  2. For SQL, HSQLDB recommends the "PostreSQL: Introduction and Concepts" manual on the PostgreSQL web (

Derby Database- 1 -