HSQLDB is a very simple database to configure as you don't need to install nor to start it before being able to use it. It comes as a single JAR file named hsqldb*.jar. If your XWiki WAR doesn't have it in WEB-INF/lib you'll need to download it and copy it there. You can download it from the HSQLDB web site or directly from the Maven Central Repository.

Once this is done, you need to configure XWiki to use HSQLDB and to tell it where the database files will be created. To do this, edit the WEB-INF/hibernate.cfg.xml file where you have expanded the XWiki WAR file and uncommented the HSQLDB part.

If you intend to use HSQLDB in production you should probably tune it for a production usage. If you've installed the XWiki standalone distribution just be aware that it's not tuned to be production-ready. Also note that you would need to find a way to perform backups, live replication, administration tools etc. We would suggest using a more production-ready database such as MySQL or PostgreSQL if you can.

In addition you should be aware that HSQLDB stores the full database in memory which means you'd need to size your memory accordingly. As an indicator having a wiki with 9 subwikis (default XE pages) takes around 110MB in memory and you'd need to have more than 512MB or you'd quickly get Out of Memory errors when performing some memory-intensive operations (exports, etc).

Other optional ideas

You can use a remote HSQLDB database instead of embedding it. To do this, just use a URL like jdbc:hsqldb:hsql:HOSTNAME... instead of the jdbcc:hsqldb:file:.. URL. The major version of the hsqldb jar file must match the major version that the HSQLDB server is using.

If you want to use your app server facilities to manage your data sources (generally a better strategy), then you must follow these steps:

In hibernate.cfg.xml, use the following settings in place of the HSQLDB block in the sample file. Specifically, do not set the url, user, etc. here and leave the provider_class unset (i.e. use the default value), since XWiki's implementation does not handle container-managed data sources:

the final step is to manage the data source using app server facilities. With Tomcat, for example, you would map the resource linkjcbc/xwikiDs to a container-managed data source using the Admin web app or by editing the file conf/Catalina/*/xwiki.xml (i.e. webapp-name + ".xml").