Feeds

Get into data with Groovy

Part 2: Object grabber

Protecting against web application threats using SSL

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 }

The next step in data security

More from The Register

next story
New 'Cosmos' browser surfs the net by TXT alone
No data plan? No WiFi? No worries ... except sluggish download speed
'Windows 9' LEAK: Microsoft's playing catchup with Linux
Multiple desktops and live tiles in restored Start button star in new vids
iOS 8 release: WebGL now runs everywhere. Hurrah for 3D graphics!
HTML 5's pretty neat ... when your browser supports it
Mathematica hits the Web
Wolfram embraces the cloud, promies private cloud cut of its number-cruncher
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
Mozilla shutters Labs, tells nobody it's been dead for five months
Staffer's blog reveals all as projects languish on GitHub
SUSE Linux owner Attachmate gobbled by Micro Focus for $2.3bn
Merger will lead to mainframe and COBOL powerhouse
iOS 8 Healthkit gets a bug SO Apple KILLS it. That's real healthcare!
Not fit for purpose on day of launch, says Cupertino
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.
WIN a very cool portable ZX Spectrum
Win a one-off portable Spectrum built by legendary hardware hacker Ben Heck
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.
Protecting users from Firesheep and other Sidejacking attacks with SSL
Discussing the vulnerabilities inherent in Wi-Fi networks, and how using TLS/SSL for your entire site will assure security.
Security for virtualized datacentres
Legacy security solutions are inefficient due to the architectural differences between physical and virtual environments.