Advanced Search

GroovySQL with Mysql db

/ Published in: Groovy
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. #!/usr/bin/env groovy
  2. import groovy.sql.Sql
  3.  
  4. sql = Sql.newInstance('jdbc:mysql://localhost:3306/test', 'root', '', 'com.mysql.jdbc.Driver')
  5.  
  6. /*println 'Show all tables'
  7. sql.eachRow('show tables'){ row ->
  8. 	println row[0]	
  9. }
  10. println ''*/
  11.  
  12. println 'Create user table'
  13. sql.execute('''
  14. create table if not exists user(
  15. 	id int auto_increment primary key,
  16. 	name varchar(63) not null,
  17. 	password varchar(35) not null
  18. )
  19. ''')
  20. println ''
  21.  
  22. println 'Create category table'
  23. sql.execute('''
  24. create table if not exists category(
  25. 	id int auto_increment primary key,
  26. 	name varchar(63) not null,
  27. 	parent_id int null
  28. )
  29. ''')
  30. println ''
  31.  
  32. println 'Create product table'
  33. sql.execute('''
  34. create table if not exists product(
  35. 	id int auto_increment primary key,
  36. 	category_id int not null,
  37. 	name varchar(63) not null,
  38. 	price decimal(10,2) not null,
  39. 	description text null
  40. )
  41. ''')
  42. println ''
  43.  
  44. //Generate some data.
  45. insertUser = 'insert into user(name, password) values(?, ?)'	
  46. for(idx in 1..3){
  47. 	println "Inserting user: tester$idx"
  48. 	//sql.executeInsert(insertUser, ["tester", "a"]) //Doesn't work???
  49. 	sql.execute(insertUser, ["tester" + idx, "a"]) 
  50. }
  51.  
  52. insertCategory = 'insert into category(name, parent_id) values(?,?)'
  53. for(idx in 1..3){
  54. 	println "Inserting category: cat$idx"
  55. 	sql.execute(insertCategory, ["cat$idx".toString(), null]) //toString is needed to persist.
  56. }
  57. lastCatId = sql.firstRow('select last_insert_id()')[0]
  58.  
  59. insertProduct = 'insert into product(category_id, name, price, description) values(?,?,?,?)'
  60. for(idx in 1..50){
  61. 	println "Inserting product: product$idx"
  62. 	sql.execute(insertProduct, [lastCatId, "product"+idx, idx * Math.random(), "Just a test product item."])
  63. }

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.