What is a good strategy to assert updated data in Database with DBUnit?

Question

Here are some (overly) simplified code example to describe my unit testing method.

CompanyDataSet.xml

<dataset>
    <company company_key="100" company_name="OldName" />
</dataset>

CompanyDaoTest.java

@Test
public void testUpdateCompany() {
  CompanyDao companyDao = new CompanyDao();
  IDatabaseConnection dbConn = createConnection();

  IDataSet dataSet = createDataSet("CompanyDataSet.xml");
  DatabaseOperation.CLEAN_INSERT.execute(dbConn, dataSet);

  companyDao.updateCompany(100, "NewName");

  // What is a good way to assert updated company data ?
}

---

I came up with two ways to assert company data.

Create another dataset xml as expected dataset.

another XML

<dataset>
    <company company_key="100" company_name="NewName" />
</dataset>

Assert part in Java

IDataSet actual = dbConn.createDataSet(new String[]{"company"});
IDataSet expected = createDataSet("CompanyDataSet_expected.xml");

Assertion.assertEquals(expected, actual);

Just load the company object through DAO, and compare the properties.

you should get the idea.

---

My Problem

The first way is pretty easy to write, but I have to create another XML file for each different update method. It dose not sounds like a good idea to create so many data set XML files.

The second way is straightforward, however, when there are different update methods, the test class will filled with methods that assert different properties with different value. And Lots tests will broke if there are something wrong with load method.

Is there a good way to assert the data ? is it possible to avoid the problem I just described (or it dosen't really matter) ?

UPDATE

Since there is no one answer the question, I decide to accept my answer.

Answer 1

I've struggling with testing databases for a long time and I compiled my thoughts in a couple of blog posts:

• Testing the Impossible: Rules of Thumb
• Testing With Databases
• All my testing related blog posts

Answer 2

Here is another way to assert the data.

Since I already have the actual IDataSet which contains current data of database. I can simply retrieve the data from it.

I use the example in question, this is what I do

@Test
public void testUpdateCompany() {
  CompanyDao companyDao = new CompanyDao();
  IDatabaseConnection dbConn = createConnection();

  IDataSet dataSet = createDataSet("CompanyDataSet.xml");
  DatabaseOperation.CLEAN_INSERT.execute(dbConn, dataSet);

  companyDao.updateCompany(100, "NewName");

  IDataSet actual = dbConn.createDataSet(new String[]{"company"});
  ITable companyTable = actual.getTable("company");

  assertEquals("NewName", companyTable.getValue(0, "company_name"));
}

It's somewhat verbose if there are many properties to check, but it should be pretty easy to handle

---

UPDATE

Although this way dose solve the problem, test will not couples on unrelated method anymore. But, when it comes to maintenance, it actually has the same overhead first two way have, even worse. Because I have to wrote column name(lots of them) inside the java code, and there is no way to validate this.

Our team decide to use dataset to assert data whenever possible. The main reason is we want to keep our test data as small as possible, and easy to assert.

So the answer of the question will be use the dataset

However, there will be some downsides too, here is how we deal with it for now:

1. Organize the XML files :

   Location : The dataset XML files will be in the same package of the test classes.

   Naming : the XML file will start with the test class name, and add method/state name if necessary. for example : "companyDao-updateCompany-expected.xml"

2. Make XML sync with database.

   We will use Untils to generate DTD file from database on the fly, and use it to validate the XML. We rarely change column name, so the overhead should be low.