SE452: Setting up hsqldb [4/18]

Hsqldb is a very simple database, but the documentation can be a bit confusing. The best source for complete documentation is the web site, at http://hsqldb.sourceforget.net. Download the 1.71 zip file. It contains documentation, but the docs on the website are better and more complete.

Basically, all you need is a single zip file (hsqldb.jar). You place that in the $CATALINA_HOME/common/lib directory, and it is now available for Tomcat to setup your database connections and for you to access them via JNDI. However, you may want to use some of the utilities available with hsqldb.

To run a utility, there is a script in the hsqldb/bin directory of the zip file. This script can start any of the utility programs that come with hsqldb. The ones of note are the Database Manager and the Script tool. The Database Manager is a GUI tool that you can use to test your queries, look at data, and configure your database. To run it, just execute runUtil DatabaseManager. This utility will ask you for the Driver, url, username, and password for the database. With hsqldb, it is just sa (for systems administrator), and "" (the empty string - don't type anything into the GUI) for the password.

Hsqldb operates in three major modes:

• in memory
• standalone (in process)
• server

In memory is the default, but it doesn't persist data to a file. Once you exit the tool, the data is gone. This is really useful for testing code, but also means you have to create your tables every time. The url for in memory would be jdbc:hsqldb:.

Standalone will only allow one user to connect at a time. This is fine for testing, and is ok for a JNDI setting since you will only be a single user logging in at a time. However, if you do this, you will not be able to run the Database Manager and Tomcat against the db at the same time. The url for standalone is jdbc:hsqldb:c:\temp\test where c:\temp\ is the directory, and test is the database name. You can store your database anywhere you want.

Server mode allows multiple users. You need to start the server in a separate window, then start Tomcat (or any other connection to the db, such as the DatabaseManager). This is the most robust (and fastest) mode. To do this, you need to have hsqldb in your classpath, and you run the command java org.hsqldb.Server -port 9001 -database c:\temp\test where the database is the same as above. The url for the server would be jdbc:hsqdlb:hsql://localhost:9001 for the above database server.