GroovyでのDB処理

@GrabConfig(systemClassLoader=true)
@Grapes(
//    @Grab("postgresql:postgresql:9.1-901.jdbc4")
    @Grab("mysql:mysql-connector-java:6.0.5")
)
import groovy.sql.Sql

def dbServer = "localhost"
def dbName = "test"
def dbPort = "3309"
//def url = "jdbc:postgresql://${dbServer}:${dbPort}/${dbName}"
def url = "jdbc:mysql://${dbServer}:${dbPort}/${dbName}"
def user = "yamap"
def password = "yamap"
//def driver = "org.postgresql.Driver"
//def driver = "com.mysql.jdbc.Driver"
def driver = "com.mysql.cj.jdbc.Driver"
def sql = Sql.newInstance(url, user, password, driver)

println "テーブル作成"
sql.execute "create table data (id varchar(50),name varchar(50))"

println "一括で取得".center(30,"-")
// ArrayList<GroovyRowResult>で返ってきます。
def rows = sql.rows("select * from data")
println rows
// 中身取得する時はこんな感じ
println rows[0].name

println "条件指定".center(30,"-")
println sql.rows("select * from data where name = ?", ["yamada"])

println "条件複数指定".center(30,"-")
println sql.rows("select * from data where name = ? and id = ?", ["yamada",1])

println "複数のMapで条件設定".center(30,"-")
println sql.rows("select * from data where name = ?1.piyo and id = ?2.huga", [piyo:"yamada"],[huga:1])

println "こんな置き換えもできる_1".center(30,"-")
println sql.rows("select * from data where name=:piyo", [piyo:"yamada"])

println "こんな置き換えもできる_2".center(30,"-")
class MyClass {
  def hoge = 1
}
println sql.rows("select * from data where name=?2.piyo and id=?1.hoge", new MyClass(), [piyo:"yamada"])

println "一行づつ取得".center(30,"-")
// rowsではできた、Mapとかクラスで指定する事はできないようです。
sql.eachRow("select * from data where name = ?", ["yamada"]) { 
  println it
}

println "先頭行を取得".center(30,"-")
println sql.firstRow("select * from data order by id")

println "挿入1".center(30,"-")
sql.executeUpdate("insert into data (id, name) values (?, ?)", ["99","don takosu"])
println sql.rows("select * from data where id = ?", ["99"])

println "挿入2_Mapで設定".center(30,"-")
def dataSet2 = sql.dataSet('data')
dataSet2.add (
  id:'666',
  name:'takahashi'
)
println sql.rows("select * from data where id = ?", ["666"])

// 削除は挿入と同じ
println "削除1".center(30,"-")
sql.execute("delete from data where id='99' and name='don takosu'")
println sql.rows("select * from data where id = ?", ["99"])

println "削除2".center(30,"-")
sql.executeUpdate("delete from data where id=:piyo and name=:huga",[piyo:"666", huga:"takahashi"])
println sql.rows("select * from data where id = ?", ["666"])

// 参考URL
// http://groovy.codehaus.org/api/groovy/sql/Sql.html
// http://d.hatena.ne.jp/fumokmm/20090505/1241504105