#!/usr/bin/env groovy
import groovy.sql.Sql

sql = Sql.newInstance('jdbc:mysql://localhost:3306/test', 'root', '', 'com.mysql.jdbc.Driver')

/*println 'Show all tables'
sql.eachRow('show tables'){ row ->
	println row[0]	
}
println ''*/

println 'Create user table'
sql.execute('''
create table if not exists user(
	id int auto_increment primary key,
	name varchar(63) not null,
	password varchar(35) not null
)
''')
println ''

println 'Create category table'
sql.execute('''
create table if not exists category(
	id int auto_increment primary key,
	name varchar(63) not null,
	parent_id int null
)
''')
println ''

println 'Create product table'
sql.execute('''
create table if not exists product(
	id int auto_increment primary key,
	category_id int not null,
	name varchar(63) not null,
	price decimal(10,2) not null,
	description text null
)
''')
println ''

//Generate some data.
insertUser = 'insert into user(name, password) values(?, ?)'	
for(idx in 1..3){
	println "Inserting user: tester$idx"
	//sql.executeInsert(insertUser, ["tester", "a"]) //Doesn't work???
	sql.execute(insertUser, ["tester" + idx, "a"]) 
}

insertCategory = 'insert into category(name, parent_id) values(?,?)'
for(idx in 1..3){
	println "Inserting category: cat$idx"
	sql.execute(insertCategory, ["cat$idx".toString(), null]) //toString is needed to persist.
}
lastCatId = sql.firstRow('select last_insert_id()')[0]

insertProduct = 'insert into product(category_id, name, price, description) values(?,?,?,?)'
for(idx in 1..50){
	println "Inserting product: product$idx"
	sql.execute(insertProduct, [lastCatId, "product"+idx, idx * Math.random(), "Just a test product item."])
}

GroovySQL with Mysql db

mysql

Groovy