Original URL: http://www.theregister.co.uk/2007/10/25/groovy_mysql/

Groovy way to MySQL

Get your Groove on

By Dr Pan Pantziarka

Posted in Developer, 25th October 2007 16:13 GMT

A very traditional niche of scripting languages, (such as Perl, Python or Ruby), has been in the ad hoc manipulation of databases – from grabbing data, transforming it, performing bulk updates, right on through to full-on data migration projects that move data from one platform or RDBMS to another. Scripting languages are perfect for these types of task because they are quick to write and test, can be used interactively while the problem space is being explored, and because extraneous features – such as graphical user interface, detailed user documentation and so on – are not required.

For hardcore Java hackers, much of this kind of ad hoc activity has either entailed writing utilities libraries or, more likely, dropping down into another language – from Unix shell scripts to the aforementioned Perl, Python, Ruby et al. Now, however, Groovy exists to provide all of the benefits of a dynamically-typed scripting language that is designed to be easily accessible to Java developers and which sits on top of the Java Virtual Machine (JVM) and, as a big bonus, can make use of existing Java APIs and packages.

In the case of database access, Groovy can be used both with the standard Java JDBC mechanism (which makes use of SQL) or with its own high-level SQL-less DataSet libraries. In this two-part hands-on tutorial we'll look at how you can use Groovy to quickly and easily interface with a MySQL database sitting on a remote server somewhere.

First things first, though, and that's to install Groovy and to check that it all runs smoothly. Groovy can be downloaded here - there are install packages for various platforms, including Windows, Linux, and a platform-independent zip.

Aside from dropping the files into the right place the only other things to do are set the GROOVY_HOME environment variable (and JAVA_HOME if it doesn't already exist), and to make sure that the \groovy\bin directory is on your path.

A quick way to test is simply to open a command shell or terminal and type:

groovy -v

This should come back with the versions of Groovy and Java that you have installed.

The only other thing you need to have in place is a JDBC driver for MySQL. The jar file needs to be on the classpath; simply copying the file to the \groovy\lib is enough to banish any problems.

For the purposes of this article we'll be looking at a simple database (called pers) which contains a single table, called users, which is represented by the following grid:

user_name email user_id
'tom' 'tom@here.com' 1
'dick' 'dick@there.co.uk' 2
'harry' 'harry@harry.com' 3
'george' 'hello@hello.org' 4

As with Java, Groovy makes use of JDBC to interact with the database. So we'll start off with some simple code to connect to and read from this database. Enter the following into a file called MySQL.groovy:

import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:mysql://192.168.16.175:3306/pers", "pan","regdev", "com.mysql.jdbc.Driver")
sql.eachRow("select * from users") {println "User name ${it.user_name}"}

The first thing to note is the import statement and the creation of the Sql object. The argument to the newInstance method broadly encapsulates the information needed to create a standard Java JDBC connection: database URL, user name, password, and JDBC driver class name.

Once we've got an Sql object we can use it to interact with the database using standard SQL syntax. In this example we are simply performing a basic select query to grab all of the data in the user's table. So far, not very different from Java, the Groovy code is less verbose for sure, but not so different to what we're used to. It's what follows that shows us a first taste of Groovy at work.

The eachRow method iterates through the rows returned by the select query, and this is combined with the closure code: {println "User name ${it.user_name}"}

Closures are blocks of code that are treated as objects and can be passed around, stored, or executed. In this case we are creating a block of code that is passed to the eachRow method, which then executes the function by binding the value of each row of data to the "it" variable. In this way we can access all of the column names in the returned rows directly. If we save this code as MySQL.groovy we can then execute it from the command line by typing:

groovy MySQL.groovy

which will return:

User name tom
User name dick
User name harry
User name george

This combination of SQL, easy iteration and the use of closures means it's possible to write extremely powerful but succinct scripts to process data from MySQL and other databases. The eachRow method isn't the only way we can access the data using the Sql class. As an alternative we can grab the rows and put them into a List as follows:

List u=sql.rows('SELECT * FROM users')
u.each {println it.user_id}

Again, we have an iteration and a closure providing fast access to processing the data.

However, the Sql class also provides a means to perform a wide variety of SQL operations, including INSERT, DELETE, UPDATE and various DDL activities.

We can use Groovy's triple quoted strings to encode SQL queries which we execute directly. For example:

sql.execute '''
  INSERT INTO users (user_name, email, user_id) 
  VALUES ('fred','fred@fred.com',99);'''
sql.execute '''
  DELETE FROM users 
  WHERE user_id>20
  '''

So far we've been using the Sql class to provide access to the database. In the next part of this tutorial we'll be looking at a different mechanism and at some practical examples of using Groovy scripting with MySQL to perform some common DBA activities. ®