/**
https://www.w3cschool.cn/groovy/groovy_database.html
**/
import java.sql.*; 
import groovy.sql.Sql 


def sql = Sql.newInstance("jdbc:mysql://localhost:3306/TESTDB", 
         "testuser", "test123", "com.mysql.jdbc.Driver")
			

sql.eachRow("SELECT VERSION()"){ row ->
    println row[0]
}

//创建表
def sqlstr = """CREATE TABLE EMPLOYEE ( 
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(1),
    INCOME FLOAT )""" 
                    
sql.execute(sqlstr);

//新增数据/事务
sql.connection.autoCommit = false        
def firstname = "Mac"
def lastname ="Mohan"
def age = 20
def sex = "M"
def income = 2000  

def sqlstr = "INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, 
    INCOME) VALUES " + "(${firstname}, ${lastname}, ${age}, ${sex}, ${income} )"
    
try {
    sql.execute(sqlstr);
    sql.commit()
    println("Successfully committed") 
} catch(Exception ex) {
    sql.rollback()
    println("Transaction rollback")
}

//更新/事务
def sqlstr = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M'" 	  
try {
    sql.execute(sqlstr);
    sql.commit()
    println("Successfully committed")
}catch(Exception ex) {
    sql.rollback() 
    println("Transaction rollback")
}

//删除/事务
def sqlstr = "DELETE FROM EMPLOYEE WHERE AGE > 20"   
try {
    sql.execute(sqlstr);
    sql.commit()
    println("Successfully committed")
}catch(Exception ex) {
    sql.rollback()
    println("Transaction rollback")
}
 
sql.close()  