Derby

From APIDesign

Revision as of 06:46, 17 July 2010

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

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. 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:

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>
 

The last problem that I had to face was inconsistent state of a database. To eliminate that, you need to close the 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. However even this is easier than writing own Ant task (as suggested elsewhere on the blogosphere).

I could not google out any sufficient how to, thus I am writing this one. I hope it will be useful for those who want to merge Ant and Derby database.

<comments/>