Unit Testing Database code using Hibernate and HSQLDB

I read this article today called Unit testing hibernate with HSQLDB I found this article really interesting because I have been writing some database code recently and I am always concerned about testing methods and code that uses database code.

HSQLDB sounds like just the thing I have been looking for, especially with its ability to create databases in memory. When I was talking to another developer about it they found the idea of a database in memory was a crazy idea. It does sound a bit bonkers but if you are only creating one table with a few rows that wouldn't take up much memory, especially when you think of amount of data you sometimes copy in the clipboard.

I have been up until now rather lazy when it comes to testing code that uses a database. I have just created the tests to connect to a database that works. I have to admit I sort of feel that going to the effort of creating and putting data into a database for testing is a bit much. The reason I think this is because database code or code that retrieves data from the database doesn't change very often and if it does you need a database up and running to make sure your changes work. So the Junit tests I have written can be picked up by the person making the change and pointed to a running database.

On the flip side of the coin it has been a slight concern in the back of my mind and one of those TODO's as soon as I got some time I would try and maybe try and write a script to build a database and fill it up. Now that I have found HSQLDB I think that if I can get this working then this should solve my problems. I think reading articles and books where they have set up a good system to test their database code makes my more dodgy method even worse but still we can strive to improve our code and tests.

When I tried it with hibernate, I couldn't quite connect to the database. I wasn't sure if this was because although I had the HSQLDB database running I wasn't pointing to the correct place or connecting correctly. Unfortunately I didn't have the time to investigate because I had already wasted enough time investigating why Hibernate didn't support the MS Access database and if there was a work around (sadly no joy there).

I like the way the article describes the problem and then goes through the why and the how of the solution. It is also a decent article on using hibernate, so if you haven't used hibernate yet, it is worth reading the article. The article is a simple and doable solution to testing database code. It has inspired me to give it another go and I think it is a sign of a good article that after reading it you believe you could also do what the author has done.

So one reason why I have blogged about this was to talk about testing database and to see if anyone comments on their experience and also so I can easily find the article again.