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:
- Check out my project anywhere
- Create and populate the database with stub data with one command.
- Run the application via jetty
- Test the application straight away
- 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.
