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.