Feeds

Get into data with Groovy

Part 2: Object grabber

Providing a secure and efficient Helpdesk

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 }

Beginner's guide to SSL certificates

More from The Register

next story
ONE MILLION people already running Windows 10
A third of them are doing it in VMs, but early feedback focuses on frippery
Sign off my IT project or I’ll PHONE your MUM
Honestly, it’s a piece of piss
Netscape Navigator - the browser that started it all - turns 20
It was 20 years ago today, Marc Andreeesen taught the band to play
Torvalds CONFESSES: 'I'm pretty good at alienating devs'
Admits to 'a metric ****load' of mistakes during work with Linux collaborators
Sway: Microsoft's new Office app doesn't have an Undo function
Content aggregation, meet the workplace ... oh
Do Moan! MONSTER 6-day EMAIL OUTAGE hits Domain Monster
Customers freaked out by frightful service
Ploppr: The #VultureTRENDING App of the Now
This organic crowd sourced viro- social fertiliser just got REAL
Return of the Jedi – Apache reclaims web server crown
.london, .hamburg and .公司 - that's .com in Chinese - storm the web server charts
NetWare sales revive in China thanks to that man Snowden
If it ain't Microsoft, it's in fashion behind the Great Firewall
prev story

Whitepapers

Forging a new future with identity relationship management
Learn about ForgeRock's next generation IRM platform and how it is designed to empower CEOS's and enterprises to engage with consumers.
Win a year’s supply of chocolate
There is no techie angle to this competition so we're not going to pretend there is, but everyone loves chocolate so who cares.
Why cloud backup?
Combining the latest advancements in disk-based backup with secure, integrated, cloud technologies offer organizations fast and assured recovery of their critical enterprise data.
High Performance for All
While HPC is not new, it has traditionally been seen as a specialist area – is it now geared up to meet more mainstream requirements?
Saudi Petroleum chooses Tegile storage solution
A storage solution that addresses company growth and performance for business-critical applications of caseware archive and search along with other key operational systems.