Feeds

Get into data with Groovy

Part 2: Object grabber

Boost IT visibility and business value

Hands on In the first part of this two-part series we looked at how Groovy provides a simple and intuitive approach to accessing MySQL. Compared to Java, Groovy is less verbose and more focused on what the developer wants to do with the database.

Additionally, things like opening and closing database connections, writing boilerplate code to handle exceptions and other house-keeping activities are hidden from the developer.

However, there's more to Groovy's database abilities than syntactic sugar sweetening Java's JDBC architecture. Having used the Sql object, let's turn to Groovy's DataSet object.

Where the Sql object uses SQL to interact with the database, the DataSet hides SQL completely, and instead grabs rows of data, each of which is stuffed directly into a map - the data structure also known as a dictionary or associative array in other languages. A map stores data as key/value pairs, and in this particular case the keys are database fields and the values are data points.

A quick example will make all of this clear, and as before we'll work with our users table from the pers database. We create a DataSet as follows:

import groovy.sql.Sql
import groovy.sql.DataSet
def sql = Sql.newInstance("jdbc:mysql://192.168.16.175:3306/pers", "pan","regdev", "com.mysql.jdbc.Driver")
def ds=sql.dataSet('users')

We connect to MySQL using Sql.newInstance and then use the dataSet method to create the DataSet. The first thing to note is that instead of a SQL query we just give the name of the table, and it's the complete table that is returned. We can take a look at the data using the rows method as follows:

x=ds.rows()
x.each { println it }

Putting the previous code into a file called ds.groovy and running it from the command-line gives us the following result:

["user_name":"tom", "user_id":1, "email":"tom@here.com"] ["user_name":"dick", "user_id":2, "email":"dick@there.co.uk"] ["user_name":"harry", "user_id":3, "email":"harry@harry.com"] ["user_name":"george", "user_id":4, "email":"hello@hello.org"]

In other words, each row contains a map of key: value pairs, where the key is the field name and the value is the content of that field for the record.

So far so good, but how much value is there in simply being able to grab complete tables from MySQL into a Groovy data structure? Plenty.

Firstly, we can access individual columns in a very straightforward manner. Want to grab all of the user names? Try this:

x.each {println it.user_name}

How about some filtering of data? Say we want to grab only those users who have a user_id > 2. Rather than doing a SELECT WHERE query, we can use the DataSet directly:

over_2 = x.findAll { it.user_id > 2 }
over_2.each { println it.user_name }

All of this without having to requery the data. And you can chain query clauses, say you want all users with a user_id >2 and a user_name not equal to harry:

not_harry = ds.findAll { it.user_id > 2 && it.user_name != 'harry' }
not_harry.each { println it.user_name }

Build a business case: developing custom apps

More from The Register

next story
KDE releases ice-cream coloured Plasma 5 just in time for summer
Melty but refreshing - popular rival to Mint's Cinnamon's still a work in progress
Leaked Windows Phone 8.1 Update specs tease details of Nokia's next mobes
New screen sizes, dual SIMs, voice over LTE, and more
PEAK LANDFILL: Why tablet gloom is good news for Windows users
Sinofsky's hybrid strategy looks dafter than ever
Fiendishly complex password app extension ships for iOS 8
Just slip it in, won't hurt a bit, 1Password makers urge devs
Mozilla keeps its Beard, hopes anti-gay marriage troubles are now over
Plenty on new CEO's todo list – starting with Firefox's slipping grasp
Apple: We'll unleash OS X Yosemite beta on the MASSES on 24 July
Starting today, regular fanbois will be guinea pigs, it tells Reg
Another day, another Firefox: Version 31 is upon us ALREADY
Web devs, Mozilla really wants you to like this one
prev story

Whitepapers

Implementing global e-invoicing with guaranteed legal certainty
Explaining the role local tax compliance plays in successful supply chain management and e-business and how leading global brands are addressing this.
Boost IT visibility and business value
How building a great service catalog relieves pressure points and demonstrates the value of IT service management.
Why and how to choose the right cloud vendor
The benefits of cloud-based storage in your processes. Eliminate onsite, disk-based backup and archiving in favor of cloud-based data protection.
The Essential Guide to IT Transformation
ServiceNow discusses three IT transformations that can help CIO's automate IT services to transform IT and the enterprise.
Maximize storage efficiency across the enterprise
The HP StoreOnce backup solution offers highly flexible, centrally managed, and highly efficient data protection for any enterprise.