JDBC with Groovy – 使用Groovy的JDBC

最后修改: 2018年 3月 2日

中文/混合/英文(键盘快捷键：t)

1. Introduction

1.介绍

In this article, we’re going to look at how to query relational databases with JDBC, using idiomatic Groovy.

在这篇文章中，我们将看看如何使用成语Groovy，用JDBC查询关系型数据库。

JDBC, while relatively low-level, is the foundation of most ORMs and other high-level data access libraries on the JVM. And we can use JDBC directly in Groovy, of course; however, it has a rather cumbersome API.

JDBC虽然相对低级，但它是大多数ORM和JVM上其他高级数据访问库的基础。当然，我们也可以在Groovy中直接使用JDBC；不过，它的API相当麻烦。

Fortunately for us, the Groovy standard library builds upon JDBC to present an interface that is clean, simple, yet powerful. So, we’ll be exploring the Groovy SQL module.

幸运的是，Groovy标准库建立在JDBC的基础上，提供了一个干净、简单而又强大的接口。因此，我们将探索Groovy的SQL模块。

We’re going to look at JDBC in plain Groovy, not considering any framework such as Spring, for which we have other guides.

我们将在普通的Groovy中考察JDBC，不考虑任何框架，例如Spring，对于Spring，我们有其他指南。

2. JDBC and Groovy Setup

2.JDBC和Groovy设置

We have to include the groovy-sql module among our dependencies:

我们必须将groovy-sql模块纳入我们的依赖项。

<dependency>
    <groupId>org.codehaus.groovy</groupId>
    <artifactId>groovy</artifactId>
    <version>2.4.13</version>
</dependency>
<dependency>
    <groupId>org.codehaus.groovy</groupId>
    <artifactId>groovy-sql</artifactId>
    <version>2.4.13</version>
</dependency>

It’s not necessary to list it explicitly if we’re using groovy-all:

如果我们使用groovy-all，就没有必要明确列出它。

<dependency>
    <groupId>org.codehaus.groovy</groupId>
    <artifactId>groovy-all</artifactId>
    <version>2.4.13</version>
</dependency>

We can find the latest version of groovy, groovy-sql and groovy-all on Maven Central.

我们可以在Maven中心找到最新版本的groovy、groovy-sql和groovy-all/em>。

3. Connecting to the Database

3.连接到数据库

The first thing we have to do in order to work with the database is connecting to it.

为了与数据库一起工作，我们必须做的第一件事是连接到它。

Let’s introduce the groovy.sql.Sql class, which we’ll use for all operations on the database with the Groovy SQL module.

让我们介绍一下groovy.sql.Sql类，我们将用Groovy SQL模块对数据库进行所有操作。

An instance of Sql represents a database on which we want to operate.

一个Sql的实例代表了我们要操作的数据库。

However, an instance of Sql isn’t a single database connection. We’ll talk about connections later, let’s not worry about them now; let’s just assume everything magically works.

然而，一个Sql的实例并不是一个单一的数据库连接。我们稍后会讨论连接问题，现在不要担心，让我们假设一切都神奇地运作。

3.1. Specifying Connection Parameters

3.1.指定连接参数

Throughout this article, we’re going to use an HSQL Database, which is a lightweight relational DB that is mostly used in tests.

在这篇文章中，我们将使用HSQL数据库，它是一个轻量级的关系型数据库，主要用于测试。

A database connection needs a URL, a driver, and access credentials:

一个数据库连接需要一个URL、一个驱动程序和访问凭证。

Map dbConnParams = [
  url: 'jdbc:hsqldb:mem:testDB',
  user: 'sa',
  password: '',
  driver: 'org.hsqldb.jdbc.JDBCDriver']

Here, we’ve chosen to specify those using a Map, although it’s not the only possible choice.

在这里，我们选择使用Map来指定这些，尽管这不是唯一可能的选择。

We can then obtain a connection from the Sql class:

然后我们可以从Sql类中获得一个连接。

def sql = Sql.newInstance(dbConnParams)

We’ll see how to use it in the following sections.

我们将在以下章节中看到如何使用它。

When we’re finished, we should always release any associated resources:

当我们完成后，我们应该总是释放任何相关的资源。

sql.close()

3.2. Using a DataSource

3.2.使用一个数据源

It is common, especially in programs running inside an application server, to use a datasource to connect to the database.

特别是在应用服务器内部运行的程序中，使用数据源连接到数据库是很常见的。

Also, when we want to pool connections or to use JNDI, a datasource is the most natural option.

另外，当我们想汇集连接或使用JNDI时，数据源是最自然的选择。

