DB Unit Testing

DB Unit testing has always been a huge headache for me and i suspect many people out there.

The few main reasons why they are difficult are:

1. Setup code for running tests (like jdbc connections)
2. Cannot assume that runs are in a certain order
3. Maintaining states

Setup code

Most database testing code will probably setup their jdbc connection like this, either you have a singleton that passes a jdbc connection to you unit tests or you will have an abstract subclass that inherit from TestCase and do the setup there and exposing the connection.

public class DBBase extends TestCase {

private Connection c;

public DBBase(String testName) {
super(testName);
}

@Override
protected void setUp() throws Exception {
super.setUp();
Class.forName("org.hsqldb.jdbcDriver" );
c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb", "sa", "");
}

public Connection getConnection(){
return c;
}

// TODO add test methods here. The name must begin with 'test'. For example:
// public void testHello() {}

}

What's not good is that if we want to run somewhere else, for example in a build server we might need to potentially change the connection string to ensure that it points to the correct server.

Maintaining States
DB tests involving queries usually does involve knowing what the state of the data in database is in so as to check the results or the query are correct or not.

http://www.dallaway.com/acad/dbunit.html

http://livingtao.blogspot.com/2007/08/unit-testing-with-junit-spring-and.html