Getting Groovy with Apache Derby

I'm working my way through the so-far excellent Programming Groovy by Venkat Subramaniam. Since I'm on the database chapter, I decided it was time to install a database server on my machine. The examples in the book are done with MySQL, but I thought I would try the more lightweight Apache Derby.

I downloaded the 10.4.1.3 binary distribution:

$ tar xzf db-derby-10.4.1.3-bin.tar.gz -C /opt
$ export DERBY_HOME=/opt/db-derby-10.4.1.3-bin
$ java -jar $DERBY_HOME/lib/derbyrun.jar server start &
Security manager installed using the Basic server security policy.
Apache Derby Network Server - 10.4.1.3 - (648739) started and ready to accept connections on port 1527 at 2008-08-16 17:31:37.060 GMT

That was simple enough, I decided to create a couple aliases to make startup and shutdown easier:

alias startderby="java -jar $DERBY_HOME/lib/derbyrun.jar server start &"
alias stopderby="java -jar $DERBY_HOME/lib/derbyrun.jar server shutdown &"

Running Derby's ij tool, I was able to create a database with a script similar to the one in the book, but tailored for Derby:

connect 'jdbc:derby://localhost:1527/peopledb;create=true;';

create table people (
    name varchar(100) not null,
    age integer not null
);

insert into people (name, age) values ('John', 26);
insert into people (name, age) values ('Jenny', 23);
insert into people (name, age) values ('Marshall', 6);

I could not find a method for determining whether or not a table exists before trying to create it in Derby. I ran the script with:

$ java -jar $DERBY_HOME/lib/derbyrun.jar ij ~/tmp/people.sql

ij version 10.4
ij> connect 'jdbc:derby://localhost:1527/peopledb;create=true;';
ij> create table people (
 name varchar(100) not null,
 age integer not null
 );
0 rows inserted/updated/deleted
ij> insert into people (name, age) values ('John', 26);
1 row inserted/updated/deleted
ij> insert into people (name, age) values ('Jenny', 23);
1 row inserted/updated/deleted
ij> insert into people (name, age) values ('Marshall', 6);
1 row inserted/updated/deleted

I was able to query the database with the following Groovy code (again modeled from the book):

import groovy.sql.Sql

def url    = 'jdbc:derby://localhost:1527/peopledb'
def driver = 'org.apache.derby.jdbc.ClientDriver'

def sql = Sql.newInstance(url, driver)
// println sql.connection.dump()

sql.eachRow('SELECT * from people') { person ->
    printf "%-20s%s\n", person.name, person.age
}

Command and results:

$ groovy -cp $DERBY_HOME/lib/derbyclient.jar People.groovy
John                26
Pam                 24
Marshall            6

Looks like I'm good to continue with the examples..

As an aside, the 10.4.1.3 download page also links for the Eclipse plugins. I've used the plugins for a 10.2 version of Derby for Eclipse 3.3, but not the 10.4 plugins with Eclipse 3.4 which I currently have. I could not get the plugins recognized in Eclipse 3.4, if someone has, if someone has, a comment would be greatly appreciated.