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.

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

<target name="-check-db">
    <property name="db" location="target/classes/db"/>
    <available property="db.exists" file="${db}"/>
</target>
<target name="create-db" description="Create a database on classpath" 
    unless="db.exists" depends="-check-db"
>
    <mkdir dir="${db}"/>
    <delete dir="${db}"/>
    <echo message="Creating DB in ${db}"/>
    <sql classpath="${cp}" userid="j1" password="j1"
        url="jdbc:derby:${db};create=true" 
        driver="org.apache.derby.iapi.jdbc.AutoloadedDriver"
    >
    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="${cp}" userid="j1" password="j1"
        url="jdbc:derby:${db};shutdown=true" 
        driver="org.apache.derby.iapi.jdbc.AutoloadedDriver"
        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/>