JUnit for database code

Question

I've been trying to implement unit testing and currently have some code that does the following:

1. query external database, loading into a feed table
2. query a view, which is a delta of my feed and data tables, updating data table to match feed table

my unit testing strategy is this:

I have a testing database that I am free to manipulate.

1. in setUP(), load some data into my testing db
2. run my code, using my testing db as the source
3. inspect the data table, checking for counts and the existence/non existence of certain records
4. clear testing db, loading in a different set of data
5. run code again
6. inspect data table again

Obviously I have the data sets that I load into the source db set up such that I know certain records should be added,deleted,updated, etc.

It seems like this is a bit cumbersome and there should be an easier way? any suggestions?

Answer 1

Is it your intent to test the view which generates the deltas, or to test that your code correctly adds, deletes and updates in response to the view?

If you want to test the view, you could use a tool like DBUnit to populate your feed and data tables with various data whose delta you've manually calculated. Then, for each test you would verify that the view returns a matching set.

If you want to test how your code responds to diffs detected by the view, I would try to abstract away database access. I imagine an java method to which you can pass a result set (or list of POJO/DTO's) and returns a list of parameter Object arrays (again, or POJO's) to be added. Other methods would parse the diff list for items to be removed and updated. You could then create a mock result set or pojo's, pass them to your code and verify the correct parameters are returned. All without touching a database.

I think the key is to break your process into parts and test each of those as independently as possible.