Jonathan Lalou's blog

Posts Tagged ‘HSQLDB’

Case

I got the project described in MK Yong’s website. This projects is a sample code of JSF + Spring + Hibernate. The laying DB is a MySQL. For many reasons, I’d rather not to detail, I prefered to a HSQLDB instead of the MySQL.

(Notice: the zip you can download at MK Yong’s website contains many errors, not related to the persistance layer but to JSF itself.)

How to migrate any project from MySQL to HSQLDB?

Solution

You have to follow these steps:

Maven

In the pom.xml, replace:

<!-- MySQL database driver -->
        <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.9</version>
        </dependency>

with:

        <!-- driver for HSQLdb -->
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.2.8</version>
        </dependency>

By the way, you can add Jetty plugin to have shorter development cycles:

            <plugin>
                <groupId>org.mortbay.jetty</groupId>
                <artifactId>maven-jetty-plugin</artifactId>
                <configuration>
                    <webApp>${basedir}/target/jsf.war</webApp>
                    <port>8088</port>
                </configuration>
            </plugin>

Persistence

Properties

Replace the content of db.properties file with:

jdbc.driverClassName=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:hsql://localhost:9001
jdbc.username=sa
jdbc.password=

Hibernate Mapping

In the *.hbm.xml files:

• in the tag <class>, remove the attribute catalog="..."
• replace the types with fully qualified object types, eg:
  • long with java.lang.Long,
  • string with java.lang.String,
  • timestamp with java.util.Date,
  • etc.

Hibernate Properties

For the property of key hibernate.dialect, replace the value: org.hibernate.dialect.MySQLDialectorg.hibernate.dialect.HSQLDialect with the value: org.hibernate.dialect.HSQLDialect.
To match my needs, I tuned Hibernate properties a bit more, but it may not be needed in all situations:

        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hbm2ddl.auto">create-drop</prop>
                <prop key="hibernate.hbm2ddl.auto">create-drop</prop>
                <prop key="connection.pool_size">1</prop>
                <prop key="current_session_context_class">thread</prop>
                <prop key="cache.provider_class">org.hibernate.cache.NoCacheProvider</prop>
            </props>
        </property>

Run

Run the HSQLDB server. IMHO, the quickest is to run the following Maven command:

mvn exec:java -Dexec.mainClass="org.hsqldb.Server"

But you may prefer the old school java -cp hsqldb-XXX.jar org.hsqldb.Server ;-).

Tip! To get a GUI to check the content of the DB instance, you can run:

mvn exec:java -Dexec.mainClass="org.hsqldb.util.DatabaseManager"

Then build and launch Jetty:

mvn clean install jetty:run-exploded

Here you can see the great feature of HSQLDB, that will allow you to create, alter and delete tables on the fly (if hibernate.hbm2ddl.auto is set to create-drop), without any SQL scripts, but only thanks to HBM mapping files.