Setting up HSQL 2.0 database

I am going to take a file based HSQL database, mentioning that is
important since HSQL can work as a in memory database too.
An easy way to start multiple hsql databases is by defining then in the
server.properties file. The content for it should look like:

server.database.0=file:./database/mydb
server.dbname.0=mydb

Note that you can defined multiple databases like this with increasing
indexes.
The scripts and properties files for the database are inside the
'database' folder i.e. mydb.script and mydb.properties.
Lets say if you are starting from scratch then, the content for them can
be copied from any test.script and test.properties file.
And these test files are generated automatically when you don't defined
a db to HSQL while starting it.

Configuring HSQL datasources in jetty for j2ee web applications

First an entry has to be put in the WEB-INF/web.xml file


  <resource-ref>
    <res-ref-name>jdbc/mydb</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

Then for jetty we can use a special config file, which is looked up in every j2ee application deployed in jetty
i.e. jetty-env.xml . We put this in WEB-INF/jetty-env.xml of the webapp. The content of files look like


<Configure id="wac" class="org.eclipse.jetty.webapp.WebAppContext">
<New id="mydb" class="org.eclipse.jetty.plus.jndi.Resource">
<Arg></Arg>
<Arg>jdbc/mydb</Arg>
<Arg>
<New class="org.apache.commons.dbcp.BasicDataSource">
<Set name="driverClassName">org.hsqldb.jdbc.JDBCDriver</Set>
<Set name="url">jdbc:hsqldb:hsql://localhost/mydb</Set>
<Set name="username">SA</Set>
<Set name="password">PASSWORD</Set>
</New>
</Arg>
</New>
</Configure>

Please note that jetty is not a full j2ee container. We have to explicitly change the jetty configuration files in order to enable jndi lookups.