A quick-switch alternative for HSQLDB file mode (I need concurrent access now)

Question

I was happily working with HSQLDB by just having my DB URL like this:

jdbc:hsqldb:file:target/testdb;

...and having everything like if I was on any other network database. Then suddenly one requirement punched me direct in the head, I need another JVM instance (in the same machine) to connect to the database to generate some reports.

I've read about Derby and Berkeley DB but it seems I'd need to set up some env vars and my customer asked me for programming this system without any special configuration (no env vars should be created).

I thought about having an independent thread to start HSQL in server mode in parallel but I'd rather like to use another DB engine which be as simple as HSQLDB but with file-mode concurrent access support. Hope you guys know about a straight forward alternative for solving my problem

Answer 1

I'd suggest you consider the set of possible JDBC solutions -- specifically, consider the solutions that will allow to programs to share access to data:

1. Shared access to the same file
2. Individual sockets/connections to a DBMS
3. Message passing between the two programs

Since you want JDBC support out of the box (i.e. you don't want to implement anything lower than the JDBC layer), I think #3 and #1 are out of the running. That leaves you with #2.

Is there any reason you think that starting HSQL in a separate thread makes your JDBC calls less simple? Where HSQL runs doesn't affect the interface for accessing it.

Answer 2

SQLite might be a good option. See this SO question.

However, I think running HSQL in server mode is probably the easiest option. I used it that way years ago and found it robust enough for basic concurrent access, and you will only need to change your url in your existing code.

Answer 3

Rather than having another database, migrate and sync the data between your in proc database, you're better off running HSQL in server mode in a separate JVM. Here's how to