Using HSQL in OpenLiberty

A quick note, this time. Recently, I wanted to do some hacking around on Jakarta EE and MicroProfile. I chose to work with the OpenLiberty runtime, as I previously had good experiences with it. My pet project also needs a database, so for starters, I chose HyperSQL Database (HSQL DB). Here’s how I set it up.

As I soon found out, the OpenLiberty documentation doesn’t mention HSQL DB at all. They have a large list of supported, “commonly used vendor databases” - but HyperSQL simply isn’t one of them.

But luckily, if your database isn’t listed there, all is not lost. It’s more a matter of “reading between the lines”.

There’s a couple of things to do:

  1. Grab a copy of of the HyperSQL database JAR-file, and place it somewhere on your machine.
  2. Tell OpenLiberty you have a JDBC driver for HyperSQL by adding this snippet to your server.xml:
    <library id="hsql-jdbc">
  3. Declare your datasource by adding this snippet to your server.xml:
    <dataSource id="my-datasource" jndiName="jdbc/my-ds">
        <jdbcDriver libraryRef="hsql-jdbc"
                    javax.sql.ConnectionPoolDataSource="org.hsqldb.jdbc.pool.JDBCPooledDataSource" />
        <properties URL="jdbc:hsqldb:mem:mydatabase"
                    user="sa" />

And you’re good to go!

The missing link (with my first attempt) was the second attribute to the dataSource element. I didn’t know you could (in this case should) specifiy it. Omitting it means OpenLiberty doesn’t know what the database-specific implementation of ConnectionPoolDataSource is, and hence fails to create the databasource.