Performing queries and updates with GSQL

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 havent 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.