Groovy. Batch. Prepared statement. Nice!

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.