Friday, August 23, 2013

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