Feeds

Get into data with Groovy

Part 2: Object grabber

Beginner's guide to SSL certificates

Of course, under the surface there's still some SQL going on and we can look at it if we want to using the sql and parameters properties of the DataSet. For example, in the case of not_harry this maps to:

println not_harry.sql
println not_harry.parameters

Which gives us:

select * from users where (user_id > ? and user_name != ?)
[2, "harry"]

Can we go further with a DataSet? So far we've restricted ourselves to querying and filtering table data. What if we want to write data back to the database? The good news is that you can add new rows of data, the bad news is that deletes or updates are not yet implemented. We'll have to hope and wait for a future release that adds this functionality.

To illustrate the add row functionality we're going to implement a common enough scenario - we're going to populate our MySQL table with data that's been dumped from a spreadsheet or another RDBMS into a CSV file. It's the kind of data manipulation task that scripting languages traditionally excel at.

The first thing to do is work out how to grab the data from the CSV file and parse it correctly. Here's an extract from our users.csv file:

fred,fred@flintstone.com,10
barney,barney@rubble.net,11
wilma,wilma@flintstone.co.uk,12
bambam,bambam@bambam.org,13
betty,betty@betty.com,14

As should be clear, Groovy is big on iterators, and in this case we can just grab the file and iterate over each line, splitting it on the comma character. As a test we can run the following code:

new File('users.csv').splitEachLine(',') {fields ->
  println fields[0] + " " + fields[1] + " " + fields[2]
  }

Here each line is parsed, tokenised on the comma and bound to a variable that's called fields. The above code will cycle through each line of the file and print the different fields for us. What we want to do next is add each line of data to the database, but instead of INSERT queries we're going to use our DataSet directly:

new File('users.csv').splitEachLine(',') {fields ->
  ds.add(
    user_name: fields[0],
    user_id: fields[2],
    email: fields[1]
  )
}
  
ds.each { println it.user_name }

Running the above code will add the rows from the CSV file and then dump the list of user_names to verify that it's worked.

In just a few lines of code we've managed to read a file, parse it, and then add the data to a database, with minimal amounts of house-keeping code or boilerplate Java.

Of course, the fact the DataSet only works on tables and not on more complex structures (such as the result of a JOIN) means you can't get away from using SQL altogether, but Groovy makes it easy to mix and match approaches. And in the case of complex queries, it's fairly straightforward to use the Sql object to create a database view and then to use the DataSet object to access that.

In all then, Groovy offers a set of high-level objects that make database interaction a relative breeze. ®

Internet Security Threat Report 2014

More from The Register

next story
Nexus 7 fandroids tell of salty taste after sucking on Google's Lollipop
Web giant looking into why version 5.0 of Android is crippling older slabs
Be real, Apple: In-app goodie grab games AREN'T FREE – EU
Cupertino stands down after Euro legal threats
Download alert: Nearly ALL top 100 Android, iOS paid apps hacked
Attack of the Clones? Yeah, but much, much scarier – report
Microsoft: Your Linux Docker containers are now OURS to command
New tool lets admins wrangle Linux apps from Windows
Bada-Bing! Mozilla flips Firefox to YAHOO! for search
Microsoft system will be the default for browser in US until 2020
prev story

Whitepapers

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.
Getting started with customer-focused identity management
Learn why identity is a fundamental requirement to digital growth, and how without it there is no way to identify and engage customers in a meaningful way.
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?
Getting ahead of the compliance curve
Learn about new services that make it easy to discover and manage certificates across the enterprise and how to get ahead of the compliance curve.
Intelligent flash storage arrays
Tegile Intelligent Storage Arrays with IntelliFlash helps IT boost storage utilization and effciency while delivering unmatched storage savings and performance.