Chapter 1. Running and Using HyperSQL

The HSQLDB jar package is located in the /lib directory of the ZIP package and contains several components and programs.

The HyperSQL RDBMS and JDBC Driver provide the core functionality. An additional jar contains Sql Tool (command line database access tool). SqlTool and the DatabaseManagers are general-purpose database tools that can be used with any database engine that has a JDBC driver.

The tools are used for interactive user access to databases, including creation of a database, inserting or modifying data, or querying the database. All tools are run in the normal way for Java programs. In the following example the Swing version of the Database Manager is executed. The hsqldb.jar is located in the directory ../lib relative to the current directory.

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

If hsqldb.jar is in the current directory, the command would change to:

java -cp hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

When a tool is up and running, you can connect to a database (may be a new database) and use SQL commands to access and modify the data.

Tools can use command line arguments. You can add the command line argument --help to get a list of available arguments for these tools.

Double clicking the HSQLDB jar will start the DatabaseManagerSwing application.

Each HyperSQL database is called a catalog. There are three types of catalog depending on how the data is stored.

All-in-memory, mem: catalogs can be used for test data or as sophisticated caches for an application. These databases do not have any files.

A file: catalog consists of between 2 to 6 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:

The properties file contains a few 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 compressed backup of the last known consistent state of the data file. All these files are essential and should never be deleted. For some catalogs, the test.data and test.backup files will not be present. In addition to those files, a HyperSQL database may link to any formatted text files, such as CSV lists, anywhere on the disk.

While the "test" catalog is open, 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.

Note

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. These files should not be deleted by the user. At the time of the next startup, all such files will be deleted by the database engine. In some circumstances, a test.data.xxx.old is created and deleted afterwards by the database engine. The user can delete these test.data.xxx.old files.

A res: catalog consists of the files for a small, read-only database that can be stored inside a Java resource such as a ZIP or JAR archive and distributed as part of a Java application program.

In general, JDBC is used for all access to databases. This is done by making a connection to the database, then using various methods of the java.sql.Connection object that is returned to access the data. Access to an in-process database is started from JDBC, with the database path specified in the connection URL. For example, if the file: 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 directory in Linux is /opt/db/ containing a database testdb (with files named testdb.*), then the database file path is /opt/db/testdb. If 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 the Javadoc for JDBCConnection for more details.

Paths and database names for file databases are treated as case-sensitive when the database is created or the first connection is made to the database. But if a second connection is made to an open database, using a path and name that differs only in case, then the connection is made to the existing open database. This measure is necessary because in Windows the two paths are equivalent.

A mem: database is specified by the mem: protocol. For mem: databases, the path is simply a name. Several mem: databases can exist at the same time and distinguished by their names. In the example below, the database is called "mymemdb":

  Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:mymemdb", "SA", "");

A res: database, is specified by the res: protocol. As it is a Java resource, the database path is a Java URL (similar to the path to a class). In the example below, "resdb" is the root name of the database files, which exists in the directory "org/my/path" within the classpath (probably in a Jar). A Java resource is stored in a compressed format and is decompressed in memory when it is used. For this reason, a res: database should not contain large amounts of data and is always read-only.

  Connection c = DriverManager.getConnection("jdbc:hsqldb:res:org.my.path.resdb", "SA", "");

The first time in-process connection is made to a database, some general data structures are initialised and a few helper threads are started. After this, creation of connections and calls to JDBC methods of the connections execute as if they are part of the Java application that is making the calls. When the SQL command "SHUTDOWN" is executed, the global structures and helper threads for the database are destroyed.

Note that only one Java process at a time can make in-process connections to a given file: database. However, if the file: database has been made read-only, or if connections are made to a res: database, then it is possible to make in-process connections from multiple Java processes.

For most applications, in-process access is 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.

Server modes provide the maximum accessibility. The database engine runs in a JVM and opens one or more in-process catalogs. It listens for connections from programs on the same computer or other computers on the network. It translates these connections into in-process connections to the databases.

