HSQLDB File mode, shutdown=auto on exception?

I was playing with HSQLDB, which is a pure Java database engine. There is lots of articles on the Internet describing this technology and I don't intend to write another one. Instead, I will point out one of the important features and tell you what problem I got with it...

HSQLDB can run in two different modes:

• Listener mode
• Server mode

One thing to mention is that there are several server modes, but that is not so relevant for us, because we are going to focus on the...

HSQLDB Listener Mode

In this mode, you simply have a database at your disposal, but you don't have to run it yourself. HSQLDB will do it for you. There are several ways to achieve this.

1. Specify the connection settings in your hibernate.cfg.xml file.
2. Specify the connection settings in your persistence.xml file.
3. Run HSQLDB Database Manager and ask it to connect to an in-memory engine.
4. And more...

So, as you can see, you don't have to run the database server, you just tell HSQLDB you want to connect to the database and it will be created and launched for you.

This is how you can specify the connection settings in your hibernate.cfg.xml file:

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">
            org.hsqldb.jdbcDriver
        </property>
        <property name="hibernate.connection.url">
            jdbc:hsqldb:file:filedb;shutdown=true
        </property>
        <property name="hibernate.connection.username">
            sa</property>
        <property name="hibernate.dialect">
            org.hibernate.dialect.HSQLDialect
        </property>

(There's more to it, that's not the complete file).

This is how you can run the Database Manager:

java -classpath lib/hsqldb-1.8.0.7.jar
org.hsqldb.util.DatabaseManagerSwing

(Line break added for readability).

I don't have a persistence.xml example here because I haven't tried it yet.

Listener vs. Server

So what's the difference?

In the Server mode you have a server, of course, which means that you can connect to it from outside with different applications.

In the Listener mode this is impossible, your database is somewhat embedded in your application and it's not accessible from the outside. This approach is also faster (no client-server communication is required).

Listener: Memory vs. File

There are two submodes for the listener mode.

• Memory
• File

With the Memory option you have no files, everything is in-memory and nothing is stored. This is useful for testing and caching purposes. And this is the JDBC URL you should use to get this effect:

jdbc:hsqldb:mem:mymemdb

The File option makes it possible to persist the actual results. However, many many people keep running into the same problem - their data is not persisted. This is the solution:

jdbc:hsqldb:file:db\filedb;shutdown=true

shutdown=true

shutdown=true does it. What does it mean? It issues a SHUTDOWN command to the database when all connections are closed. Only that makes sure that your data will be saved. This problem does not occur in the server modes.

But then I realized there might be a problem - and there is a problem indeed.

Is SHUTDOWN issued when an exception is thrown?

In short: no. Look at this pseudocode.

1. Saving an entity in a transaction.
2. An exception is thrown after the transaction.
3. Application exits.

Because operation 1 executes in a transaction, you would expect its result to be persisted even though an exception is thrown later on. And this is how it happens with HSQLDB when you are using server mode (and other databases too). However, in HSQLDB with the File mode on, when an exception occurs, nothing is persisted, whether shutdown=true is on or not.

That's wrong and not desired. Perhaps I'm missing something; I posted a question on the Hibernate Forum.

Workaround

I have only been able to find one workaround. You are able to shutdown the database yourself, programatically.

org.hsqldb.DatabaseManager.closeDatabases(0);

A definitely better way would be to handle it with AOP (because if you just hardcode this call your application becomes tightly coupled with HSQLDB in the Listener File mode) with, for example, Spring Framework. I will try that some other time; meanwhile, my friend Jeroen has recently published a great intro to AOP.

I am not satisfied by this behavior but I guess I will have to live with that for now. I will use the server mode instead.