groovy语法基础--数据库

Groovy的groovy-sql模块提供了比当前Java的JDBC技术更高级的抽象。Groovy sql API支持各种各样的数据库，其中一些如下所示。

• HSQLDB
• Oracle
• SQL Server
• MySQL
• MongoDB

以MySQL为例：

1、创建MySQL连接，需要传入4个参数：url、user、password、driverClassName

def sql = Sql.newInstance(
                'jdbc:mysql://XXXXXXXXXXXXXX',
                'XXXXXXXX',
                'XXXXXXXXXXX',
                'com.mysql.jdbc.Driver')

提交操作

sql.commit()

回滚操作

sql.rollback()

断开连接

sql.close()

2、创建表

通过sql.execute方法，执行SQL语句

def sql = Sql.newInstance(
                'jdbc:mysql://XXXXXXXXXXXXXX',
                'XXXXXXXX',
                'XXXXXXXXXXX',
                'com.mysql.jdbc.Driver')
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.close() 

3、插入操作

def sql = Sql.newInstance(
                'jdbc:mysql://XXXXXXXXXXXXXX',
                'XXXXXXXX',
                'XXXXXXXXXXX',
                'com.mysql.jdbc.Driver')

sql.connection.autoCommit = false
		
def sqlstr = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)""" 
try {
   sql.execute(sqlstr);
   sql.commit()
   println("Successfully committed") 
}catch(Exception ex) {
   sql.rollback()
   println("Transaction rollback") 
}

sql.close() 

假如只需要插入部分列的值，可以通过${}传参

def sql = Sql.newInstance(
                'jdbc:mysql://XXXXXXXXXXXXXX',
                'XXXXXXXX',
                'XXXXXXXXXXX',
                'com.mysql.jdbc.Driver')

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") 
}

sql.close() 

4、查询操作

查询操作是通过使用SQL类的eachRow方法进行，语法为

eachRow(GString gstring, Closure closure) 

执行给定的SQL查询调用，结果集的每一行给出闭幕。

参数

• GString的 -这需要执行的SQL语句。

• 封闭 -封闭语句来处理来自读操作retrived行。执行给定的SQL查询调用，结果集的每一行给出闭幕。

def sql = Sql.newInstance(
                'jdbc:mysql://XXXXXXXXXXXXXX',
                'XXXXXXXX',
                'XXXXXXXXXXX',
                'com.mysql.jdbc.Driver')

sql.eachRow('select * from employee') {
         tp -> 
         println([tp.FIRST_NAME,tp.LAST_NAME,tp.age,tp.sex,tp.INCOME])
      }  

sql.close() 

结果输出为：

[Mac, Mohan, 20, M, 2000.0]

5、更新操作

def sql = Sql.newInstance(
                'jdbc:mysql://XXXXXXXXXXXXXX',
                'XXXXXXXX',
                'XXXXXXXXXXX',
                'com.mysql.jdbc.Driver')

sql.connection.autoCommit = false
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() 

6、删除操作

def sql = Sql.newInstance(
                'jdbc:mysql://XXXXXXXXXXXXXX',
                'XXXXXXXX',
                'XXXXXXXXXXX',
                'com.mysql.jdbc.Driver')

sql.connection.autoCommit = false
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()