Groovy. Batch. Prepared statement. Nice!

August 31, 2011
By

(This article was first published on novyden, and kindly contributed to R-bloggers)

Scripting with Groovy is exciting thing. You get a feeling that the language was inspired by an Oracle who read your mind and then made it 10 times better. So imagine how I felt after finding out that Groovy can NOT batch prepared statements.

Batching sql updates, inserts or deletes is one of the top features that database scripts would need. Without prepared statements I had to resort to generating SQL with GString:

sql.withBatch { stmt ->

mymap.each { k,v ->
stmt.addBatch("""UPDATE some_table
SET some_column = '${v}'
WHERE id = ${k} """)
}
}

Besides SQL injection this presents the problem of escaping strings in SQL: big pain in some cases. By arguing that injection is not an issue for internal script (it’s not in a wild on the web after all) you would leave yourself with the loop hole anyway. Don’t forget about performance. The bottom line: I need support for prepared statements!

I would have to stop here joining ranks of complaints like this if not for Groovy 1.8.1. This latest stable version (as of today) addresses bunch of bugs and just couple of features. And one of two is batch support for prepared statements. Below is secure and reliable (as well as more readable) version with batch support for prepared statement in 1.8.1:

sql.withBatch(20, """UPDATE some_table 

SET some_column = ?
WHERE id = ? """) { ps ->

mymap.each { k,v ->
ps.addBatch(v, k)
}
}

You can find more options on how use batching with prepared statements in Groovy 1.8.1 docs.

To leave a comment for the author, please follow the link and comment on their blog: novyden.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Search R-bloggers


Sponsors

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)