Integration Testing with Maven and an In-Memory Derby Database

Abstract
You have a database-driven application. Your project contains a lot of SQL and you want to test it. Following strict unit testing guidelines you have mocked your data access up to this point (to avoid access to an external resource) and have verified your business rules.  Now you are ready to move to the next step - integration testing and verifying the SQL itself. But how? And more importantly, how to do it quickly and easily - i.e. not relying on an external database server? The purpose of this article is to explore quick and easy integration testing with Maven and an in-memory Derby database.

System Requirements
The code was developed and run using the following system setup. If yours is different, it may work but no guarantees.

• JDK 1.7.0_17
• NetBeans 7.4
• Maven 3.0.5 (bundled with NetBeans)
• Derby 10.10.1.1

The rest of the software dependencies can be found in the project's pom.xml. 

Download Project
If you just want to look at the project demonstrating the solution, download the code example from GitHub (https://github.com/mjremijan/MavenIntegrationTestingWithDerby).

Run Project
The project is a standard Maven setup.  Simply execute mvn from the command line after changing to the MavenIntegrationTestingWithDerby directory. Or, within NetBeans, right-click on the project and select "Clean and Build". Using NetBeans is probably easiest.

The project contains Maven integration tests which are NOT run during the unit testing phase of the Maven lifecycle. So executing mvn test won't run the integration test examples!

Configure Integration Testing
The Maven world has two kinds of testing: unit-testing and integration-testing.  Both are driven off the Maven execution life cycle.  Unit-testing you get for free, but integration-testing is a bit more complicated.  Let's learn a little more about each one.

We'll first talk a bit about unit-testing because it is probably something you are already familiar with and we'll use this familiarity to springboard into integration-testing. Unit testing is performed by the maven-surefire-plugin during the test phase of the  life cycle. There is no configuration needed to run simple unit tests. Simply create a src/test/main/**/*Test.java class and Maven will automatically execute it for you. It's nice that unit-testing works so easily, and you'd think it would be the same for integration-testing, but it's not.

Now we'll talk a bit about integration-testing. Integration testing is performed by the maven-failsafe-plugin during the integration-test phase of the life cycle. For integration tests, the default file matching name patterns are as follows:

1. src/test/main/**/IT*.java
2. src/test/main/**/*IT.java
3. src/test/main/**/*ITCase.java  

As you can see, these file name patterns are different than they are for unit tests. The most popular pattern is *IT.java since it most closely matches its unit test counterpart *Test.java. You annotate the *IT.java with the same @Test annotations you would use for a unit test.  In fact the test itself is written almost exactly as if it were a unit test. However the difference is that integration tests use real external resources more and rely on mock objects less.  

After creating *IT.java integration tests you may run your Maven default life cycle and expect the integration tests to run. Your expectations are wrong. To get integration tests to run the maven-failsafe-plugin must be configured in your project's pom.xml.  Configuring the plugin is easy. Listing 1 shows you how to add the plugin to your project's.

Listing 1: Add maven-failsafe-plugin to project's build

<project><build><plugins>...
<!-- 
    The maven-failsafe-plugin is used to run integration tests.
    The tests are part of /src/main/test and should end with 
    *IT.java
-->
<plugin>
    <groupid>org.apache.maven.plugins</groupid>
    <artifactid>maven-failsafe-plugin</artifactid>
    <version>2.12.4</version>
    <executions>
        <execution>
            <id>integration-tests</id>
            <goals>
                <goal>integration-test</goal>
                <goal>verify</goal>
            </goals>
            <configuration>
                <skiptests>${skip.integration.tests}</skiptests>
            </configuration>
        </execution>
    </executions>
</plugin>
</plugins></build></project>

In this article's project (download), I have two integration test examples.  The first is SomeTableFindExistingIT.java.  This integration test will attempt to find existing data in the database.  The second is SomeTableInsertAndFindIT.java.  This integration test will attempt to insert data into a database then query to find it. Now that I have my *IT.java files and I have the maven-failsafe-plugin configured in the pom.xml, the integration tests are ready to run right?  Well not quite yet.

Remember, integration tests partially rely on external resources.  For these example integration tests, a database is needed. So the next configuration step I need to make is to get Maven to automatically start and stop a database and fill it with data for my integration tests to use.  Let's take a look at that next.

Configure Derby
Derby is a very interesting project with a history with some twists and turns. Accoring to Wikipedia, Derby started its life with Cloudscape, Inc. The first release of the project was called JBMS.  After this first release the project was renamed and released as Cloudscape.  After a few company acquisitions, the project eventually ended up at IBM but still continued to be called Cloudscape. IBM eventually gave Cloudscape to Apache.  At this point it changed names to Derby. Then, just to make things even more confusing, Sun (now Oracle) incorporated the project into Java 6 and refered to it as Java DB.  So Cloudscape, Derby, Java DB...all the same name for basically the same thing.

We are going to use Derby for our integration tests because the project supports common SQL syntax and it can be run in-memory without the need for a separate database server.  Maven can start the database, run the integration tests, and stop the database.  Nice, clean, and easy.


To do this, we need to add two configurations to the project's pom.xml. Listing 2 shows the first, which are the dependencies to get the Derby JARs from the Maven repo.

Listing 2: Dependencies to get Derby JARs

<project>...
<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derby</artifactId>
    <version>10.10.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derbyclient</artifactId>
    <version>10.10.1.1</version>
</dependency>

Next we need to add a plugin to the Maven build.  Listing 3 shows this inmemdb-maven-plugin which is used to control Derby during integration testing.

Listing 3: The inmemdb-maven-plugin

<project><build><plugins>...
<!-- 
    This plugin will take care of starting a Derby database
    and creating the tables/data before integration testing
    begins. It will then cleanup everything afterwards
-->        
<plugin>
    <groupId>com.btmatthews.maven.plugins.inmemdb</groupId>
    <artifactId>inmemdb-maven-plugin</artifactId>
    <version>1.4.2</version>
    <configuration>
        <monitorKey>inmemdb</monitorKey>
        <monitorPort>11527</monitorPort>
    </configuration>
    <executions>
        <execution>
            <id>run</id>
            <goals>
                <goal>run</goal>
            </goals>
            <phase>pre-integration-test</phase>
            <configuration>
                <daemon>true</daemon>
                <type>derby</type>
                <database>${ferris.property.test.data.db}</database>
                <username>${ferris.property.test.data.user}</username>
                <password>${ferris.property.test.data.pass}</password>
                <sources>
                    <script>
                        <sourceFile>src/test/resources/sql/test-data-derby.sql</sourceFile>
                    </script>                               
                </sources>
            </configuration>
        </execution>
        <execution>
            <id>stop</id>
            <goals>
                <goal>stop</goal>
            </goals>
            <phase>post-integration-test</phase>
        </execution>
    </executions>  
</plugin>

Let's take a look at this plugin in more detail.

1st, a big shout out to Brian Matthews for creating this plugin and making it available.

2nd, <phase>pre-integration-test</phase>: This execution is tied to the pre-integration-test phase of the Maven execution life cycle.  This basically means that before Maven runs the integration tests, it will first start an in-memory Derby database instance.

3rd, <database>, <username>, and <password>: These values are all properties defined in the pom.xml. It is important to have this data extracted as properties because we will need to reuse the values later when configuring JPA to connect to the database.  Instead of having duplicate data, put the data in properties.

4th, src/test/resources/sql/test-data-derby.sql: This is the SQL script which creates our tables and fills them with data.  Depending on the complexity of your database, you may want to split this into multiple files.  Tying an SQL script file to a specific integration test is a good idea once your project gets large and has a lot of integration tests. Doing so will hopefully prevent weird dependencies between integration tests and SQL scripts.

5th, <phase>post-integration-test</phase>: This execution is tied to the post-integration-test phase of the Maven execution life cycle. So this basically means once the integration tests are over, Maven will stop the in-memory Derby database instance.

So with some Maven dependencies the inmemdb-maven-plugin plugin, some Maven properties, and an SQL script, we have configured Maven to automatically start, build, and stop a database for the integration tests. Let's next take a closer look at the SQL script and see how the database is built.


Configure Data
In the previous section we looked at the inmemdb-maven-plugin plugin to get the Derby database up and running and loaded with data before our integration tests run. This is a great convenience; We don't have to worry about doing this all in the integration test itself.  If you have only one test it's not that bad, however if you have dozens it can get very tricky having the tests do this database work.  It is better to have Maven take care of it before the integration test start.  Let's see how Maven does this.

The src/test/resources/sql/test-data-derby.sql file is in this article's project (download).  Listing 4 shows this file.

Listing 4:SQL to build the database

-- Create the tables
CREATE TABLE app_sometable
(
   some_varchar varchar(100), 
   some_numeric numeric, 
   some_timestamp timestamp, 
   some_blob blob  
);

-- Insert the data
insert into app_sometable (some_varchar, some_numeric, some_timestamp, some_blob)
values ('unit_test_varchar',123,'1977-01-30-10.11.30.766',null);

The SQL script is very simple.  It creates a table named app_sometable with columns of various commonly used types.  Then it inserts a row of test data into the table.  This row can then be used in the integration test to verify that selecting data from the table is working properly.

A final point to make about the SQL is that it is not production ready.  It has no primary key, no indices and no permissions.  When integration testing, you typically will not use the full-fledged production SQL scripts.  Integration tests verify the basic communication between resources is working, so in this case the SQL doesn't need to get too fancy.

Now that we have our database and our data, the next thing to do is figure out how the code is going to connect and get it.  For that, we'll need to configure JPA.

Configure JPA
Java Persistence API (JPA) is the Java standard for object-relational mapping (ORM) to turn relational database data into Java objects. The persistence.xml file is used to configure JPA.  Section 8.2.1 of the JSR 338 specification defines that the persistence.xml file is to be located off the root of the JAR resource as /META-INF/persistence.xml.  Translating this into a Maven integration test, that means we need to put the persistence.xml file in the following location:

src/test/resources/META-INF/persistence.xml

the src/test/resources directory is the standard Maven directory for test resources and META-INF/persistence.xml is where the JPA specification says to put this file.  Now, let's take a look listing 5 and see what's in our integration test persistence.xml.

Listing 5: Integration test persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
  <persistence-unit name="MavenIntegrationTestingWithDerbyPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <class>org.ferris.mavenintegrationtestingwithderby.SomeTable</class>
    <properties>
      <property name="hibernate.connection.username"        value="${ferris.property.test.data.user}"/>
      <property name="hibernate.connection.password"        value="${ferris.property.test.data.pass}"/>
      <property name="hibernate.connection.url"             value="jdbc:derby://localhost/memory:${ferris.property.test.data.db}"/>
      <property name="hibernate.connection.driver_class"    value="${ferris.property.test.data.driver}"/>
      <property name="hibernate.dialect"                    value="${ferris.property.test.data.dialect}"/>
      <property name="hibernate.show_sql"                   value="true"/>
      <property name="hibernate.cache.provider_class"       value="org.hibernate.cache.NoCacheProvider"/>      
    </properties>
  </persistence-unit>
</persistence>

In listing 5, there are a couple things to comment on.

1st MavenIntegrationTestingWithDerbyPU: This is the name of the persistence unit.  We'll need to remember this when we get to coding the integration tests themselves. 

2nd: Remember the Maven properties when setting up the inmemdb-maven-plugin plugin?  Well here they are again, with a few more added to get the integration test persistence.xml configured property.

Although JPA needs persistence.xml, you will need to do one more thing to get the integration tests running properly. What you need to do is make sure Maven does a search and replace of all the properties in persistence.xml. This is easy do do by configuring a resource as showing in listing 6.

Listing 6: Configure resource so Maven performs search & replace of properties

<project><build>...
<!--
  Make sure resources are filtered to get the properties
-->
<testResources>
 <testResource>
  <directory>src/test/resources</directory>
  <filtering>true</filtering>
 </testResource>
</testResources>

This tells Maven to do the search and replace of all files it finds in the src/test/resources directory when it is copying those resources to the /build directory during the unit- and integration-testing phases of the Maven life cycle. 

At this point we have all of the configuration done.  We have configured Maven with the maven-failsafe-plugin plugin to run integration tests during the Maven life cycle. Next we used the inmemdb-maven-plugin plugin to start and build and in-memory Derby database before the integration tests start and to stop the database after the tests are finished.  Then we looked at JPA and made sure the persistence.xml file is configured correctly for the integration tests.  The final thing to do is to create an integration test.

Writing your integration tests
This article's project (download) has two integration tests. Listing 7 shows SomeTableFindExistingIT.java.

Listing 7: The SomeTableFindExistingIT.java integration test

package org.ferris.mavenintegrationtestingwithderby;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import org.apache.log4j.Logger;
import static org.junit.Assert.*;
import org.junit.Before;
import org.junit.Test;

public class SomeTableFindExistingIT 
{
    private static Logger log = Logger.getLogger(SomeTableFindExistingIT.class);
    private EntityManager em;

    @Before
    public void setEntityManager() throws Exception 
    {
        try {
            EntityManagerFactory emf
                    = Persistence.createEntityManagerFactory("MavenIntegrationTestingWithDerbyPU");
            em = emf.createEntityManager();
        } catch (Exception e) {
            log.fatal("Cannot create EntityManager", e);
            throw e;
        }
    }
    
    @Test
    public void findSomeTableData() throws Exception 
    {
        String someVarchar = "unit_test_varchar";   
        Long someNumeric = new Long(123);
        Timestamp someTimestamp = new Timestamp(new SimpleDateFormat("yyyy-MM-dd-hh.mm.ss.SSS").parse("1977-01-30-10.11.30.766").getTime());
        
        SomeTable bean = em.find(SomeTable.class, someVarchar);
        assertNotNull(
              String.format("Not found! someVarchar=\"%s\"",someVarchar)
            , bean
        );
        assertEquals(someVarchar, bean.getSomeVarchar());
        assertEquals(someNumeric, bean.getSomeNumeric());
        assertEquals(someTimestamp, bean.getSomeTimestamp());        
    }
}

Let's take a look at this integration test a bit more closely.

1st setEntityManager(): This @Before annotated method will create an EntityManager for the integration test.  Notice the persistence unit name is "MavenIntegrationTestingWithDerbyPU", which is the same as in the persistence.xml file.  It's also important to mention this is a real EntityManager attached to a real Derby database.  No mocks here.

2nd findSomeTableData(): This @Test method sets the following variables: someVarchar, someNumeric, someTimestamp.  It uses them to both find and assert the data in the SomeTable object matches the data in the database. Again, no mocks here. The data in SomeTable comes from the app_sometable table in the Derby database which was pre-populated with data by Maven before the integration test are run.

Now that the integration test is written, we just need to run it and make sure it works. 

Running your integration tests
Integration tests are part of the Maven life cycle and are run during the integration-test phase. Table 1 shows the Maven default life cycle.  Notice the integration-test phase is much later in the life cycle than the test phase. This means if you execute $mvn test you will run your unit tests but not your integration tests!  Eclipse and NetBeans both have GUI shortcuts for testing the project but you must be aware those shortcuts only run $mvn test and will not include the integration tests.  To completely run your integration tests, you must execute $mvn verify or create your own GUI shortcut to do so.

Table 1: The Maven lifecycle

validate

initialize

generate-sources

process-sources

generate-resources

process-resources

compile

process-classes

generate-test-sources

process-test-sources

generate-test-resources

process-test-resources

test-compile

process-test-classes

test

prepare-package

package

pre-integration-test

integration-test

post-integration-test

verify

install

deploy

Conclusion
Running unit tests with Maven is easy.  Running integration tests take a bit more work.  But I guess this makes sense.  After all, an application may integrate with half a dozen system and trying to get them all configured and up and running for an automated integration test can be quite a challenge. But if you just want to connect to a database and run a few queries, I hope this article helped.

References

http://en.wikipedia.org/wiki/Apache_Derby
http://inmemdb-maven-plugin.btmatthews.com/run-mojo.html
http://maven.apache.org/surefire/maven-surefire-plugin/
http://maven.apache.org/surefire/maven-failsafe-plugin/
http://download.oracle.com/otndocs/jcp/persistence-2_1-fr-eval-spec/index.html
 

stop