Groovy代码示例 – 数据库连接及基本操作

数据库操作之插入数据

仍然可以使用之前提过的execute()方法执行sql插入操作。

插入一行数据：

sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"

对于插入数据，推荐使用executeInsert()方法而不是用execute()。该方法返回新插入数据的键值列表。不管是execute或是executeInsert方法都可以在SQL放置'?'参数占位符。如果这样将自动启用PreparedStatement以防止被SQL注入。下面的例子展示如何使用'?'参数占位符和参数列表：

使用参数占位符和参数列表插入一行数据

def insertSql = 'INSERT INTO Author (firstname, lastname) VALUES (?,?)'
def params = ['Jon', 'Skeet']
def keys = sql.executeInsert insertSql, params
assert keys[0] == [1]

此外，execute和executeInsert方法都支持GString。SQL中任何'$'符号都被看做是占位符。你可以使用转义符号'\'将'$'当作普通字符而不是占位符。executeInsert还允许提供主键列名的列表，当方法返回复合主键而我们却只想获取其中的部分主键列的时候，可以通过主键列名指定。以下代码展示通过GString传入SQL语句插入数据以及通过主键列名的列表获取返回的主键值。

def first = 'Guillaume'
def last = 'Laforge'
def myKeyNames = ['ID']
def myKeys = sql.executeInsert """
  INSERT INTO Author (firstname, lastname)
  VALUES (${first}, ${last})
""", myKeyNames
assert myKeys[0] == [ID: 2]

数据库操作之执行SQL以创建数据库表

可以使用execute()方法执行任意的SQL语句。接下来看一下如何创建数据库表。

执行execute()方法并传入要执行SQL语句是最简单的SQL执行方法。

创建表：

//以上省略sql实例化过程

//以下建立数据库表
sql.execute '''
  CREATE TABLE Author (
    id          INTEGER GENERATED BY DEFAULT AS IDENTITY,
    firstname   VARCHAR(64),
    lastname    VARCHAR(64)
  );
'''
//以下省略关闭sql对象过程

execute方法接收GString参数和SQL参数列表。还有一些其他类似的方法如：executeInsert,executeUpdate等。后面的例子中将会给出其使用方式。

使用grab自动下载驱动并连接数据库

之前的例子假定目标数据库的驱动已经放在classpath中。对于一个完整意义上的脚本来说，你可以在脚本最顶部使用@Grab语句自动下载驱动并装载到classloader中。

使用@Grab自动下载驱动并连接数据库：

//下载驱动
@Grab('org.hsqldb:hsqldb:2.3.2')
//加载到classloader
@GrabConfig(systemClassLoader=true)
// 以下可以正常使用Sql对象，create, use, and then close sql instance ...

@GrabConfig语句用于类加载器加载已下载的驱动，确保了驱动类是被系统类加载器（System ClassLoader）所加载。从而保证跟java.sql.DriverManager类在相同的类加载器中。这样DriverManager在加载驱动时才能找到刚刚下载的驱动类。

使用数据源(datasource)连接数据库

实际上，我们经常使用数据源连接到数据库。可能已经有了连接池和相应的数据源。这里，我们使用已经提供的数据源尝试连接HSQLDB数据库。

使用数据源连接HSQLDB

import groovy.sql.Sql
import org.hsqldb.jdbc.JDBCDataSource

def dataSource = new JDBCDataSource(database: 'jdbc:hsqldb:mem:yourDB', user: 'sa', password: '')
def sql = new Sql(dataSource)

// use then close 'sql' instance ...

如果已经有了自己的连接池，连接会有所不同。比如已经有了Apache DBCP连接池。

使用Apache DBCP连接池连接到HSQLDB。

@Grab('commons-dbcp:commons-dbcp:1.4')
import groovy.sql.Sql
import org.apache.commons.dbcp.BasicDataSource

def ds = new BasicDataSource(driverClassName: "org.hsqldb.jdbcDriver",
    url: 'jdbc:hsqldb:mem:yourDB', username: 'sa', password: '')
def sql = new Sql(ds)
// use then close 'sql' instance ...

groovy连接数据库