Groovy’s Sql class accepts datasources just fine:

Groovy的Sql类可以很好地接受数据源。

def sql = Sql.newInstance(datasource)

3.3. Automatic Resource Management

3.3.自动资源管理

Remembering to call close() when we’re done with an Sql instance is tedious; machines remember stuff much better than we do, after all.

当我们用完一个Sql实例时，记住调用close()是很乏味的；毕竟，机器比我们记得更牢。

With Sql we can wrap our code in a closure and have Groovy call close() automatically when control leaves it, even in case of exceptions:

通过 Sql ，我们可以将我们的代码包裹在一个闭包中，并让Groovy在控制权离开时自动调用close() ，甚至在出现异常的情况下。

Sql.withInstance(dbConnParams) {
    Sql sql -> haveFunWith(sql)
}

4. Issuing Statements Against the Database

4.发布针对数据库的声明

Now, we can go on to the interesting stuff.

现在，我们可以继续讨论有趣的事情了。

The most simple and unspecialized way to issue a statement against the database is the execute method:

对数据库发布语句的最简单和非专业的方法是executemethod。

sql.execute "create table PROJECT (id integer not null, name varchar(50), url varchar(100))"

In theory it works both for DDL/DML statements and for queries; however, the simple form above does not offer a way to get back query results. We’ll leave queries for later.

理论上，它既适用于DDL/DML语句，也适用于查询；但是，上面的简单表格并没有提供一种获得查询结果的方法。我们会把查询留到以后。

The execute method has several overloaded versions, but, again, we’ll look at the more advanced use cases of this and other methods in later sections.

执行方法有几个重载版本，但是，我们将在后面的章节中看一下这个方法和其他方法的更高级用例。

4.1. Inserting Data

4.1.插入数据

For inserting data in small amounts and in simple scenarios, the execute method discussed earlier is perfectly fine.

对于插入少量的数据和简单的场景，前面讨论的执行方法是完全可以的。

However, for cases when we have generated columns (e.g., with sequences or auto-increment) and we want to know the generated values, a dedicated method exists: executeInsert.

然而，当我们有生成的列（例如，有序列或自动递增），并且我们想知道生成的值时，存在一个专门的方法。executeInsert.。

As for execute, we’ll now look at the most simple method overload available, leaving more complex variants for a later section.

至于execute，我们现在来看看最简单的方法重载，把更复杂的变体留给后面的章节。

So, suppose we have a table with an auto-increment primary key (identity in HSQLDB parlance):

所以，假设我们有一个具有自动递增主键的表（用HSQLDB的说法是身份）。

sql.execute "create table PROJECT (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))"

Let’s insert a row in the table and save the result in a variable:

让我们在表中插入一行，并将结果保存在一个变量中。

def ids = sql.executeInsert """
  INSERT INTO PROJECT (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')
"""

executeInsert behaves exactly like execute, but what does it return?

executeInsert的行为与execute完全一样，但它的返回结果是什么？

It turns out that the return value is a matrix: its rows are the inserted rows (remember that a single statement can cause multiple rows to be inserted) and its columns are the generated values.

事实证明，返回值是一个矩阵：它的行是插入的行（记住，一条语句可以导致插入多行），它的列是生成的值。

It sounds complicated, but in our case, which is by far the most common one, there is a single row and a single generated value:

这听起来很复杂，但在我们的案例中，也是迄今为止最常见的案例，有一个单一的行和一个单一的生成值。

assertEquals(0, ids[0][0])

A subsequent insertion would return a generated value of 1:

随后的插入将返回一个生成值1。

ids = sql.executeInsert """
  INSERT INTO PROJECT (NAME, URL)
  VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')
"""

assertEquals(1, ids[0][0])

4.2. Updating and Deleting Data

4.2.更新和删除数据

Similarly, a dedicated method for data modification and deletion exists: executeUpdate.

同样地，也有一个专门的方法用于数据的修改和删除。executeUpdate。

Again, this differs from execute only in its return value, and we’ll only look at its simplest form.

同样，这与执行的不同之处只在于它的返回值，我们将只看它最简单的形式。

The return value, in this case, is an integer, the number of affected rows:

在这种情况下，返回值是一个整数，即受影响行的数量。

def count = sql.executeUpdate("UPDATE PROJECT SET URL = 'https://' + URL")

assertEquals(2, count)

5. Querying the Database

5.查询数据库

Things start getting Groovy when we query the database.

当我们查询数据库的时候，事情就开始变得Groovy了。

Dealing with the JDBC ResultSet class is not exactly fun. Luckily for us, Groovy offers a nice abstraction over all of that.

