Feeds

Get into data with Groovy

Part 2: Object grabber

Secure remote control for conventional and virtual desktops

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. ®

Top 5 reasons to deploy VMware with Tegile

More from The Register

next story
Google+ goes TITSUP. But WHO knew? How long? Anyone ... Hello ...
Wobbly Gmail, Contacts, Calendar on the other hand ...
Preview redux: Microsoft ships new Windows 10 build with 7,000 changes
Latest bleeding-edge bits borrow Action Center from Windows Phone
UNIX greybeards threaten Debian fork over systemd plan
'Veteran Unix Admins' fear desktop emphasis is betraying open source
Microsoft promises Windows 10 will mean two-factor auth for all
Sneak peek at security features Redmond's baking into new OS
DEATH by PowerPoint: Microsoft warns of 0-day attack hidden in slides
Might put out patch in update, might chuck it out sooner
Google opens Inbox – email for people too stupid to use email
Print this article out and give it to someone techy if you get stuck
Redmond top man Satya Nadella: 'Microsoft LOVES Linux'
Open-source 'love' fairly runneth over at cloud event
prev story

Whitepapers

Cloud and hybrid-cloud data protection for VMware
Learn how quick and easy it is to configure backups and perform restores for VMware environments.
A strategic approach to identity relationship management
ForgeRock commissioned Forrester to evaluate companies’ IAM practices and requirements when it comes to customer-facing scenarios versus employee-facing ones.
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?
Three 1TB solid state scorchers up for grabs
Big SSDs can be expensive but think big and think free because you could be the lucky winner of one of three 1TB Samsung SSD 840 EVO drives that we’re giving away worth over £300 apiece.
Security for virtualized datacentres
Legacy security solutions are inefficient due to the architectural differences between physical and virtual environments.