HSQLDB

HSQLDB is a lightweight Java database engine that has been around since 2001. However, because it is a continuation of Thomas Mueller's closed Hypersonic SQL Project, it has actually been around longer than 2001. In short, the product is fairly mature.

HSQLDB provides a good amount of ANSI-92 SQL-compliant features (and many enhancements from more recent SQL standards)more than we will need in this book. Furthermore, most of the features defined by JDBC 2, and some from JDBC 3, are also supported. HSQLDB's popularity has grown significantly since its inception a few years ago, and it is commonly found bundled with open source and commercial Java-related products such as JBoss, OpenOffice.org, Atlassian's JIRA, and many more.

At the time of this writing, the HSQLDB project was one of the top 50 ranking in more than 100,000 SourceForge.net projects.

HSQLDB can be found at http://hsqldb.org. There are ample setup instructions on this site to download, install, and configure it. I'm using version 1.8.x in this book.

HSQLDB Server and Convenient Ant Tasks

Now we need to start the HSQLDB server and create the database using our DDL file. However, first, let's copy the hsqldb.jar file from the HSQLDB install directory to our lib/ directory; for example, on my Microsoft Windows XP-based system, I typed the following:

copy \hsqldb\lib\hsqldb.jar \anil\rapidjava\timex\lib\

We will use our Ant script, timexhsqldb.xml, to start the server and also to create the database. This file is placed in the top-level directory of our sample application (in my case, this is C:\anil\rapidjava\timex).

Assuming our HSQLDB configuration is set up correctly, we can now type the ant -f timexhsqldb.xml starthsql command to start the HSQLDB server, as demonstrated here:

C:\anil\rapidjava\timex>ant -f timexhsqldb.xml starthsql

From another command window, we can type the ant -f timexhsqldb.xml execddl command to execute our DDL script for creating our database within HSQLDB, as demonstrated here:

C:\anil\rapidjava\timex>ant -f timexhsqldb.xml execddl

Before we move on, let's review parts of timexhsqldb.xml.

The following properties are related to HSQLDB; that is, the hfile property points to a local set of files under the timex/data/ directory, the halias is the alias we will use in our client applications to connect to the HSQLDB server, and the hport is the port number the HSQLDB server will listen to:

<property name="hfile" value="-database.0 data/timexdb"/> <property name="halias" value="timex"/> <property name="hport" value="9005"/>

Next, the starthsql Ant target starts the HSQLDB server using the built-in Ant java task, as shown here:

<java fork="true"       classname="${hclass}" classpath="${hjar}"       args="${hfile} -dbname.0 ${halias} -port ${hport}"/>

The execddl Ant target uses the built-in sql task to execute our SQL DDL script, as shown here:

<sql classpath="${hjar}"      driver="org.hsqldb.jdbcDriver"      url="jdbc:hsqldb:hsql://localhost:${hport}/${halias}"      user password=""      print="yes">

HSQLDB Database Manager and SqlTool

HSQLDB is bundled with two tools you should read about in the HSQLDB documentation: HSQL Database Manager (GUI) and SqlTool (command-line based). These are nice tools for working with our database. Meanwhile, you will find two convenient ant tasks in our timexhsqldb.xml file, hsqldm and sqltool, which can be used to start these two tools. For example, to start HSQL Database Manager, type the following on the command line:

ant -f timexhsqldb.xml hsqldm

After the Database Manager comes up, we can change the following parameters on the screen and work with our database in a GUI fashion, instantly (assuming the HSQLDB server is running in another window):

Type: HSQL Database Engine Server

URL: jdbc:hsqldb:hsql://localhost:9005/timex

HSQLDB Persistent and In-Memory Modes

Be sure to read about the various modes HSQLDB can run in (such as local versus server and in-memory versus persistent); we will use the server and persistent mode. For example, we could also use the very same HSQLDB database files (found under our timex/data/ directory) as follows:

jdbc:hsqldb:file:${catalina.base}/webapps/timex/WEB-INF/data/timexdb

Incidentally, this is a feature that ties in nicely with the next section on bundling HSQLDB in an archive file.

Bundling HSQLDB in a Deployable Archive File

As an added benefit, HSQLDB has a small enough footprint to run entirely in memory. For example, we could deploy our sample application with HSQLDB, bundled in the same web archive (WAR) file, essentially making the WAR file a fully self-contained system with no need for an external database!

Personal Opinion: Data Is the Customer's Most Valuable Asset!

After all my years developing software, I still find that some people miss the whole point of Information Technology (IT). In my words, IT means technology for managing information. Information. As in data (databases).

Data is the customer's asset and hence the single most important component of a system. The data outlives most programs written to use it. This is precisely why the domain model, physical data model, and database refactoring are more important aspects of software development than, for example, cool tools or adding layers of unnecessary abstractions in your code.

When you're designing the database, one important thing to keep in mind is that the database can be used by multiple applications, not just a single, well-designed, object-oriented, n-tier application. For example, querying and reporting tools could also access the database for customer reports. So, as much as possible, the structure of the database should be somewhat independent of a single application. Furthermore, even the original application designed for the database can be retired after a few years, but the database will likely live on for a long time to come on.

For further reading on this matter, visit the agiledata.org website to learn more about database refactoring techniques. You may also want to visit the domaindrivendesign.org website, which is complementary to the AM website. For example, I found this line from an article by Eric Evans on this website, "the complexity that we should be tackling is the complexity of the domain itselfnot the technical architecture, not the user interface, not even specific features."

To summarize, the data is the customer's asset, so focus on getting the domain model and database structure right using a combination of some upfront design and database refactoring as necessary.