Get into data with Groovy

Part 2: Object grabber


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


Other stories you might like

  • North Korea pulled in $400m in cryptocurrency heists last year – report

    Plus: FIFA 22 players lose their identity and Texas gets phony QR codes

    In brief Thieves operating for the North Korean government made off with almost $400m in digicash last year in a concerted attack to steal and launder as much currency as they could.

    A report from blockchain biz Chainalysis found that attackers were going after investment houses and currency exchanges in a bid to purloin funds and send them back to the Glorious Leader's coffers. They then use mixing software to make masses of micropayments to new wallets, before consolidating them all again into a new account and moving the funds.

    Bitcoin used to be a top target but Ether is now the most stolen currency, say the researchers, accounting for 58 per cent of the funds filched. Bitcoin accounted for just 20 per cent, a fall of more than 50 per cent since 2019 - although part of the reason might be that they are now so valuable people are taking more care with them.

    Continue reading
  • Tesla Full Self-Driving videos prompt California's DMV to rethink policy on accidents

    Plus: AI systems can identify different chess players by their moves and more

    In brief California’s Department of Motor Vehicles said it’s “revisiting” its opinion of whether Tesla’s so-called Full Self-Driving feature needs more oversight after a series of videos demonstrate how the technology can be dangerous.

    “Recent software updates, videos showing dangerous use of that technology, open investigations by the National Highway Traffic Safety Administration, and the opinions of other experts in this space,” have made the DMV think twice about Tesla, according to a letter sent to California’s Senator Lena Gonzalez (D-Long Beach), chair of the Senate’s transportation committee, and first reported by the LA Times.

    Tesla isn’t required to report the number of crashes to California’s DMV unlike other self-driving car companies like Waymo or Cruise because it operates at lower levels of autonomy and requires human supervision. But that may change after videos like drivers having to take over to avoid accidentally swerving into pedestrians crossing the road or failing to detect a truck in the middle of the road continue circulating.

    Continue reading
  • Alien life on Super-Earth can survive longer than us due to long-lasting protection from cosmic rays

    Laser experiments show their magnetic fields shielding their surfaces from radiation last longer

    Life on Super-Earths may have more time to develop and evolve, thanks to their long-lasting magnetic fields protecting them against harmful cosmic rays, according to new research published in Science.

    Space is a hazardous environment. Streams of charged particles traveling at very close to the speed of light, ejected from stars and distant galaxies, bombard planets. The intense radiation can strip atmospheres and cause oceans on planetary surfaces to dry up over time, leaving them arid and incapable of supporting habitable life. Cosmic rays, however, are deflected away from Earth, however, since it’s shielded by its magnetic field.

    Now, a team of researchers led by the Lawrence Livermore National Laboratory (LLNL) believe that Super-Earths - planets that are more massive than Earth but less than Neptune - may have magnetic fields too. Their defensive bubbles, in fact, are estimated to stay intact for longer than the one around Earth, meaning life on their surfaces will have more time to develop and survive.

    Continue reading

Biting the hand that feeds IT © 1998–2022