Several different programs can connect to the server and retrieve or update information. Applications programs (clients) connect to the server using the HyperSQL JDBC driver. In most server modes, the server can serve an unlimited number of databases that are specified at the time of running the server, or optionally, as a connection request is received.

A Sever mode is also the preferred mode of running the database during development. It allows you to query the database from a separate database access utility while your application is running.

There are three server modes, based on the protocol used for communications between the client and server. They are briefly discussed below. More details on servers is provided in the HyperSQL Network Listeners (Servers) chapter.

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

  org.hsqldb.server.WebServer

  try {
      Class.forName("org.hsqldb.jdbc.JDBCDriver" );
  } catch (Exception e) {
      System.err.println("ERROR: failed to load HSQLDB JDBC driver.");
      e.printStackTrace();
      return;
  }

  Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb", "SA", "");

  Connection c = DriverManager.getConnection("jdbc:hsqldb:http://localhost/xdb", "SA", "");

  Connection c = DriverManager.getConnection("jdbc:hsqldb:hsqls://localhost/xdb", "SA", "");
  Connection c = DriverManager.getConnection("jdbc:hsqldb:https://localhost/xdb", "SA", "");

As shown so far, a java.sql.Connection object is always used to access the database. But the speed and performance depends on the type of connection.

Establishing a connection and closing it has some overheads, therefore it is not good practice to create a new connection to perform a small number of operations. A connection should be reused as much as possible and closed only when it is not going to be used again for a long while.

Reuse is more important for server connections. A server connection uses a TCP port for communications. Each time a connection is made, a port is allocated by the operating system and deallocated after the connection is closed. If many connections are made from a single client, the operating system may not be able to keep up and may refuse the connection attempt.

A java.sql.Connection object has some methods that return further java.sql.* objects. All these objects belong to the connection that returned them and are closed when the connection is closed. These objects can be reused, but if they are not needed after performing the operations, they should be closed.

A java.sql.DatabaseMetaData object is used to get metadata for the database.

A java.sql.Statement object is used to execute queries and data change statements. A java.sql.Statement can be reused to execute a different statement each time.

A java.sql.PreparedStatement object is used to execute a single statement repeatedly. The SQL statement usually contains parameters, which can be set to new values before each reuse. When a java.sql.PreparedStatement object is created, the engine keeps the compiled SQL statement for reuse, until the java.sql.PreparedStatement object is closed. As a result, repeated use of a java.sql.PreparedStatement is much faster than using a java.sql.Statement object.

A java.sql.CallableStatement object is used to execute an SQL CALL statement. The SQL CALL statement may contain parameters, which should be set to new values before each reuse. Similar to java.sql.PreparedStatement, the engine keeps the compiled SQL statement for reuse, until the java.sql.CallableStatement object is closed.

A java.sql.Connection object also has some methods for transaction control.

The commit() method performs a COMMIT while the rollback() method performs a ROLLBACK SQL statement.

The setSavepoint(String name) method performs a SAVEPOINT <name> SQL statement and returns a java.sql.Savepoint object. The rollback(Savepoint name) method performs a ROLLBACK TO SAVEPOINT <name> SQL statement.

The Javadoc for JDBCConnection, JDBCDriver, JDBCDatabaseMetadata JDBCResultSet, JDBCStatement, JDBCPreparedStatement list all the supported JDBC methods together with information that is specific to HSQLDB.

All databases running in different modes can be closed with the SHUTDOWN command, issued as an SQL statement.

When SHUTDOWN is issued, all active transactions are rolled back. The catalog files are then saved in a form that can be opened quickly the next time the catalog is opened.

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 its minimum 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.

Databases are not closed when the last connection to the database is explicitly closed via JDBC. 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.

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

This feature is useful for running tests, where it may not be practical to shutdown the database after each test. But it is not recommended for application programs.

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.

With HyperSQL 2.0 the username and password that are specified for the connection are used for the new database. Both the username and password are case-sensitive. (The exception is the default SA user, which is not case-sensitive). If no username or password is specified, the default SA user and an empty password are used.

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.

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

A database has many optional properties, described in the System Management chapter. You can specify most of these properties on the URL or in the connection properties for the first connection that creates the database. See the Properties chapter.