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.

org.hibernate.MappingException: Could not determine type for Set / List

I was simplifying the domain model of a pet project by removing an unneccesarry entity by using JPA 2.0's ElementCollection annotation:

public class SecurityDetail {

public enum AuthorityRole{
  ROLE_ADMIN,
  ROLE_SUPER,
  ROLE_USER
}
....
@ElementCollection
@CollectionTable(
  name="Authority",
  joinColumns=@JoinColumn(name="username")
)
private Set authorities;
....

However I was getting this error message:
org.hibernate.MappingException: Could not determine type for: java.util.Set, at table: SecurityDetail, for columns: [org.hibernate.mapping.Column(authorities)]


Digging around (googling) I ended up on this blog entry: blog.m1key.me/2010/06/orghibernatemappingexception-could-not.html, which main solution is to make sure you have a newer hibernate, version 3.5.3 or later.


I use maven, with the hibernate3-maven-plugin, sql-maven-plugin and dbunit-maven-plugin to create the schema, database and to populate data. So I bumped the org.hibernate:hibernate-entitymanager dependency from 3.5.0 to 3.5.3-Final. (and fixed my own nexus repository to pull JBoss's latest jars.)

However it did not fix the problem. But by reading the comments of the blog entry above I also realised that my hibernate3-maven-plugin may be using older dependencies. So I explicitly added the recent verions of org.hibernate:hibernate-core dependency to both the plugin and the app, as well as org.hibernate:hibernate-entitymanager to the plugin as well:

<properties>
  <hibernate.version>3.5.3-Final</hibernate.version>
</properties>
....
<dependencies>
  <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>
</dependencies>
....
<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>
    </dependencies>
  </plugin>
</plugins>

No value specified for parameter when using MySQL with JPA/Hibernate

I came across a problem when swithching database for a project from HSQLDB to MySQL.

I am using memory based HSQL for unit tests.
I am using file based HSQL for development testing.
I have changed my integration tests to using MySQL from file based HSQL.
Final production release will probably use MySQL or Firebird.
(This isn't commercial work, so no Oracle in any stack...)

But when I switched to MySQL, JPA/Hibernate starting complaining about: No value specified for parameter 2. As usual the generic multiple vendor reasons for using JPA/JDBC usually is not true...

Was a bit dumbfounded with this error, but eventually found the solution : http://opensource.atlassian.com/projects/hibernate/browse/HHH-2605.

As it turns out there is a bug in the version of Hibernate that I use.

I depend on
 <dependencies>
 ...
   <dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-   entitymanager</artifactId>
    <version>3.3.1.ga</version>
   </dependency>
 ...
 </dependencies>

And this version in the maven repositories was uploaded with wrong transparent dependency to hibernate 3.2.4.ga, which is buggy.

But the quick fix is to change my own dependency management to use version 3.2.6.

Thus this change solved the problem:
 <dependencyManagement>
 ...
   <dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate</artifactId>
    <version>3.2.6.ga</version>
   </dependency>
 ...
 </dependencyManagement>

Maven causes problems, but also solves problems...