Using HSQL in OpenLiberty

Published August 23, 2022.

Written by Maarten Mulders , reading time roughly 2 minutes (229 words).

Posted in java , open-liberty , web-development , jakarta-ee , microprofile

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">
       <fileset>
           <dir>/path/to/where/you/put/your/downloaded/file</dir>
           <include>*jar</include>
       </fileset>
   </library>
   

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" />
   </dataSource>
   

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.