一、创建数据库
（略），运行sql脚本即可、查询结果：
二、连接到数据库
注意：该处采用IntelliJ IDEA进行代码编写因此，步骤较原书多一点。
1、添加com.mysql.jdbc.Driver
将mysql-connector-java-5.1.7-bin.jar添加到lib下；将jar包右键add as library添加为库。结构如下图所示：
2、代码编写
sql=Sql.newInstance('jdbc:mysql://localhost:3306/weatherinfo','root','root',
'com.mysql.jdbc.Driver')
println sql.connection.catalog
3、结果显示：
weatherinfo
三、数据库的select操作
代码编写：
println'city :temperature'
sql.eachRow('SELECT*FROM weather;'){
println"${it[0]}:${it[1]}"//也可以是it.city等
}
结果展示：
city :temperature
Beijing :30
Tianjin :31
Guiyang :26
Shanghai :35
Guangzhou :39
Dalian :30
四、将数据转换成XML表示
代码编写：
def builder=new MarkupBuilder()
builder.weather{
sql.eachRow('SELECT * FROM weather;'){
city(name:it.city,temperature:it.temperature)
}
}
结果展示：
五、使用DataSet
使用Groovy的dataSet方法接收一个表名，返回一个DataSet实例，它作为一个虚拟代理，直到迭代时，才会去取出实际的行。例子：
代码调用：
println 'cities with higher temperature:'
def dataSet=sql.dataSet('weather')
def citiesWithHigherTemperature=dataSet.findAll{it.temperature>30}
citiesWithHigherTemperature.each{
println "${it[0]}:${it[1]}"
}
结果展示：
cities with higher temperature:
Tianjin:31
Shanghai:35
Guangzhou:39
六、插入与更新
我们可以使用两种方法来添加数据到数据库
1、使用DataSet
代码编写
println "number of cities before insert is${sql.rows('SELECT*FROM weather;').size()}"
dataSet.add(city:'Harbin ',temperature:10)
println "number of cities after insert is${sql.rows('SELECT*FROM weather;').size()}"
结果展示
number of cities before insert is 6
number of cities after insert is 7
2、使用sql类的execute方法或者executeInsert方法
代码编写
println "number of cities before insert is${sql.rows('SELECT*FROM weather;').size()}"
sql.execute("INSERT INTO weather(city, temperature) VALUES ('Shenzhen',39);")
println "number of cities after insert is${sql.rows('SELECT*FROM weather;').size()}"
结果展示
number of cities before insert is 8
number of cities after insert is 9
七、访问Microsoft Excel
groovy中可以用Sql类来访问Excel电子表格。由于JDK1.8已经删除ODBC相关功能，此处不再研究。若您有更好的解决办法，联系我
代码编写：
def sql=Sql.newInstance(
"""jdbc.odbc.Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};
DBQ = ../temperature.xls;
READONLY = false""",'','')
println"../temperature.xls"
sql.eachRow('SELECT*from [temperature$]'){
println "${it.city}:${it.temperature}"
}
异常展示：
Caught: java.sql.SQLException: No suitable driver found for jdbc.odbc.Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};
DBQ = ../temperature.xls;
READONLY = false
java.sql.SQLException: No suitable driver found for jdbc.odbc.Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};
DBQ = ../temperature.xls;
READONLY = false
at ExcelConnection.run(ExcelConnection.groovy:9)
《完》
