I've been having quite a bit of trouble setting up a JUnit test case that uses Derby and DbUnit. I've searched and searched on Google for answers and put bits and pieces together but nothing seemed to be solving my exact situation.
First of all I'm dealing with a fairly old application that was written in Java long ago and has very little JUnit in it. We've been trying to improve this by adding JUnit when possible. So I was assigned the task of changing some SQL and I figured this would be a good opportunity to add the use of an in-memory database.
We use DB2, so a natural fit is to use Derby since the SQL should be fairly similar. I have used HSQLDB a few times in the past and don't remember running into too many issues getting it to work. With Derby, however, I ran into a couple of snags. I thought I'd capture some of them here in the hopes that someone will chime in with better ways to do it or in the slim chance this might actually help someone else.
My first challenge was trying to get around the code being tied to a datasource and not running in a container with JNDI made that something I wanted to avoid. After making a few changes to the service implemenation to make it have the datasource injected I finally got around the need for a mock implementation of JNDI.
Then next challenge was getting Derby to work. I started by grabbing the Derby jar that is included with RAD 7.0 in the shared folders. I came to find out that this version of Derby does not support doing a memory only DB. Upon running the basic JUnit test case I created so far I saw it generate a log file and a whole directory full of files for the database. I figured this isn't good for JUnit because you'd have to delete the directory after each test run. In frustraion, I researched a bit and found that starting with Derby 10.5 there is support for an in-memory backend. I downloaded the newer version and ran again. What!?! It is still generating the file system. I did some more research and determined I needed to change the connection uri to "jdbc:derb:memory:derbyDB;create=true". Reran the test and this time the directory was not created.
It feels like I'm making some progress, so I thought I'd add another simple test case. This caused some new issues because Derby didn't appear to be cleaning up between test cases. Probably uses a singleton for the in memory database, so not restarting the JVM caused the table to already be there. To get around this I created a method that would create a random connection URI so that each test run used its own schema.
Looking back at the work I did, the setUp method was creating the table. This probably could have been changed so the table was created in the constructor and then have the setUp or tearDown just empty the table out. Then I wouldn't need to have the random connection URI.
Anyway, here is a link to the project which can be built with Maven.