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.
3 comments:
Thanks a lot buddy
«Scripting with Groovy is exciting thing»
Man, are you kidding?
I've never seen anything worse than a groove.
Post a Comment