Initialising Derby Embedded Database before Tests

I am currently messing around with Netbeans Platform and with that has come the need to to run some UI tests and Integration tests against the Derby embedded database used by the application.

Netbeans has a really handy way of hooking into the build script for this stuff. So I modified the build.xml and added the following:

   driver="org.apache.derby.jdbc.EmbeddedDriver"
  url="jdbc:derby:/home/carl/euctest2.mdb"
                userid=""
                password=""
         classpath="${pathToDerby}derby.jar"
                autocommit="true"
                caching="true"
                onerror="continue">
                    
 

Now before running my JUnit tests the database would initialised.

With other databases, such as Oracle, I normally use the sql ant task which works great and is simple to set up. However in my JUnit tests I kept getting "Internal Exception: java.sql.SQLException: Failed to start database '/home/carl/euctest2.mdb' with class loader sun.misc.Launcher$AppClassLoader@2bbd86, see the next exception for details.
Error Code: 40000 .... ERROR XSDB6: Another instance of Derby may have already booted the database". So obviously the sql task was not closing its connection with derby correctly. With the Derby embedded database there can only ever be ONE connection and this was still being hogged by the ant task.

So next I googled and found this article. The solution here was to use custom ant tasks to close the connection with derby. But it ended up giving me the same problems. Not sure why. Maybe I did something silly when configuring something.

Anyway my workaround was to use the Ant exec task to run a script that connected to the database and ran my script. So instead of the sql task I had:

And the actual script:

java -cp derbytools.jar:derby.jar -Dij.database=jdbc:derby:/home/carl/euctest2.mdb org.apache.derby.tools.ij /pathToScripts/init.sql

NB: This script would have to be run from the same directory as the derbytools.jar and derby.jar.

And with that I was able to initialise my database before running the tests. And the tests were able to function correctly because the script was closing the connection with Derby correctly. Yay!