[FIXED] What is the best way to reset the database to a known state while testing database operations?

Issue

I'm writing tests with JUnit for some methods operating on a test database.

I need to reset the database to the original state after each @Test. I'm wondering what's the best way to do that.

Is there some method in the EntityManager? Or should I just delete everything manually or with an SQL statement? Would it be better to just drop and recreate the whole database?

Solution

One technique that I have used in the past is to recreate the database from scratch by simply copying the database from a standard 'test database', and using this in the tests.

This technique works if:

1. Your schema doesn't change much (otherwise it's a pain to keep in line)
2. You're using something like hibernate which is reasonably database independent.

This has the following advantages:

1. It works with code that manages its own transactions. My integration tests run under junit. For instance, when I'm testing a batch process I call Batch.main() from junit, and test stuff before and after. I wouldn't want to change the transaction processing in the code under test.
2. It's reasonably fast. If the files are small enough, then speed is not a problem.
3. It makes running integration tests on a ci server easy. The database files are checked in with the code. No need for a real database to be up and running.

And the following disadvantages:

1. The test database files need to be maintained along with the real database. If you're adding columns all of the time, this can be a pain.
2. There is code to manage the jdbc urls, because they change for every test.

I use this with Oracle as the production/integration database and hsqldb as the test database. It works pretty well. hsqldb is a single file, so is easy to copy.

So, in the @Before, using hsqldb, you copy the file to a location such as target/it/database/name_of_test.script. This is picked up in the test.

In the @After, you delete the file (or just leave it, who cares). With hsqldb, you'll need to do a SHUTDOWN as well, so that you can delete the file.

You can also use a @Rule which extends from ExternalResource, which is a better way to manage your resources.

One other tip is that if you're using maven or something like it, you can create the database in target. I use target/it. This way, the copies of databases get removed when I do and mvn clean. For my batches, I actually copy all of my other properties files etc into this directory as well, so I don't get any files appearing in strange places either.

Answered By - Matthew Farwell
Answer Checked By - Marilyn (JavaFixing Volunteer)