处理JDBC的ResultSet类并不完全有趣。幸运的是，Groovy为所有这些提供了一个很好的抽象。

5.1. Iterating Over Query Results

5.1.迭代查询结果

While loops are so old style… we’re all into closures nowadays.

虽然循环是如此古老的风格……但我们现在都喜欢封闭式。

And Groovy is here to suit our tastes:

而Groovy的出现正好符合我们的口味。

sql.eachRow("SELECT * FROM PROJECT") { GroovyResultSet rs ->
    haveFunWith(rs)
}

The eachRow method issues our query against the database and calls a closure over each row.

eachRow方法发出我们对数据库的查询，并在每条记录上调用一个闭包。

As we can see, a row is represented by an instance of GroovyResultSet, which is an extension of plain old ResultSet with a few added goodies. Read on to find more about it.

正如我们所看到的，一行由GroovyResultSet的实例表示，它是普通的ResultSet的扩展，增加了一些好处。继续阅读以了解更多信息。

5.2. Accessing Result Sets

5.2.访问结果集

In addition to all of the ResultSet methods, GroovyResultSet offers a few convenient utilities.

除了所有的ResultSet方法之外，GroovyResultSet还提供了一些方便的工具。

Mainly, it exposes named properties matching column names:

主要是，它暴露了与列名相匹配的命名属性。

sql.eachRow("SELECT * FROM PROJECT") { rs ->
    assertNotNull(rs.name)
    assertNotNull(rs.URL)
}

Note how property names are case-insensitive.

请注意属性名称是不区分大小写的。

GroovyResultSet also offers access to columns using a zero-based index:

GroovyResultSet也提供了对使用零基索引的列的访问。

sql.eachRow("SELECT * FROM PROJECT") { rs ->
    assertNotNull(rs[0])
    assertNotNull(rs[1])
    assertNotNull(rs[2])
}

5.3. Pagination

5.3.分页

We can easily page the results, i.e., load only a subset starting from some offset up to some maximum number of rows. This is a common concern in web applications, for example.

我们可以很容易地对结果进行分页，即只加载一个子集，从某个偏移量开始，直到某个最大行数。这是网络应用中常见的问题，比如说。

eachRow and related methods have overloads accepting an offset and a maximum number of returned rows:

eachRow和相关方法有重载，接受一个偏移量和返回行的最大数量。

def offset = 1
def maxResults = 1
def rows = sql.rows('SELECT * FROM PROJECT ORDER BY NAME', offset, maxResults)

assertEquals(1, rows.size())
assertEquals('REST with Spring', rows[0].name)

Here, the rows method returns a list of rows rather than iterating over them like eachRow.

在这里，rows方法返回一个行的列表，而不是像eachRow那样对它们进行迭代。

6. Parameterized Queries and Statements

6.参数化查询和语句

More often than not, queries and statements are not fully fixed at compile time; they usually have a static part and a dynamic part, in the form of parameters.

更多的时候，查询和语句在编译时并不完全固定；它们通常有一个静态部分和一个动态部分，以参数的形式存在。

If you’re thinking about string concatenation, stop now and go read about SQL injection!

如果你正在考虑字符串连接的问题，现在就停下来，去读一读SQL注入的知识吧!

We mentioned earlier that the methods that we’ve seen in previous sections have many overloads for various scenarios.

我们在前面提到，我们在前几节看到的方法有很多重载，用于各种情况。

Let’s introduce those overloads that deal with parameters in SQL queries and statements.

让我们来介绍那些处理SQL查询和语句中的参数的重载。

6.1. Strings With Placeholders

6.1.带有占位符的字符串

In style similar to plain JDBC, we can use positional parameters:

在类似于普通JDBC的风格中，我们可以使用位置参数。

sql.execute(
    'INSERT INTO PROJECT (NAME, URL) VALUES (?, ?)',
    'tutorials', 'github.com/eugenp/tutorials')

or we can use named parameters with a map:

或者我们可以使用带有地图的命名参数。

sql.execute(
    'INSERT INTO PROJECT (NAME, URL) VALUES (:name, :url)',
    [name: 'REST with Spring', url: 'github.com/eugenp/REST-With-Spring'])

This works for execute, executeUpdate, rows and eachRow. executeInsert supports parameters, too, but its signature is a little bit different and trickier.

这适用于execute、executeUpdate、rows和eachRow。executeInsert也支持参数，但它的签名有点不同，而且比较麻烦。

6.2. Groovy Strings

6.2.格罗维弦乐队

We can also opt for a Groovier style using GStrings with placeholders.

