Setup HSQLDB on MAC

A few days ago, I was involved in developing a practice for a University project where a Java application had to be built that communicated with an SQL instance. There is not much problem using standard SQL such as mySQL, MariaDB or PostgreSQL, but the export is a bit cumbersome. Doing some research on the Internet I found this wonderful HyperSQL solution, it is a functional Java based SQL database solution that you can easily save to your git repository like the rest of your application.

Let me detail step by step how we proceed with the installation and later I will publish some tricks.

Hsqldb Server

This is the preferred way of running a database server and the fastest one. A proprietary communications protocol is used for this mode. A command similar to those used for running tools and described above is used for running the server. The following example of the command for starting the server starts the server with one (default) database with files named "mydb.*".

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

The command line argument -? can be used to get a list of available arguments.

Note that both "xdb" is used for both File and database name.

   Running Tools

All tools can be run in the standard way for archived Java classes. In the following example the AWT version of the Database Manager, the hsqldb.jar is located in the directory ../lib relative to the current directory.

 java -cp ../lib/hsqldb.jar org.hsqldb.util.DatabaseManager

If hsqldb.jar is in the current directory, the command would change to:

java -cp hsqldb.jar org.hsqldb.util.DatabaseManager

Main classes for the Hsqldb tools

• org.hsqldb.util.DatabaseManager
• org.hsqldb.util.DatabaseManagerSwing
• org.hsqldb.util.Transfer
• org.hsqldb.util.QueryTool
• org.hsqldb.util.SqlTool

Use these setting in the connection to setting to run the server.



Start the DB Manager Swing application

HSQL needs to be setup in JPA persistence.xml to perform connections.

<persistence-unit name="Stocks" transaction-type="RESOURCE_LOCAL">
<non-jta-data-source>nJTA</non-jta-data-source>
        <class>com.larebelion.db.stock</class>
        <properties>
        <!-- jdbc:hsqldb:mdb -->
        <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:hsql://localhost/mdb" />
        <property name="javax.persistence.jdbc.user" value="SA"/>
        <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver"/>
        <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema(SchemaAction='add', ForeignKeys=true)"/>
<property name="openjpa.Log" value="DefaultLevel=WARN, Runtime=INFO, Tool=INFO, SQL=TRACE"/>        
     <property name="openjpa.jdbc.DBDictionary" value="hsql" />
<property name="openjpa.jdbc.Schema" value="PUBLIC" />
        </properties>   
    </persistence-unit> 
</persistence>