DBUnit does not flush and insert the database after each method, so the test is not independent

Question

I have a test for the DAO class, I use DBUnit to create and populate the database (using a derby in memory). I am having problems testing the dao update method as it modifies the data and then another test fails. Since we all know that a test must be independent of others, and I know that DBUnit has some features for cleaning and restoring a database after each test. But that will not work!

Code 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 a test (disabled to avoid side 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

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

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

test1.xml is similar to

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

test2.xml is similar to

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

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

• delete everything from table2
• delete everything from table1
• insert data from test1.xml into table1
• insert data from test1.xml into table2
• run test1
• delete everything from table1
• insert data from test2.xml into table1
• run test2

So, when test2 is executed, table 1 contains the data from test2.xml, which is what we expect. But table2 still contains data for test1, which may cause some problems.

The workaround is to have an empty row for each table in all xml files. He will ensure that all tables are cleaned before insertion.

In the example above

test1.xml will look like

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

test2.xml is similar to

 <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); } 

Answer 3

Ensure that the database is reset before each test case to ensure test independence. @BeforeMethod is called only once before running all test cases, so putting cleanAndPopulate here is not enough.