Create, populate and reset a dynamic database (HSQLDB,Hibernate,SQLMaven,DbUnit)

Thought I'd jot down how I create, populate and reset databases for development and testing. I use this method in my Java based pet projects such as Snaps ^[app][code] and Wishlist ^[app][code]. I also include this setup as default in my project template.

My projects are Maven based, using Jetty as java container through its maven plugin. In addition I also use JRebel, IntelliJ IDEA and Ubuntu, a setup I described in this howto: Ubuntu + IntelliJ + Maven + Jetty + JRebel, but none of these are required.

This setup is enables a fluid, very dynamic and quick development process. In addition I then use the in memory database HSQLDB for quick and standalone database interaction.

Using JPA/Hibernate with hsqldb, my tables can be created automatically when I start Jetty. However in developement and testing I prefer to have some default data pre populated.

This is where DbUnit comes in to play. With DBunit's maven plugin I can export my current data and populate future databases with the same data. As it is all easy to read XML I can edit manually the basic stub data as well.

However DbUnit can not create the database as it is run before jetty:run action, so I use the SQL-Maven plugin for this purpose. Again SQL Maven needs to know what tables to create, so I use the Maven Hibernate3 Plugin to export a schema from the JPA annotations.


This combination allows me to:

1. Check out my project anywhere


2. Create and populate the database with stub data with one command.


3. Run the application via jetty


4. Test the application straight away


5. or develop dynamically with instant feedback

How to set up Hibernate, SQLMaven and DbUnit plugins

Prerequisites/assumptions

• Java


• Maven


• Jetty (can be tomcat maven plugin)


• JPA annotations


• HSQLDB (using file based hsqldb persistance to survive restarts)

JPA annotations -> Schema: Hibernate

To export the JPA annotations into a database schema we include the Maven Hibernate3 Plugin in your pom.xml. This is quite a long plugin section as it defines a few dependencies which otherwise may be overriden by the plugin and lead to problems like this mapping exception.


<profile>
  <id>hbm-export</id>
  <build>
    <plugins>
      <plugin>
        <groupId>org.codehaus.mojo</groupId>
   <artifactId>hibernate3-maven-plugin</artifactId>
        <version>2.2</version>
        <executions>
          <execution>
            <phase>process-classes</phase>
            <goals>
              <goal>hbm2ddl</goal>
            </goals>
          </execution>
        </executions>
        <configuration>
          <components>
            <component>
              <name>hbm2ddl</name>
      <implementation>jpaconfiguration</implementation>
            </component>
          </components>
          <componentProperties>
    <persistenceunit>${project.artifactId}</persistenceunit>
            <outputfilename>schema.ddl</outputfilename>
            <drop>false</drop>
            <create>true</create>
            <export>false</export>
            <format>true</format>
          </componentProperties>
        </configuration>
        <dependencies>
          <dependency>
            <groupId>hsqldb</groupId>
       <artifactId>hsqldb</artifactId>
            <version>${hsqldb.version}</version>
          </dependency>
          <dependency>
            <groupId>org.hibernate</groupId>
       <artifactId>hibernate-entitymanager</artifactId>
            <version>${hibernate.version}</version>
            <exclusions>
              <exclusion>
                <groupId>cglib</groupId>
         <artifactId>cglib</artifactId>
              </exclusion>
              <exclusion>
                <groupId>commons-logging</groupId>
    <artifactId>commons-logging</artifactId>
              </exclusion>
            </exclusions>
          </dependency>
          <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
            <exclusions>
              <exclusion>
                <groupId>cglib</groupId>
          <artifactId>cglib</artifactId>
              </exclusion>
              <exclusion>
                <groupId>commons-logging</groupId>
                <artifactId>commons-logging</artifactId>
              </exclusion>
            </exclusions>
          </dependency>
          <dependency>
            <groupId>org.hibernate</groupId>
      <artifactId>hibernate-annotations</artifactId>
            <version>${hibernate.version}</version>
            <exclusions>
              <exclusion>
                <groupId>cglib</groupId>
                <artifactId>cglib</artifactId>
              </exclusion>
              <exclusion>
                <groupId>commons-logging</groupId>
        <artifactId>commons-logging</artifactId>
              </exclusion>
            </exclusions>
          </dependency>
        </dependencies>
      </plugin>
    </plugins>
  </build>
</profile>


To run the schema creation action on its own:

mvn -DskipTests \

-Dhibernate.dialect=org.hibernate.dialect.HSQLDialect \

-P hbm-export compile hibernate3:hbm2ddl;

Note: We need to compile the JPA classes so that the plugin can extract annotation information from them.

Note2: that Hibernate/Red Hat have a bad track record of including their plugins and releases properly in maven central repository, so you might either want to adjust your dependeinces and repositories accordingly. Or you can use my proxy repository:


