Groovy中的Groovy-sql API支持各种各样的数据库,包括:
以MySql为例,需要下载的jar包为:
mysql-connector-java-5.1.38-bin
以下代码连接了一个
的数据库
import java.sql.*;
import groovy.sql.Sql
class Example {
static void main(String[] args) {
// Creating a connection to the database
def sql = Sql.newInstance('jdbc:mysql://localhost:3306/TESTDB',
'testuser', 'test123', 'com.mysql.jdbc.Driver')
// Executing the query SELECT VERSION which gets the version of the database
// Also using the eachROW method to fetch the result from the database
sql.eachRow('SELECT VERSION()'){ row ->
println row[0]
}
sql.close()
}
}
写好sql的字符串。以sql实例的execute方法执行即可。
import java.sql.*;
import groovy.sql.Sql
class Example {
static void main(String[] args) {
// Creating a connection to the database
def sql = Sql.newInstance('jdbc:mysql://localhost:3306/TESTDB', 'testuser',
'test123', '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()
}
}
$定义一个参数。在执行sql语句的时候替换为值:
import java.sql.*;
import groovy.sql.Sql
class Example {
static void main(String[] args) {
// Creating a connection to the database
def sql = Sql.newInstance('jdbc:mysql://localhost:3306/TESTDB', 'testuser',
'test123', '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()
}
}
使用SQL类的eachRow方法进行读操作。
eachRow(GString gString, Closure closure)
import java.sql.*;
import groovy.sql.Sql
class Example {
static void main(String[] args) {
// Creating a connection to the database
def sql = Sql.newInstance('jdbc:mysql://localhost:3306/TESTDB', 'testuser',
'test123', 'com.mysql.jdbc.Driver')
sql.eachRow('select * from employee') {
tp ->
println([tp.FIRST_NAME,tp.LAST_NAME,tp.age,tp.sex,tp.INCOME])
} // [Mac, Mohan, 20, M, 2000.0]
sql.close()
}
}
UPDATE和DELETE操作都是写好SQL然后通过execute执行,但是需要commit()提交(在后文“提交”中介绍)。
事务是保持数据一致性的机制,包括以下四种属性:
为了保证事务机制,需要有:
sql.commit()
,用于通知数据库完成操作sql.close()
,用于还原更改记得断开连接:
sql.close()