DBUnit not cleaning and insert the database after every method, so test are not independent

Question

I have a test for a DAO class, I use DBUnit to create and populate the database (using an in-memory derby). I am in problems when testing the dao update method because it modify the data and then the other test fails. As all of us know a test should be independent of any other, and I know that DBUnit has some facilities to clean and regenerate the database after every test. But it does not work!

The code is this (TestNG):

@BeforeMethod
public void prepareData() throws Exception {
  cleanAndPopulate("users");
}

public void cleanAndPopulate (String nameXML) throws Exception {
  IDatabaseConnection conn; 
  conn = new DatabaseConnection (sessionForTesting.connection());        
  InputStream is = DBconnection.class.getClassLoader()
    .getResourceAsStream(nameXML + ".xml");      
  dataset = new FlatXmlDataSet(is);
  System.out.println("*** Preparando base de datos de test"); 
  DatabaseOperation.CLEAN_INSERT.execute(conn, dataset); 
}

This is the test (disabled to avoid collateral effects):

@Test(enabled=false) // Deja la BBDD en estado erroneo!!!
public void busco_y_actualizo() throws Exception { 
    PacoUser resultado = userdao.getById(1L);
    resultado.setName("OTRO");
    userdao.update(resultado);
    PacoUser resultado2 = userdao.getById(1L);
    AssertJUnit.assertNotNull(resultado2); 
    AssertJUnit.assertEquals("OTRO", resultado2.getName());    
}

Answer 1

It is because CLEAN_INSERT does "CLEAN" before the test, not after the test.

For example, if there are two tests, test1 and test2. test1 and test2 populate tables from test1.xml and test2.xml respectively.

test1.xml is like

<dataset>
  <table1 ... />
  <table2 ... />
</dataset>

test2.xml is like

<dataset>
  <table1 ... />
</dataset>

When the order of tests is test1 and then test2, CLEAN_INSERT will do the following operations:

1. delete all from table2
2. delete all from table1
3. insert data from test1.xml into table1
4. insert data from test1.xml into table2
5. execute test1
6. delete all from table1
7. insert data from test2.xml into table1
8. execute test2

So when test2 is executed, table1 has data from test2.xml, which is what we expect. But table2 still contains data for test1, which may cause some issues.

A workaround is to have an empty row for each table in all xml files. It will make sure all tables would be cleaned before insert.

For the above example,

test1.xml will be like

<dataset>
  <table1 ... />
  <table2 ... />
  <table1 />
  <table2 />
</dataset>

test2.xml is like

<dataset>
  <table1 ... />
  <table1 />
  <table2 />
</dataset> 

Answer 2

@After
public void after() throws SQLException {
    Session session = hibernateSessionFactory.openSession();
    try {
        Work work = new Work() {

            @Override
            public void execute(Connection connection) throws SQLException {
                IDatabaseConnection dbConn = null;
                try {
                    dbConn = getConnection(connection);
                } catch (DatabaseUnitException e) {
                    logger.error("Exception in before", e);
                    Assert.fail(e.getMessage());
                }

                try {
                    List<String> resultList = (List<String>) hibernateTemplate
                            .execute(new HibernateCallback() {
                                String sql = "SELECT st.TABLE_NAME FROM INFORMATION_SCHEMA.SYSTEM_TABLES st where st. TABLE_TYPE='TABLE'";

                                public List<String> doInHibernate(
                                        Session session)
                                        throws HibernateException,
                                        SQLException {
                                    Query query = session
                                            .createSQLQuery(sql);
                                    List<String> list = query.list();
                                    return list;
                                }
                            });

                    QueryDataSet partialDataSet = new QueryDataSet(dbConn);
                    for (String tableName : resultList) {
                        partialDataSet.addTable(tableName);
                    }

                    DatabaseOperation.DELETE_ALL.execute(dbConn,
                            partialDataSet);

                } catch (Exception e) {
                    logger.error("Exception in after", e);
                    Assert.fail(e.getMessage());
                } finally {
                    dbConn.close();
                }

            }

        };
        session.doWork(work);

    } catch (Exception e) {
        logger.error("Exception in after", e);
        Assert.fail(e.getMessage());
    } finally {
        session.close();
    }
}

protected DatabaseConnection getConnection(Connection connection)
        throws DatabaseUnitException, SQLException {
    return new DatabaseConnection(connection, SCHEMA);
}