Best way to create / drop a database before / after integration testing on a Maven/Junit/DBUnit project?

Question

I've seen some people use the maven-sql-plugin to do this. But it seems like a task that is better suited for DBUnit....perhaps at the beginning of an entire test suite.

What's the best practice here?

Schoenfelder

Answer 1

I use the Maven SQL Plugin

You're much better off using it and making sure that you create and populate before your tests and then drop after your tests. You'll also want to use create or replace, or drop if exists in your creation script (assuming your database supports it) in the event that a test fails and leaves the database in some inconsistent state.

Unseen

Could you give an example of DB inconsistent state? It will be dropped on next tests run or in cleanup step anyway. Also, if you are using creation DB in build script you lose ability to run one specific test from IDE(at least Eclipse), because you'll need to create test db by hand. – Selfimmolation

Answer 2

It took some fiddling around, but I got it to drop, create, and create the schema for H2 and MySQL. Still need to finish it for Oracle and SQL*Server 2008. I tucked the exact DROP and CREATE commands into properties and in some cases (such as H2) needed to skip the create database altogether. Here is what it looks like:

  <plugin>
    <!-- Used to automatically drop (if any) and create a database prior to running integration test cases. -->
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>sql-maven-plugin</artifactId>
    <dependencies>
      <dependency>
        <!-- Adds the correct JDBC driver as a dependency of this plugin -->
        <groupId>${database.groupId}</groupId>
        <artifactId>${database.artifactId}</artifactId>
        <version>${database.version}</version>
      </dependency>
    </dependencies>
    <configuration>
      <!-- common configuration shared by all executions -->
      <driver>${database.class}</driver>
      <username>${database.username}</username>
      <password>${database.password}</password>
      <url>${database.url}</url>
    </configuration>
    <executions>
      <execution>
        <!-- Start by dropping the database (we'll leave it intact when finished) -->
        <id>drop-db</id>
        <phase>pre-integration-test</phase>
        <goals>
          <goal>execute</goal>
        </goals>
        <configuration>
          <!-- Can't use regular URL in case database doesn't exist -->
          <url>${database.url.alternate}</url>
          <skip>${database.sqlDrop.skip}</skip>
          <autocommit>true</autocommit>
          <sqlCommand>${database.sqlDrop};</sqlCommand>
          <onError>continue</onError>
        </configuration>
      </execution>
      <execution>
        <!-- then create a new database -->
        <id>create-db</id>
        <phase>pre-integration-test</phase>
        <goals>
          <goal>execute</goal>
        </goals>
        <configuration>
          <!-- Can't use regular URL in case database doesn't exist -->
          <url>${database.url.alternate}</url>
          <skip>${database.sqlCreate.skip}</skip>
          <autocommit>true</autocommit>
          <sqlCommand>${database.sqlCreate};</sqlCommand>
          <onError>continue</onError>
        </configuration>
      </execution>
      <execution>
        <!-- and finally run the schema creation script we just made with the hibernate3-maven-plugin -->
        <id>create-schema</id>
        <phase>pre-integration-test</phase>
        <goals>
          <goal>execute</goal>
        </goals>
        <configuration>
          <skip>${database.sqlSchema.skip}</skip>
          <autocommit>true</autocommit>
          <srcFiles>
            <srcFile>target/hibernate3/sql/create-${database.vendor}-schema.sql</srcFile>
          </srcFiles>
          <onError>continue</onError>
        </configuration>
      </execution>
    </executions>
  </plugin>  

Schoenfelder