How to revert the database back to the initial state using dbUnit?

Question

I am new to automated testing and dbUnit. So I would appreciate your advice.

I am going to create a test suite, that will run the following way:

• create an in-memory H2 database
• run DDL scripts to create tables
• run dbUnit to insert initial data (let's call it STATE0) that will be used by all tests.
• run tests

Till there it looks nice for me, but what I don't understand, is how do I revert the database to the STATE0 after a test run and changed the data?

Can I do it with dbUnit?
Or with something else?
Should I recreate the database before each test?

Simple not commiting transactions in tests is not appropriate for me, because the tests will eventually run more than one transaction over may be more than one database connection.

Answer 1

DBUnit can do the work four you automatically if you write your @BeforeClass, @Before and @After methods properly. E.g. in our project, using Derby, one such test case looks like

public class MyTest {
    protected static IDataSet getDataSet() throws Exception {
        URL url = MyTest.class.getClassLoader().getResource("MyDataSet.xml");
        return new XmlDataSet(new FileInputStream(url.getPath()));
    }

    private static JdbcDatabaseTester databaseTester;

    @BeforeClass
    public static void setUpClass() throws Exception {
        // Init test environment, session etc.
        databaseTester = new JdbcDatabaseTester(
                "org.apache.derby.jdbc.ClientDriver",
                "jdbc:derby://localhost:1527/myschema", 
                "username", "password");
        databaseTester.setDataSet(getDataSet());
    }

    @AfterClass
    public static void tearDownClass() {
        // Close session etc.
    }

    @Before
    public void setUp() throws Exception {
        databaseTester.onSetup();
    }

    @After
    public void tearDown() throws Exception {
        databaseTester.onTearDown();
    }

    @Test
    public void test() throws Exception { ... }
}

This code puts back (a subset of) the DB schema to the state defined by MyDataSet.xml after each test. (Note that, as @Pascal commented, the reset may not always be full - if a test modifies a table which is not in the dataset, it won't be affected by the @Before / @After methods.)

Answer 2

To initialize the database to the initial dataset, just implement these methods in your test case :

@Override
protected DatabaseOperation getSetUpOperation() throws Exception
{
    return DatabaseOperation.CLEAN_INSERT; // by default (will do DELETE_ALL + INSERT)
}

@Override
protected DatabaseOperation getTearDownOperation() throws Exception
{
    return DatabaseOperation.NONE; // by default
}

You may have foreign keys constraints if some of your tests insert rows in an empty table (not defined in initial dataset for example).

Just add this empty table in your dataset without any row :

<mydb_mypopulatedtable id="1" name="toto" alias="funky"/>
<mydb_mypopulatedtable id="2" name="titi" alias="groovy"/>
<mydb_mypopulatedtable id="3" name="tutu" alias="creepy"/>

<mydb_myemptytable />

Here, myemptytable has a foreign key to mypopulatedtable. If myemptytable was not defined, DBUnit would try to delete the mypopulatedtable but will fail because of the constraint. If defined, DBUnit will delete myemptytable rows before.