Introduction to GSQL (Groovy SQL)

In this article I cover working with SQL from Groovy using the GSQL support built into the language. This is not a complete guide, rather a getting started guide, that shows you a few of the concepts. Firstly, GSQL is built on top of JDBC; which you are probably already familiar with. If not there are many good web guides and books on the subject. Lets get started looking a how to configure up GSQL and execute some statements.

Making the JDBC driver available

Before we can do anything else we need to establish a connection with a database. In order to do this we need to ensure that the appropriate JDBC driver is available on the classpath. If you are using groovy scripting, you can place the driver in $GROOVY_HOME/lib or you can edit $GROOVY_HOME/conf/groovy-starter.conf and add an extra entry.

Creating a connection to the database

Now we have the driver on the classpath, we need to make a connection to the database that we can use to execute statements against. If you’ve done this before with JDBC directly, it can be quite long winded. However lets look at how groovy does things:

//
// Here we create an sql instance - that we will use to interact with the database, make
// sure you have HSQL on your classpath for this.
// 
def sql = Sql.newInstance("jdbc:hsqldb:mem:testdb", "sa", "", "org.hsqldb.jdbcDriver")

Executing a DDL statement - create table.

OK, so in the last step we managed to connect to the database, now we need to actually create a table. What we need to do is execute an SQL statement against the database. Lets take a look at how this is done:

sql.execute("""
    create table atable ( 
        name varchar(50),
        address varchar(100)
    )""")

Inserting data into the database

Using the same method as before, we will use the execute command to call insert for each record.

sql.execute("insert into atable(name, address) values(’dave’, ’some address’)")
sql.execute("insert into atable(name, address) values(’joe’, ’another address’)")
sql.execute("insert into atable(name, address) values(’fred’, ’yet another address’)")

Moving on from the previous page, we now have the table created and populated with several rows of data, each of the examples below assumes that you have already performed the steps on page 1 of this article.

Perform a query and iterate over the rows

Here we will execute a simple select statement and iterate the results. As we iterate the results notice that we treat each column as a property of the result set object. Isn’t that somewhat easier than JDBC!

sql.rows("select * from atable").each { row ->
    println("atable [ ${row.name}, ${row.address} ]")
}

Query with GSQL and generate an XML document

Now we exend on the above example to show how easily we can combine Groovy’s XML and GSQL support. We use GSQL to read the rows as above, but then use a MarkupBuilder to generate the XML. If you haven't looked at our article on Groovy XML yet, a MarkupBuilder is used to produce XML using Groovy’s builder support.

def writer = new StringWriter(500); 
def xml = new MarkupBuilder(writer);
xml.atableRecords {
    sql.rows("select * from atable").each { row ->
        atable( ’name’ : row.name, ’address’ : row.address)
    }
}

println writer;

Updating and deleting using GSQL

Lastly for this article, we investigate how to update and delete rows using GSQL’s executeUpdate method. This method returns the number of rows affected by the statement. Lets take a look at how it works:

int rowsAffected = sql.executeUpdate("update atable set address = ’secret address’")
println ("This update modified ${rowsAffected} rows") 

Final notes

What should be noted is that in all these examples we did not catch exceptions, most of the SQL methods that we called can fail, and would probably throw an exception when they did. However, for many scripts and simple cases, catching the exception is not necessary; instead we just need the groovy console to produce an error message when something goes wrong. In the event you do want to catch the exception just wrap the calls in a try..catch block.