MySQL Connect and Query bootstrap

//see: http://groovy.codehaus.org/Tutorial+6+-+Groovy+SQL
@Grapes(
 @Grab(group='mysql', module='mysql-connector-java', version='5.1.26')
)
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql
String user = "root"
String pwd = "1234"

String fileTable = "mcScrap";
List fileCols = ['col1','test1col'];

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/onix21", user, pwd, "com.mysql.jdbc.Driver")

println "Connected!\n"

println "Found $fileTable ?: " + !!(sql.rows("SHOW TABLES LIKE ${fileTable}").size())

String command = """
CREATE TABLE IF NOT EXISTS `$fileTable` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`${fileCols[0]}` VARCHAR(45) NULL ,
`${fileCols[1]}` VARCHAR(45) NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `id_UNIQUE` (`id` ASC) );
"""
println command
println sql.execute(command)

command = "SELECT * FROM `$fileTable`"
println command
sql.eachRow(command) { println "$it.id -- ${it[fileCols[0]]} -- ${it[fileCols[1]]} --" }

println sql.execute("INSERT INTO `$fileTable` (${fileCols.join(',')}) VALUES (?,?);", ['1','1'])

command = "SELECT * FROM `$fileTable`"
println command
sql.eachRow(command) { println "$it.id -- ${it.col1} -- ${it.test1col} --" }

println sql.executeUpdate("UPDATE `$fileTable` SET ${fileCols[0]} = ? WHERE ${fileCols[1]}=1", [Math.random()])

List rows = []
sql.eachRow(command) { rows << (it.toRowResult() as HashMap) }
println "rows: " + rows.size()
println "type: " + rows[0].getClass().name
println "1st: " + rows[0]
println "all: " + rows

sqLite

@Grapes([    
   @Grab(group='org.xerial', module='sqlite-jdbc', version='3.7.2'),
   @GrabConfig(systemClassLoader=true)
 ])
import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.SQLException
import java.sql.Statement
import java.sql.PreparedStatement
import groovy.sql.Sql
import java.text.SimpleDateFormat

String databasefileName = "testDB.db" // this will write file to your system

Class.forName("org.sqlite.SQLite")
try {
    Sql.newInstance("jdbc:sqlite:"+databasefileName,"org.sqlite.JDBC")
    connection = DriverManager.getConnection("jdbc:sqlite:" + databasefileName)
    executeCommand = connection.createStatement().&executeUpdate

    executeCommand("drop table if exists myTable")

    String ct = "create table myTable (col1 string, col2 date, col3 string, col4 int)"
    executeCommand(ct)

    prepStatment = connection.prepareStatement("insert into myTable values(?, ?, ?, ?)")
} 
catch(SQLException e) { System.err.println("sql: " + e.getMessage()); return }

//populate table
updateSqLiteDB(connection, prepStatment)

Sql sql = new Sql(connection)
//run sql select statement
sql.eachRow("select * from myTable") {
    print(" col1 = " + it.getString("col1").padRight(5))
    print(" col2 = " + it.getDate("col2"))
    print(" col3 = " + it.getString("col3").padRight(15))
    println(" col4 = " + it.getInt("col4"))
}

//close connection
try { 
    if(connection != null) 
      connection.close();
} 
catch(SQLException e) { 
    // connection close failed. 
    System.err.println("sql: " + e) 
} 

////////////////////////////////END OF MAIN/////////////////////////////////

static void updateSqLiteDB(def connection, def prepStatment) {
    try {
        100.times { i ->
            String valCol1 = i.toString()
            Date valCol2 = (new java.sql.Date(111,0,15)) + i
            String valCol3 = (i.toString()) * 5
            Integer valCol4 = i * i
            prepStatment.setString(1, valCol1)
            prepStatment.setDate(2, valCol2)
            prepStatment.setString(3, valCol3)
            prepStatment.setInt(4, valCol4)
            prepStatment.addBatch()
        }
        connection.setAutoCommit(false);
        prepStatment.executeBatch();
        connection.setAutoCommit(true);
    }
    catch(SQLException e) { System.err.println("sql batch: " + e.getMessage()) } 
}