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()