Groovy Databasing With Apache Derby - Part 1

By Pan Pantziarka

Introducing Groovy

Groovy is an open-source, dynamic scripting language that sits on top of the Java Virtual Machine (JVM). It has been designed to be appealing to the Java developer, easy to pick up, understand and use. It can interoperate with existing Java packages and libraries, making it easy to mix and match code in Java and Groovy. In terms of language style, Groovy bears similarities to other dynamic languages, including Ruby and Python, and of course it has plenty of similarities with Java too.

Installation of Groovy is very straightforward - just head over to http://groovy.codehaus.org/ and download the appropriate package for your platform. Assuming you've got the files into the right place on your machine the only other things to do is set the GROOVY_HOME environment variable. It also depends on a JAVA_HOME environment variable and the addition of the \groovy\bin directory to your path. If all is well open a command shell and enter groovy -v to see a report of Groovy and Java version information.

In terms of databases, Groovy makes use of the standard JDBC mechanism, but makes life a whole lot easier in lots of ways. This tutorial looks at Groovy and databases, using the Apache Derby as the example database system.

Introducing Apache Derby

Apache Derby is a fully-featured relational database that is fully implemented in Java. It has a small footprint, but is powerful, SQL-compliant and can be run in server mode or can be used as an embedded database within an application. If you are new to Apache please take a look at our introductory tutorial first, not only will it ensure that you've got a functioning Derby installation, you'll also have a head start in the data required for this tutorial. And just to make sure that we've got a functioning Derby installation we can open a command shell, navigate to the <derby_home>\bin directory and enter sysinfo to get a report of version information.

Sample Database

We're going to use a simple database for this tutorial, and being a lazy developer, we're going to use the database developed for the Apache Derby tutorial mentioned above. If you've followed that then you should have the data, if not, here's the SQL you need to define the database and populate with some data:

CREATE TABLE USERS ( 
FIRST_NAME VARCHAR(30) NOT NULL,
LAST_NAME VARCHAR(30) NOT NULL,
EMP_NO INTEGER NOT NULL CONSTRAINT EMP_NO_PK PRIMARY KEY
);

CREATE TABLE PC (
TYPE VARCHAR(10) NOT NULL,
SERIAL VARCHAR(50),
OS VARCHAR(20),
EMP_NO INTEGER,
CODE_NO INTEGER NOT NULL CONSTRAINT CODE_NO_PK PRIMARY KEY
);

INSERT INTO USERS VALUES('Bill','Gates',1);
INSERT INTO USERS VALUES('Joe','Bloggs',2);
INSERT INTO USERS VALUES('Peter','Kropotkin',3);
INSERT INTO PC VALUES('Desktop','01010','Linux',1,1);
INSERT INTO PC VALUES('Laptop','101010','BSD',2,2);
INSERT INTO PC VALUES('Desktop','101010','XP',3,12);

SELECT * FROM USERS;

If you cut and paste all of that into a text file called dbdata.txt, we can use it to generate our database and data.

At the Derby ij prompt enter the following command:

ij> CONNECT 'jdbc:derby:/DerbyDB/AssetDB;create=true';

Once this has executed it will create an empty database in a directory called /DerbyDB. Now we want to create the tables and populate with data:

ij> run 'dbdata.txt'

If all has gone well this will finally come back with the results of the SELECT * FROM USERS query.

Now, finally, we're in a position to play with Groovying the database.

SQL Object

As mentioned already, Groovy makes use of the existing Java JDBC mechanism to interact with databases. However, the dynamic features of Groovy, along with the use of some syntactic sugar means that it's very easy to interact with databases. The starting point should be familiar enough though, and that's to establish the connection to the database. In our case we're going to use Apache Derby in embedded mode - in other words our code will run load and run the database directly.

Enter the following code into a file called derby.groovy:

import groovy.sql.*
import java.sql.*

String driver = "org.apache.derby.jdbc.EmbeddedDriver"
def sql = Sql.newInstance("jdbc:derby:/DerbyDB/AssetDB","","",driver)

println 'Using eachRow...'
sql.eachRow("select * from users") {println "User name ${it.first_name}"}

We can run this from the command line by entering:

groovy derby.groovy

If you get a java.lang.ClassNotFoundException error than it means that Groovy can't see the Derby driver on the classpath. A simple fix for this is just to copy the derby.jar file to the \lib directory.

Assuming all has gone well you should see the result:

Using eachRow..
User name Bill
User name Joe
User name Peter

What we've done is establish a connection to the database in much the same way as we do with standard JDBC. We create a connection URL that includes name of the JDBC driver class, and then pass that to the newInstance method of the Sql class. From there we use the eachRow method of the sql object, passing a simple select query as a string. As you'll discover, Groovy is big on iterators, and the eachRow method just iterates through each row of the results of the query string. The interesting bit is in the braces: {println "User name ${it.first_name}"}. Here we are defining a very simple function which prints the first_name field from the variable returned by eachRow. Note that the variable returned by eachRow is bound to the variable called it, and that we can access the field by name.

The simple function we defined is a very simple example of a closure. Closures are function objects which can be passed around, bound and executed with different bits of data. Closures, together with Groovy's iterators, make it easy to create powerful and flexible code that is very short but expressive. Note also in our simple piece of code that much of the scaffolding code that Java requires is eliminated. We don't need to worry about opening and closing resources, writing code to handle exceptions and so on.

What else can we do with a Sql object? We can use the rows method to grab the data and drop it into a List:

List u=sql.rows('SELECT * FROM users')
println 'Number of rows ' + u.size
u.each {println it.emp_no}

Here we're passing another SQL query, passing the result to a List, printing the number of rows and then in the last line using the each method to iterate through and print the emp_no field.

Here's another example that uses the eachRow method, but this time we're binding the value returned by each row to a variable called user inside the closure:

sql.eachRow('SELECT * FROM users') {user -> println user.first_name + ' ' + user.last_name}

Finally, the Sql class also has an execute method that can be used to execute SQL code directly, making it possible to run INSERT, UPDATE and DELETES in addition to the SELECT queries we've run so far.

Here we run a query to insert another value into our users table:

sql.execute '''INSERT INTO users (first_name, last_name, emp_no) 
               VALUES ('fred','flintstone',99)'''

>>Next Page: DataSet Object

TechBookReport Tutorials