Derby

From APIDesign

Derby (also known as Java DB) is a lightweight, embeddable SQL database. It is very useful while running tests. I decided to use it to demonstrate functionality of the LiveDB project.

Derby and Ant

It took me about two days to find out how to configure the Derby database through Ant build script. I wanted the Ant script to start the database, create there a table, fill it with some sample data and then compile and execute associated unit tests. Then the database shall be discarded.

Embed or Connect?

For a while I was oscillating between using embedded driver vs. starting the server and connecting to it via socket. At the end the embedded driver turned to be easier to use. One of its benefits is that one does not need to start separate process. Whenever one needs a database, it is enough to create the driver and use it (but don't forget to shut the database down if you modify it):

Code from build.xml:

<target name="-check-db">
    <property name="db" location="build/classes/db"/>
    <available property="db.exists" file="${db}"/>
</target>
<target name="create-db" unless="db.exists" depends="init,-check-db">
    <mkdir dir="${db}"/>
    <delete dir="${db}"/>
    <echo message="Creating DB in ${db}"/>
    <sql classpath="${file.reference.derby.jar}" userid="j1" password="j1" 
        url="jdbc:derby:${db};create=true" 
        driver="org.apache.derby.jdbc.EmbeddedDriver"
    >
    create table APP.AGE (
        NAME VARCHAR(30),
        AGE NUMERIC(3)
    );
    insert into APP.AGE values ('apidesign', 3);
    </sql>
    <!-- don't forget to shutdown the DB -->
    <sql classpath="${file.reference.derby.jar}" userid="j1" password="j1" 
        url="jdbc:derby:${db};shutdown=true" 
        driver="org.apache.derby.jdbc.EmbeddedDriver" 
        onerror="continue"
        errorproperty="ignore.error" 
        failonconnectionerror="false"
    >none</sql>
    <echo message="DB created OK."/>
</target>
 

Shut the Database Down

The last problem that I had to face was inconsistent state of the database. To eliminate that, you need to shut the database down. As far as I can tell this cannot be done in Ant (version 1.8.1) without having an <sql> target that fails and print a nasty warning. In spite of that I consider this more elegant than writing own Ant task (as suggested elsewhere on the blogosphere; btw. that article is from year 2004, so it clearly shows how big process Ant made since then).

On Classpath Database

You may notice that I am creating the database inside build/classes directory. E.g. on the application classpath. This allows my tests to reference the database with location agnostic jdbc:derby:classpath:db URL (the last db is relative path to root of classpath elements).

Perfect Match

Derby comes with huge amount of documentation. However it seems to cover everything else than usage in unit tests and scripting with Ant. I could not google out any sufficient how to, thus I wrote this one. I hope it will be useful for those who want to merge Ant and Derby database in similar ways.

<comments/>