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

DbUnit will meet your needs. One thing to watch out for is that they have switched to using SLF4J as their logging facade instead of JCL. You can configure SLF4J to forward the logging to JCL but be warned if you are using Maven DbUnit sucks in their Nop log provider by default so you will have to use an exclusion, I blogged about this conflict recently.

Answer 2

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.

Answer 3

I use DbUnit, but also I work very hard to not to have to test against the DB. Tests that go against the database should only exist for the purpose of testing the database interface. So I have Mock Db Connections that I can set the data for use in all the rest of my tests.

Answer 4

If you are using Maven, one option is to use the sql-maven-plugin. It allows you to run database initialization/population scripts during the maven build cycle.

Answer 5

Apart from the already suggested DBUnit, you may want to look into Unitils. It uses DBUnit, but provides more than that (quoting from the site):

• Automatic maintenance of databases, with support for incremental, repeatable and post processing scripts
• Automatically disable constraints and set sequences to a minimum value
• Support for Oracle, Hsqldb, MySql, DB2, Postgresql, MsSql and Derby
• Simplify test database connection setup
• Simple insertion of test data with DBUnit * Run tests in a transaction
• JPA entity manager creation and injection for hibernate, toplink and * Hibernate SessionFactory creation and session
• Automatically test the mapping of JPA entities / hibernate mapped objects with the database