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.

Maven Jetty and Tomcat dependencies with JSP / JSTL errors

I use Jetty as a Maven plugin during development, but tend to run Tomcat as my deployment server. (Not sure "production" is appropiate as it's my personal server). My applications are often java MVC based utilising some form of JSP.

This combination creates a slight issue with dependencies for JSP and JSTL. If you have ever seen this error when running mvn jetty:run:

java.lang.ClassNotFoundException: org.apache.el.ExpressionFactoryImpl

Or these errors when running in tomcat when it was okay in jetty:

javax.servlet.jsp.JspApplicationContext
.getExpressionFactory()Ljavax/el/ExpressionFactory

or

org.apache.jasper.JasperException: Unable to read TLD "META-INF/c.tld" from JAR file

or

java.lang.LinkageError: loader constraint violation: loader (instance of org/apache/catalina/loader/WebappClassLoader) previously initiated loading for a different type with name "javax/el/ExpressionFactory"

Over the last few years I have perservered and solved this flat wheel in different ways. However in my recent deja-vue I now have a tidy solution. (the project where this is visable is my up4 project. github source, demo)

I am utilising the jetty maven plugin, and the tomcat maven plugin to show how this works.


<plugin>
<groupId>org.mortbay.jetty</groupId>
   <artifactId>jetty-maven-plugin</artifactId>
   <version>7.0.2.v20100331</version>
</plugin>
<plugin>
   <groupId>org.codehaus.mojo</groupId>
   <artifactId>tomcat-maven-plugin</artifactId>
</plugin>


To utilise JSP pages and JSTL EL with e.g. Spring MVC / Struts Tiles you perhaps expect these types of maven dependencies, remembering that most containers include support for Servelet API and JSPs:


<dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>servlet-api</artifactId>
   <version>2.5</version>
   <scope>provided</scope>
</dependency>
<dependency>
   <groupId>javax.servlet.jsp</groupId>
   <artifactId>jsp-api</artifactId>
   <version>2.1</version>
   <scope>compile</scope>
</dependency>
<dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>jstl</artifactId>
   <version>1.2</version>
</dependency>


If you then try mvn clean jetty:run you may end up with a:

java.lang.ClassNotFoundException: org.apache.el.ExpressionFactoryImpl

A response which will solve the jetty problem is to include some jstl EL dependencies:


<dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>servlet-api</artifactId>
   <version>2.5</version>
   <scope>provided</scope>
</dependency>
<dependency>
   <groupId>javax.servlet.jsp</groupId>
   <artifactId>jsp-api</artifactId>
   <version>2.1</version>
   <scope>compile</scope>
</dependency>
<dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>jstl</artifactId>
   <version>1.2</version>
</dependency>
<dependency>
   <groupId>org.apache.tomcat</groupId>
   <artifactId>jasper-el</artifactId>
   <version>6.0.26</version>
</dependency>


mvn jetty:run

This should now work fine in Jetty (test in browser as well).

Now try the application in tomcat: mvn clean tomcat:run . It will moan about unable to read some the JSTL taglibs:

org.apache.jasper.JasperException: Unable to read TLD "META-INF/c.tld" from JAR file

This due to the compile scope of the jsp-api dependency, changing this to provided:


<dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>servlet-api</artifactId>
   <version>2.5</version>
   <scope>provided</scope>
</dependency>
<dependency>
   <groupId>javax.servlet.jsp</groupId>
   <artifactId>jsp-api</artifactId>
   <version>2.1</version>
   <scope>provided</scope>
</dependency>
<dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>jstl</artifactId>
   <version>1.2</version>
</dependency>
<dependency>
   <groupId>org.apache.tomcat</groupId>
   <artifactId>jasper-el</artifactId>
   <version>6.0.26</version>
</dependency>


Test first in mvn jetty:run , which should be okay.

Then in mvn clean tomcat:run . It starts ok, but on the first browser test this error appears:

javax.servlet.jsp.JspApplicationContext
.getExpressionFactory()Ljavax/el/ExpressionFactory

Okay, so Tomcat does not like JSTL EL dependencies as it provides them itself, so my initial respone was to have a separate profile which changed the scope of the dependency to provided for tomcat builds only. However on further reflection it turns out the spec says JSP & EL should be provided by the container. So in fact the problem lays with Jetty, and it turns out the move to eclipse.org created a licensing issue with JSP, so jetty does not include the appropiate libs. So to fix this the correct dependencies will have to be:

In your plugins section:


<plugin>
   <groupId>org.mortbay.jetty</groupId>
   <artifactId>jetty-maven-plugin</artifactId>
   <version>7.0.2.v20100331</version>
   <dependencies>
     <dependency>
       <groupId>org.mortbay.jetty</groupId>
       <artifactId>jsp-2.1-glassfish</artifactId>
       <version>9.1.1.B60.25.p2</version>
     </dependency>
     <dependency>
       <groupId>org.apache.tomcat</groupId>
       <artifactId>el-api</artifactId>
       <version>6.0.26</version>
     </dependency>
     <dependency>
       <groupId>org.apache.tomcat</groupId>
       <artifactId>jasper-el</artifactId>
       <version>6.0.26</version>
     </dependency>
   </dependencies>
</plugin>


And in your normal dependencies section:


<dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>servlet-api</artifactId>
   <version>2.5</version>
   <scope>provided</scope>
</dependency>
<dependency>
   <groupId>javax.servlet.jsp</groupId>
   <artifactId>jsp-api</artifactId>
   <version>2.1</version>
   <scope>provided</scope>
</dependency>
<dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>jstl</artifactId>
   <version>1.2</version>
</dependency>
<dependency>
   <groupId>org.apache.tomcat</groupId>
   <artifactId>el-api</artifactId>
   <version>6.0.26</version>
   <scope>provided</scope>
</dependency>
<dependency>
   <groupId>org.apache.tomcat</groupId>
   <artifactId>jasper-el</artifactId>
   <version>6.0.26</version>
   <scope>provided</scope>
</dependency>


mvn jetty:run & mvn tomcat:run

Voila.


Ps. If you are providing a WAR for another jetty container, you may want to create a profile section which include the provided dependencies.

Ps2. If you prefer Sun's EL libs then replace the org.apache.tomcat dependencies with:

<dependency>
   <groupId>javax.el</groupId>
   <artifactId>el-api</artifactId>
   <version>2.2</version>
   <scope>compile</scope>
</dependency>
<dependency>
   <groupId>org.glassfish.web</groupId>
   <artifactId>el-impl</artifactId>
   <scope>runtime</scope>
   <version>2.2</version>
</dependency>


IntelliJ IDEA on Ubuntu (with Maven , Jetty and JRebel)

I have written an howto on Ubuntu+IntelliJ+Maven+Jetty+JRebel, basically a quick quide to get IntelliJ IDEA working on Ubuntu, with Maven, the Jetty plugin for Maven and with JRebel (JavaRebel).

It assumes a clean basic Ubuntu install, and is quite terse for now. I may extend it with more example work flows etc in the future.

Comments are welcome.

groovy maven netbeans jetty facebook spring

Time to rewrite my old wishlist facebook application.

The old one (v3.x) dont work anymore as the web services are gone, I took them offline many months ago. The new application will use the wishlist 4.x web services running on wish.flurdy.com.

Tech stack for this project will be:

Language: groovy.
Build tool: maven.
Web server: jetty (maven plugin).
IDE: netbeans (6.5, with groovy plugin and maven plugin).
Framework: spring (+mvc and WebServices).

So really, only groovy is new compared to the other wishlist projects, but it will be nice to see groovy interact with them.

Already hitting some snags with groovy, maven and netbeans. Seems to be a few posts on how to use netbeans and groovy, or maven and groovy, but not all 3 together.

May at some stage try grails.