Feeds

Get into data with Groovy

Part 2: Object grabber

New hybrid storage solutions

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 }

Security for virtualized datacentres

More from The Register

next story
Not appy with your Chromebook? Well now it can run Android apps
Google offers beta of tricky OS-inside-OS tech
Greater dev access to iOS 8 will put us AT RISK from HACKERS
Knocking holes in Apple's walled garden could backfire, says securo-chap
NHS grows a NoSQL backbone and rips out its Oracle Spine
Open source? In the government? Ha ha! What, wait ...?
Google extends app refund window to two hours
You now have 120 minutes to finish that game instead of 15
Intel: Hey, enterprises, drop everything and DO HADOOP
Big Data analytics projected to run on more servers than any other app
New 'Cosmos' browser surfs the net by TXT alone
No data plan? No WiFi? No worries ... except sluggish download speed
prev story

Whitepapers

Providing a secure and efficient Helpdesk
A single remote control platform for user support is be key to providing an efficient helpdesk. Retain full control over the way in which screen and keystroke data is transmitted.
Top 5 reasons to deploy VMware with Tegile
Data demand and the rise of virtualization is challenging IT teams to deliver storage performance, scalability and capacity that can keep up, while maximizing efficiency.
Reg Reader Research: SaaS based Email and Office Productivity Tools
Read this Reg reader report which provides advice and guidance for SMBs towards the use of SaaS based email and Office productivity tools.
Security for virtualized datacentres
Legacy security solutions are inefficient due to the architectural differences between physical and virtual environments.
Secure remote control for conventional and virtual desktops
Balancing user privacy and privileged access, in accordance with compliance frameworks and legislation. Evaluating any potential remote control choice.