Multiple Java Applications accessing one HSQLDB causes app to hang

Question

This is semi-related to my previous question. As that previous question states, I have a desktop app that calls off to a different Main method that will kick off a particular process. Both the desktop app and the separate Main method will access the same HSQLDB database.

Prior to getting this far, my desktop app had just been accessing the HSQLDB database using a connection URL like this one:

jdbc:hsqldb:file:/some/path/myDatabase

Now this works fine in a single user environment. Now that I've got a multi-user environment with the desktop app and the separate Main process wanting to read/write to/from this database, I wanted to make this database a shared resource.

I've had a look at the HSQLDB documentation and this post about Creating a shared HSQLDB database but to no avail.

In the post it talks about starting up the server via code. I don't think this is what I want to do as I would like to have the HSQLDB database up and running all the time as there could be multiple users of the desktop app.

Looking at the official HSQLDB documentation, it states that you can start an HSQLDB server like this:

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

If I run the above command with my own database file and name, it seems to start up okay:

[Server@6ca1c]: [Thread[main,5,main]]: checkRunning(false) entered
[Server@6ca1c]: [Thread[main,5,main]]: checkRunning(false) exited
[Server@6ca1c]: Startup sequence initiated from main() method
[Server@6ca1c]: Loaded properties from [/some/path/myDatabase/server.properties]
[Server@6ca1c]: Initiating startup sequence...
[Server@6ca1c]: Server socket opened successfully in 16 ms.

I then changed my connection URL to the following:

jdbc:hsqldb:hsql://localhost/xdb

This does not seem to work for me. Any ideas as to what I've done wrong or what I could be missing?

Furthermore, when the desktop app calls off to the external Main method the desktop app just hangs as the external Main method notices that the desktop app seems to have a lock on the database. As soon as I shut down the desktop app, the external Main method actually does what I expected it to do.

Answer 1

using jdbc:hsqldb:file:/some/path/myDatabase you are creating a in-process database so this should not be shared between multiple JVM (ideally), however there is a way as per guide

"In 1.8.0, you can run a server instance in a thread from the same virtual machine as your application and provide external access to your in-process database."

imho, better way would be to start in server mode in separate jvm.

update:

check you log when the hsqlsdb server is starting up:

[Server@83cc67]: Initiating startup sequence...
[Server@83cc67]: Server socket opened successfully in 31 ms.
[Server@83cc67]: Database [index=0, id=0, db=file:test, alias=] opened sucessfully in 250 ms.

match the alias name in the driver url

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

in this case there was no alias. I just tested this and it works fine.

Answer 2

The answer by @kunal is correct. But you have a server.properties file which may contain the settings below:

server.database.0 file:mydb 
server.dbname.0 xdb

Use either the server.properties file for settings, or alternatively the command line. Do not use both.

Once you have started the server, the desktop app can no longer connect to the database with the jdbc:hsqldb:file:/some/path/myDatabase URL. It needs to connect with the jdbc:hsqldb:hsql://localhost/xdb URL.

HSQLDB has a generic solution for invoking the main method for a different process. Use this class to invoke any process MainInvoker class source

Answer 3

I had a similar problem, one application writes into the hsqldb and another one reads from it.

I've solved everything without using Server, all I used were some configurations found here.

I added following properties:

• application which writes:
  • readonly = false
  • hsqldb.lock_file = false
• application which only reads:
  • readonly = true