Database scenarios for integration and unit tests

If you have an application that depends on a database and you want to write unit
 or integration tests, you face two problems.

• setting up test data, or putting the database in its initial state
• checking the data in the database as part of the test

One way to get around these problems is to use 
a technique called mocking. The object under test 
works at runtime with stand-ins for the objects it depends on, 
and those stand-ins contain data prepared in advance.

Mock objects are a good solution, especially for unit tests, but they have limits. The most serious, in my view, is the inability to simulate complex processes running in the background. Referential integrity constraints are a classic database example. Preparing a mock that faithfully simulates a database is very inefficient. Of course, it needn't be the database directly. There might be a middle layer such as Hibernate, with the database underneath it.

Another alternative to mocks, and this time a full-fledged one, is the
 DbUnit framework. DbUnit lets you 
 set up test data and check it. Andrew Glover shows how to use it 
 in his article Effective Unit Testing with DbUnit.

DbUnit is very useful and its API supports a range of operations, 
 from comparing datasets to extracting database schemas. Then again, 
 you can't teach an old dog new tricks.

What I wanted was to:

• avoid making developers create test data as XML
• avoid making developers learn another API
• make prepared data easy to reuse and extend

The result is a simple “framework” I named ScenarioTestCase, which 
 is based on jUnit. Briefly, it's for writing integration 
and 
 unit tests with the standard Java API and ordinary SQL, with the aim of making existing test data easy to reuse or extend.

Image could not be loaded. Archive image in Database scenarios for integration and unit tests, figure 1

↗ Medium

Full size (about 38 KB)

At the heart of it all is the abstract class ScenarioTestCase, which manages the test lifecycle and lets you run test scenarios. A test scenario, represented by the TestSQLScenario interface, is a set of SQL queries that initialize and clean up test data.

ScenarioTestCase guarantees the following:

• a call before the test starts to the method onStartUp
• execution of every registered scenario before each test method
• a call before each test method to onSetUp
• a call after each test method to onTearDown
• the ability to run arbitrary SQL queries (insert, update, delete, select)

Also worth mentioning is the class ConnectionscenarioTestCase, a subclass of ScenarioTestCase. It opens a database connection from the parameters it receives and handles the JDBC work. The following example shows how to write a test. Suppose we have a table containing a user's login name and some other values. The test object checks that the application logic changes that login name.

Writing a scenario

A test scenario contains SQL commands to initialize and clean up the database. Each scenario must implement the TestSQLScenario interface mentioned above, which defines two methods. The method getInitSQL returns the initialization commands. The method getCleanUpSQL returns the cleanup commands.

public class ExampleScenario implements TestSQLScenario{
  
    public List getInitSQL() {
        ArrayList l = new ArrayList();
        l.add("insert into users (uname, user_id,) values ('pavel', 1)");
        return l;
    }

public List getCleanUpSQL() {
        ArrayList l = new ArrayList();
        l.add("delete from users where user_id = 1979");
        return l;
    }
}

Writing the test itself

The test object itself subclasses ConnectionscenarioTestCase. The onStartUp method creates the test scenario and registers it using the method addScenario. You can register as many scenarios as you like this way. The ConnectionscenarioTestCase class defines the abstract method
 getConnectionParametrs, which subclasses use to specify the parameters for opening the database connection.

public class ExampleTest extends ConnectionScenarioTestCase {
    
    protected String[] getConnectionParameters() {       
        return new String[]{
                "com.mysql.jdbc.Driver", 
                "jdbc:mysql://localhost/test", 
                "test", 
                "test"};
    }

protected void onStartUp() {      
        super.onStartUp();
        addScenario(new ExampleScenario());//přidání scénáře        
    }

public void testFoo(){       
        //zinstancování aplikační logiky
 AplikacniLogika al = new AplikacniLogika(); 
 //změna uživatelského jména
        bl.zmen('šavel')
        
        //Pro ověření použijeme select, kde předpokládanou změnu 
        //zahrneme v rámci where klauzule viz uname='šavel'.
        int count = 
jdbcSelect("select count(*) from users where uname='šavel' and user_id = 1");
        
        //V případě, že logika zafungovala správně, musí byt v proměnné count hodnota 1
        assertEquals("Uživatelské jméno musí být změněno", 1, count);
    }
    
    public void testHoo(){       
       //test something else
    }
}

You can run the test object like any other JUnit test.

Extensibility

ScenarioTestCase is intended to be extended as needed, and ConnectionScenarioTestCase shows one way to do it. A typical 
extension might start, for example,
a database connection pool or a container (Spring) in which the application logic “lives.”

Objects implementing the TestSQLScenario interface can be composed 
 or extended. The idea is to have a few basic scenarios 
 that most tests share. If you need to extend a test scenario, 
 subclass it or use composition.

This simple “framework” is by no means intended to compete with or replace DbUnit. It's just a very simple alternative for writing database-dependent tests conveniently. The source files and compiled classes are free for you to use as you like. Just remember that you need JUnit on the classpath and that it's written for Java 5.0.