Feeds

Get into data with Groovy

Part 2: Object grabber

Combat fraud and increase customer satisfaction

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 }

SANS - Survey on application security programs

More from The Register

next story
This time it's 'Personal': new Office 365 sub covers just two devices
Redmond also brings Office into Google's back yard
Oh no, Joe: WinPhone users already griping over 8.1 mega-update
Hang on. Which bit of Developer Preview don't you understand?
Microsoft lobs pre-release Windows Phone 8.1 at devs who dare
App makers can load it before anyone else, but if they do they're stuck with it
Half of Twitter's 'active users' are SILENT STALKERS
Nearly 50% have NEVER tweeted a word
Internet-of-stuff startup dumps NoSQL for ... SQL?
NoSQL taste great at first but lacks proper nutrients, says startup cloud whiz
IRS boss on XP migration: 'Classic fix the airplane while you're flying it attempt'
Plus: Condoleezza Rice at Dropbox 'maybe she can find ... weapons of mass destruction'
Ditch the sync, paddle in the Streem: Upstart offers syncless sharing
Upload, delete and carry on sharing afterwards?
New Facebook phone app allows you to stalk your mates
Nearby Friends feature goes live in a few weeks
Microsoft TIER SMEAR changes app prices whether devs ask or not
Some go up, some go down, Redmond goes silent
prev story

Whitepapers

Securing web applications made simple and scalable
In this whitepaper learn how automated security testing can provide a simple and scalable way to protect your web applications.
3 Big data security analytics techniques
Applying these Big Data security analytics techniques can help you make your business safer by detecting attacks early, before significant damage is done.
The benefits of software based PBX
Why you should break free from your proprietary PBX and how to leverage your existing server hardware.
Top three mobile application threats
Learn about three of the top mobile application security threats facing businesses today and recommendations on how to mitigate the risk.
Combat fraud and increase customer satisfaction
Based on their experience using HP ArcSight Enterprise Security Manager for IT security operations, Finansbank moved to HP ArcSight ESM for fraud management.