我们也可以选择使用带有占位符的GStrings的Groovier风格。

All the methods we’ve seen don’t substitute placeholders in GStrings the usual way; rather, they insert them as JDBC parameters, ensuring the SQL syntax is correctly preserved, with no need to quote or escape anything and thus no risk of injection.

我们看到的所有方法都不是以通常的方式替换GStrings中的占位符；而是将它们作为JDBC参数插入，确保SQL语法被正确保留，不需要引用或转义任何东西，因此没有注入的风险。

This is perfectly fine, safe and Groovy:

这是很好的，安全的和Groovy的。

def name = 'REST with Spring'
def url = 'github.com/eugenp/REST-With-Spring'
sql.execute "INSERT INTO PROJECT (NAME, URL) VALUES (${name}, ${url})"

7. Transactions and Connections

7.事务和联系

So far we’ve skipped over a very important concern: transactions.

到目前为止，我们已经跳过了一个非常重要的问题：交易。

In fact, we haven’t talked at all about how Groovy’s Sql manages connections, either.

事实上，我们也根本没有谈及Groovy的Sql如何管理连接。

7.1. Short-Lived Connections

7.1.短暂的联系

In the examples presented so far, each and every query or statement was sent to the database using a new, dedicated connection. Sql closes the connection as soon as the operation terminates.

在迄今为止介绍的例子中，每一个查询或语句都是使用一个新的、专用的连接发送到数据库的。Sql一旦操作结束，就关闭连接。

Of course, if we’re using a connection pool, the impact on performance might be small.

当然，如果我们使用一个连接池，对性能的影响可能很小。

Still, if we want to issue multiple DML statements and queries as a single, atomic operation, we need a transaction.

尽管如此，如果我们想把多个DML语句和查询作为一个单一的原子操作发布，我们需要一个事务。

Also, for a transaction to be possible in the first place, we need a connection that spans multiple statements and queries.

另外，为了使交易首先成为可能，我们需要一个跨越多个语句和查询的连接。

7.2. Transactions With a Cached Connection

7.2.使用缓存连接的事务

Groovy SQL does not allow us to create or access transactions explicitly.

Groovy SQL不允许我们明确地创建或访问事务。

Instead, we use the withTransaction method with a closure:

相反，我们使用带有闭包的withTransaction方法。

sql.withTransaction {
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('tutorials', 'github.com/eugenp/tutorials')
    """
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')
    """
}

Inside the closure, a single database connection is used for all queries and statements.

在闭合内部，所有的查询和语句都使用一个数据库连接。

Furthermore, the transaction is automatically committed when the closure terminates, unless it exits early due to an exception.

此外，当闭包终止时，事务会自动提交，除非它因异常而提前退出。

However, we can also manually commit or rollback the current transaction with methods in the Sql class:

然而，我们也可以通过Sql类中的方法手动提交或回滚当前事务。

sql.withTransaction {
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('tutorials', 'github.com/eugenp/tutorials')
    """
    sql.commit()
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')
    """
    sql.rollback()
}

7.3. Cached Connections Without a Transaction

7.3.没有事务的缓存连接

Finally, to reuse a database connection without the transaction semantics described above, we use cacheConnection:

最后，为了重用一个数据库连接而不使用上述的事务语义，我们使用cacheConnection。

sql.cacheConnection {
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('tutorials', 'github.com/eugenp/tutorials')
    """
    throw new Exception('This does not roll back')
}

8. Conclusions and Further Reading

8.结论和进一步阅读

In this article, we’ve looked at the Groovy SQL module and how it enhances and simplifies JDBC with closures and Groovy strings.

在这篇文章中，我们已经了解了Groovy SQL模块，以及它是如何通过闭包和Groovy字符串增强和简化JDBC的。

We can then safely conclude that plain old JDBC looks a bit more modern with a sprinkle of Groovy!

因此，我们可以有把握地得出结论：普通的JDBC在撒上Groovy后看起来更现代了！这就是Groovy。

We haven’t talked about every single feature of Groovy SQL; for example, we’ve left out batch processing, stored procedures, metadata, and other things.

我们还没有谈到Groovy SQL的每一项功能；例如，我们没有提到批处理、存储过程、元数据和其他东西。

For further information, see the Groovy documentation.

有关进一步的信息，请参阅Groovy文档。

The implementation of all these examples and code snippets can be found in the GitHub project – this is a Maven project, so it should be easy to import and run as is.

所有这些例子和代码片段的实现都可以在GitHub项目中找到–这是一个Maven项目，所以应该很容易导入并按原样运行。