Groovy的groovy-sql模块提供了针对JAVA JDBC的较高抽象层次的封装。而JDBC提供了较底层的、容易理解的、统一访问各种数据库API的封装。我们的例子中使用HSQLDB，但是你可以应用到ORACLE、MySQL等其他数据库。groovy-sql最常用的类就是groovy.sql.Sql，该类为JDBC的访问提供了更高抽象层次的封装。我们首先来介绍它：

要想使用groovy Sql类连接上数据库，需要提供以下信息：

• 数据库连接地址(URL)
• 连接用户名
• 连接用户密码
• 连接驱动名称（某些情况下可以自动识别）

比如HSQLDB数据库，这些值是这样的：

根据JDBC驱动文档和实际情况决定自己各个字段的值。

Sql类使用newInstance的工厂方法实例化，该方法支持以下参数：

连接HSQLDB：

import groovy.sql.Sql

def url = 'jdbc:hsqldb:mem:yourDB'
def user = 'sa'
def password = ''
def driver = 'org.hsqldb.jdbcDriver'
def sql = Sql.newInstance(url, user, password, driver)

// use 'sql' instance ...

sql.close()

如果你不想手动关闭连接。你可以使用withInstance方法，该方法帮你自动关闭连接。

使用withInstance连接HSQLDB

Sql.withInstance(url, user, password, driver) { sql ->
  // use 'sql' instance ...
}

数据库操作之读取行数据

可以使用Sql类的query,eachRow,firstRow和rows方法从数据库读取行数据。

使用query方法可以遍历JDBC API中定义的ResultSet，代码示例如下：

def expected = ['Dierk Koenig', 'Jon Skeet', 'Guillaume Laforge']

def rowNum = 0
sql.query('SELECT firstname, lastname FROM Author') { resultSet ->
  while (resultSet.next()) {
    def first = resultSet.getString(1)
    def last = resultSet.getString('lastname')
    assert expected[rowNum++] == "$first $last"
  }
}

使用eachRow方法可以使用groovy提供的具有更高抽象层次的可以像map一样操作行数据。该方法操作起来更加简单和友好，同时避免了像ResultSet那样去遍历。

rowNum = 0
sql.eachRow('SELECT firstname, lastname FROM Author') { row ->
  def first = row[0]
  def last = row.lastname
  assert expected[rowNum++] == "$first $last"
}

注意，上述代码中你可以像使用groovy-list和groovy-map那样访问行数据。

使用firstRow方法跟使用eachRow是类似的但是该方法只返回其中一条数据，如何结果不为空的话。

def first = sql.firstRow('SELECT lastname, firstname FROM Author')
assert first.values().sort().join(',') == 'Dierk,Koenig'

使用rows()方法返回行数据列表：

List authors = sql.rows('SELECT firstname, lastname FROM Author')
assert authors.size() == 3
assert authors.collect { "$it.FIRSTNAME ${it[-1]}" } == expected

注意：map格式的行数据的键值是不区分大小写的。因此，使用'FIRSTNAME'或'firstname'是都可以取到数据。同时，也是可以支持通过下标的方式读取数据。

你也可以使用上述方法返回统计值，虽然firstRow方法就能满足这种需求。

assert sql.firstRow('SELECT COUNT(*) AS num FROM Author').num == 3

数据库操作之更新数据

更新数据也可以使用execute()方法来完成，只需传入更新语句和参数即可。

插入和更新数据：

sql.execute "INSERT INTO Author (lastname) VALUES ('Thorvaldsson')"
sql.execute "UPDATE Author SET firstname='Erik' where lastname='Thorvaldsson'"

推荐使用executeUpdate方法来执行更新，该方法返回更新数据的条数。很多时候通过返回的更新条数可以知道更新有没有成功。

使用executeUpdate：

def updateSql = "UPDATE Author SET lastname='Pragt' where lastname='Thorvaldsson'"
def updateCount = sql.executeUpdate updateSql
assert updateCount == 1

def row = sql.firstRow "SELECT * FROM Author where firstname = 'Erik'"
assert "${row.firstname} ${row.lastname}" == 'Erik Pragt'

数据库操作之删除行数据

execute方法可以用来删除行数据，如下面代码所示：

assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 3
sql.execute "DELETE FROM Author WHERE lastname = 'Skeet'"
assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 2