<repositories>
  <repository>
    <id>code-flurdy-repo</id>
    <name>code@flurdy repository</name>
    <url>http://code.flurdy.com/nexus/content/groups/noncentral</url>
    <releases><enabled>true</enabled></releases>
    <snapshots><enabled>true</enabled></snapshots>
  </repository>
</repositories>
<pluginRepositories>
  <pluginRepository>
    <id>code-flurdy-repo</id>
    <name>code@flurdy repository</name>
    <url>http://code.flurdy.com/nexus/content/groups/noncentral</url>
    <releases><enabled>true</enabled></releases>
    <snapshots><enabled>false</enabled></snapshots>
  </pluginRepository>
</pluginRepositories>


Please if your project is popular do not use my repo as it will explode my ec2/S3 data bandwidth usage!

Schema -> Database: SQLMaven

To create your database from this schema with SQL-Maven, add this to you pom.xml


<profile>
  <id>sqlmaven</id>
  <build>
    <plugins>
      <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>sql-maven-plugin</artifactId>
        <version>1.3</version>
        <dependencies>
          <dependency>
            <groupId>hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>${hsqldb.version}</version>
          </dependency>
        </dependencies>
        <configuration>
          <driver>${db.driverClassName}</driver>
          <url>${db.url}</url>
          <username>${db.username}</username>
          <password>${db.password}</password>
          <autocommit>true</autocommit>
     <srcFiles>
     <srcFile>target/hibernate3/sql/schema.ddl</srcFile>
          </srcFiles>
        </configuration>
      </plugin>
    </plugins>
  </build>
</profile>


To run the database creation action on its own:

mvn -DskipTests -P sqlmaven sql:execute;

Database population: DbUnit

To populate your database via DbUnit, add this to you pom.xml


<profile>
  <id>dbunit</id>
  <build>
    <plugins>
      <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>dbunit-maven-plugin</artifactId>
        <version>1.0-beta-3</version>
        <dependencies>
          <dependency>
            <groupId>hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>${hsqldb.version}</version>
          </dependency>
        </dependencies>
        <configuration>
          <dataTypeFactoryName>org.dbunit.ext.hsqldb.HsqldbDataTypeFactory</dataTypeFactoryName>
          <url>jdbc:hsqldb:file:${project.basedir}/target/db/build;shutdown=true</url>
          <driver>org.hsqldb.jdbcDriver</driver>
          <username>sa</username>
          <password></password>
          <type>CLEAN_INSERT</type>
          <src>src/test/data/stub.xml</src>
        </configuration>
      </plugin>
    </plugins>
  </build>
</profile>


First you need data to export. I suggest you run jetty:run action and add/use the application to create data in the database. Eg.: Register a user, create default domain objects, or whatever your application uses.

You then stop the application and export the data created:

mvn -DskipTests -P dbunit dbunit:export;

These are by default exported to target/dbunit/export.xml.

These are a good base data. You may try and adjust and extend this file if you like, but be aware that you might brake it, so prehaps try unadjusted initially.

Copy the export file to test/data so that it can be part of your source control etc. You may have noticed I already included the test/data as a src element in the plugin.

cp target/dbunit/export.xml test/data/stub.xml;

Run the database population action (remember to have a new clean database):

mvn -DskipTests -P dbunit dbunit:operation;


Example configuration of all these plugins can be found in this project's pom.xml.

Combined

Above are the basic steps. These I then aggregate into a couple of common one liners:

Create database:

mvn -o -DskipTests \

-Dhibernate.dialect=org.hibernate.dialect.HSQLDialect \

-P hbm-export,sqlmaven \

compile hibernate3:hbm2ddl sql:execute;



Create and populate database:

mvn -o -DskipTests \

-Dhibernate.dialect=org.hibernate.dialect.HSQLDialect \

-P hbm-export,sqlmaven,dbunit \

compile hibernate3:hbm2ddl \

sql:execute dbunit:operation;



Reset database:

rm -rf target/db;

mvn -o -DskipTests \

-Dhibernate.dialect=org.hibernate.dialect.HSQLDialect \

-P hbm-export,sqlmaven,dbunit \

compile hibernate3:hbm2ddl \

sql:execute dbunit:operation;



Clean, rebuild database and run jetty:

mvn -o -DskipTests \

-Dhibernate.dialect=org.hibernate.dialect.HSQLDialect \

-P hbm-export,sqlmaven,dbunit \

clean compile hibernate3:hbm2ddl \

sql:execute dbunit:operation jetty:run;

As you can see these one liners can be quite long so I again wrap these into bash scripts that I put in a bin folder.

You can extend this further to include these plugins as enabled by default and the maven goals as part of your other goals: E.g. include hbm:export and sqlmaven as part of install or test command or dbunit:operation with the jetty:run command, so that they are totally automatic. However I prefer a bit more control of when my data is reset etc.

Summary

With these plugins I can in one command create and populate my database, so that my app is up and running with data very quickly. Hope this is of use to others.