I want to share what I’ve done working on my current project to verify data in a database. The application we’re developing relying on SOAP messages exchange where the messages contain information to be written in datasource (DB2, MSSQL, Postgres, AS/400 etc.) Yeah we’re using them all.
You can consider this as a mappping SOAP(Business document) Database table.
When my service endpoint stores information from business document to database I want to make sure that data will be stored in correct fields.

So here you go.

1. Initialize DbAssert by passing Xml file defining the datasources according to IntegrationSpec.

dbAssert = DbAssert.initAssert("com/x/testfw/Sources.xml");

2. Set source name you’re going to use to check data in. This comes from your Source.xml file.


3. Set condition to search record by. To do this you need to know the field name to search by and supposed value of the field stored by your business logic you’re testing.
Let’s say that my code adds new customer to hypothetical “customers” table. I know that my “customers” table contains “id” field and new customer should be stored with id 123 in this field.
So I need to look record in database by “id” field with value 123.

dbAssert.condition("id", 123); 

For database it becomes SELECT … WHERE id = 123;

Any additional condition you can add by using

dbAssert.addCondition("last_name", "Coupland");

In this case for database it becomes SELECT … WHERE id = 123 AND last_name = ‘Coupland’;

4. You’re able to make assertions.

dbAssert.assertColumn("customers.name", "Douglas");

Where “customers.name” is table name and field name to retrieve value from.
“Douglas” is the value we expect in this database field. You can add as many assertions as you want.

You’re also able to specify table name just once and then for assertions use field names.

dbAssert.condition("id", 123);
dbAssert.assertColumn("last_name", "Coupland");
dbAssert.assertColumn("name", "Douglas");

Ok. There is more. You can pass a HashMap with fieldName – expectedValue pairs to assertColumn method.

dbAssert.condition("id", 123);

final Map columnsMap = new HashMap();
columnsMap.put("name", "Douglas");
columnsMap.put("last_name", "Coupland");

It’s very useful when your table is fairly big.

What else?

Setting table name (dbAssert.table(“tableName”)) resets already set conditions the same is true for dbAssert.sourceName(“sourceName”).Using dbAssert.condition(“key”, “value”) resets previously set conditions as well. Note: dbAssert.addCondition(“key”, “value”) DOESN’T reset previously defined cond’s.

It’s intended only for database mapping verifications. It’s not a general-purpose testing class. You’ll still need to write jUnit tests. Sorry. :) I want to keep it as simple as possible. I would consider it as just another type of assertion statement you can use in your jUnit tests with already existing ones but for testing data in database. I like it more than DBUnit of course I do it’s implemented by myself. ;)