Junit: test query classes

Question

I need to write some tests with JUnit to test our classes that access the database, some are pretty simple (select a list of items) others insert new items, you know basic SQL stuff.

Now my question is, what are some things that I need to keep in mind when writing my tests?

For example: database class fetches items from a table, I could test it with valid ids, invalid ids but what else?

So, to summarize: how do you best test your query classes?

It has been a while since I wrote any tests so I am a bit rusty :)

Answer 1

If you're just testing SQL queries, you can

• Run each one of the queries in a success scenario to check that you have the correct statements
• (as you mentioned) select invalid ids
• Insert duplicate records (by PK or unique key)
• Ensure you can't put a null in non-nullable fields.
• Delete invalid ids (should it fail? or just ignore that the row doesn't exist?)
• Validate any checks you have on a column
• Maybe any validation you have to prevent a String to be stored in a shorter varchar column?

I've never done more than the first bullet point for queries, but I can see some value in testing some extra scenarios.

One good practice for this type of integration tests, is to be able to wipe out the DB an insert any data you need for each test, as this will guarantee that each test is isolated. If you're using java you can use a framework such as DBUnit to do this. Another good practice is that the wipe out of the DB and creation of the test data is done before you run each test rather than after. This has the benefit that if a test fails, you'll have the DB in the state when the test failed.

Something else you can test is any kind of data transformation you do at the DAO level (e.g. a varchar column with a list of comma separated values, and you transform this to a List, rather than leaving it as a string).