A Guide to Jdbi – Jdbi指南

最后修改: 2018年 3月 13日

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

1. Introduction

1.介绍

In this article, we’re going to look at how to query a relational database with jdbi.

在这篇文章中，我们将看看如何用jdbi查询关系型数据库。

Jdbi is an open source Java library (Apache license) that uses lambda expressions and reflection to provide a friendlier, higher level interface than JDBC to access the database.

Jdbi是一个开源的Java库（Apache许可证），它使用lambda表达式和reflection来提供一个比JDBC更友好、更高级的接口来访问数据库。

Jdbi, however, isn’t an ORM; even though it has an optional SQL Object mapping module, it doesn’t have a session with attached objects, a database independence layer, and any other bells and whistles of a typical ORM.

Jdbi并不是一个ORM；即使它有一个可选的SQL对象映射模块，它也没有一个带有附加对象的会话、一个数据库独立层，以及典型ORM的任何其他铃铛和口哨。

2. Jdbi Setup

2.Jdbi设置

Jdbi is organized into a core and several optional modules.

Jdbi被组织成一个核心模块和几个可选模块。

To get started, we just have to include the core module in our dependencies:

为了开始工作，我们只需要在我们的依赖中包括核心模块。

<dependencies>
    <dependency>
        <groupId>org.jdbi</groupId>
        <artifactId>jdbi3-core</artifactId>
        <version>3.1.0</version>
    </dependency>
</dependencies>

Over the course of this article, we’ll show examples using the HSQL database:

在这篇文章中，我们将展示使用HSQL数据库的例子。

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>2.4.0</version>
    <scope>test</scope>
</dependency>

We can find the latest version of jdbi3-core, HSQLDB and the other Jdbi modules on Maven Central.

我们可以在Maven中心找到最新版本的jdbi3-core，HSQLDB和其他Jdbi 模块。

3. Connecting to the Database

3.连接到数据库

First, we need to connect to the database. To do that, we have to specify the connection parameters.

首先，我们需要连接到数据库。要做到这一点，我们必须指定连接参数。

The starting point is the Jdbi class:

出发点是Jdbi类。

Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");

Here, we’re specifying the connection URL, a username, and, of course, a password.

在这里，我们要指定连接的URL，一个用户名，当然还有一个密码。

3.1. Additional Parameters

3.1.附加参数

If we need to provide other parameters, we use an overloaded method accepting a Properties object:

如果我们需要提供其他参数，我们使用一个接受Properties对象的重载方法。

Properties properties = new Properties();
properties.setProperty("username", "sa");
properties.setProperty("password", "");
Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", properties);

In these examples, we’ve saved the Jdbi instance in a local variable. That’s because we’ll use it to send statements and queries to the database.

在这些例子中，我们将Jdbi实例保存在一个本地变量中。这是因为我们将用它来向数据库发送语句和查询。

In fact, merely calling create doesn’t establish any connection to the DB. It just saves the connection parameters for later.

事实上，仅仅调用create并不能建立与数据库的任何连接。它只是为以后保存连接参数。

3.2. Using a DataSource

3.2.使用一个数据源

If we connect to the database using a DataSource, as is usually the case, we can use the appropriate create overload:

如果我们使用DataSource连接到数据库，就像通常的情况一样，我们可以使用适当的createoverload。

Jdbi jdbi = Jdbi.create(datasource);

3.3. Working With Handles

3.3.使用处理程序

Actual connections to the database are represented by instances of the Handle class.

与数据库的实际连接由Handle类的实例表示。

The easiest way to work with handles, and have them automatically closed, is by using lambda expressions:

处理句柄的最简单方法是使用lambda表达式，并让它们自动关闭。

jdbi.useHandle(handle -> {
    doStuffWith(handle);
});

We call useHandle when we don’t have to return a value.

当我们不需要返回一个值时，我们会调用useHandle。

Otherwise, we use withHandle:

否则，我们使用withHandle。

jdbi.withHandle(handle -> {
    return computeValue(handle);
});

It’s also possible, though not recommended, to manually open a connection handle; in that case, we have to close it when we’re done:

也有可能，尽管不推荐，手动打开一个连接句柄；在这种情况下，我们必须在完成后关闭它。

Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
try (Handle handle = jdbi.open()) {
    doStuffWith(handle);
}

Luckily, as we can see, Handle implements Closeable, so it can be used with try-with-resources.

幸运的是，正如我们所看到的，Handle实现了Closeable，所以它可以被用于try-with-resources。

4. Simple Statements

4.简单的声明

Now that we know how to obtain a connection let’s see how to use it.

现在我们知道如何获得一个连接，让我们看看如何使用它。

In this section, we’ll create a simple table that we’ll use throughout the article.

在本节中，我们将创建一个简单的表格，我们将在整个文章中使用。

To send statements such as create table to the database, we use the execute method:

为了向数据库发送诸如创建表的语句，我们使用执行方法。

handle.execute(
  "create table project "
  + "(id integer identity, name varchar(50), url varchar(100))");

execute returns the number of rows that were affected by the statement:

执行返回受该语句影响的行数。

int updateCount = handle.execute(
  "insert into project values "
  + "(1, 'tutorials', 'github.com/eugenp/tutorials')");

assertEquals(1, updateCount);

Actually, execute is just a convenience method.

实际上，执行只是一种方便的方法。

We’ll look at more complex use cases in later sections, but before doing that, we need to learn how to extract results from the database.

我们将在后面的章节中研究更复杂的用例，但在这之前，我们需要学习如何从数据库中提取结果。

5. Querying the Database

5.查询数据库

The most straightforward expression that produces results from the DB is a SQL query.

从数据库产生结果的最直接的表达方式是SQL查询。

To issue a query with a Jdbi Handle, we have to, at least:

要用Jdbi Handle发出查询，我们至少要这样做。

  1. create the query
  2. choose how to represent each row
  3. iterate over the results

We’ll now look at each of the points above.

我们现在就来看看上面的每一个要点。

5.1. Creating a Query

5.1.创建一个查询

Unsurprisingly, Jdbi represents queries as instances of the Query class.

不出所料，Jdbi将查询表示为Query 类的实例。

We can obtain one from a handle:

我们可以从一个手柄上获得一个。

Query query = handle.createQuery("select * from project");

5.2. Mapping the Results

5.2.绘制结果图

Jdbi abstracts away from the JDBC ResultSet, which has a quite cumbersome API.

Jdbi对JDBC的ResultSet进行了抽象，它有一个相当繁琐的API。

Therefore, it offers several possibilities to access the columns resulting from a query or some other statement that returns a result. We’ll now see the simplest ones.

因此，它提供了几种可能性来访问由查询或其他一些返回结果的语句所产生的列。我们现在将看到最简单的几种。

We can represent each row as a map:

我们可以将每一行表示为一张地图。

query.mapToMap();

The keys of the map will be the selected column names.

地图的键将是选定的列名。

Or, when a query returns a single column, we can map it to the desired Java type:

或者，当一个查询返回一个单列时，我们可以把它映射到所需的Java类型。

handle.createQuery("select name from project").mapTo(String.class);

Jdbi has built-in mappers for many common classes. Those that are specific to some library or database system are provided in separate modules.

Jdbi 为许多常见的类提供了内置的映射器。那些特定于某些库或数据库系统的映射器则在单独的模块中提供。

Of course, we can also define and register our mappers. We’ll talk about it in a later section.

当然，我们也可以定义和注册我们的映射器。我们将在后面的章节中讨论这个问题。

Finally, we can map rows to a bean or some other custom class. Again, we’ll see the more advanced options in a dedicated section.

最后，我们可以将行映射到Bean或其他一些自定义类。同样，我们将在专门的章节中看到更高级的选项。

5.3. Iterating Over the Results

5.3.对结果进行迭代

Once we’ve decided how to map the results by calling the appropriate method, we receive a ResultIterable object.

一旦我们决定如何通过调用适当的方法来映射结果，我们会收到一个ResultIterable 对象。

We can then use it to iterate over the results, one row at a time.

然后我们可以用它来迭代结果，每次都是一行。

Here we’ll look at the most common options.

在这里，我们将看一下最常见的选项。

We can merely accumulate the results in a list:

我们只需将结果累积到一个列表中。

List<Map<String, Object>> results = query.mapToMap().list();

Or to another Collection type:

或者到另一个Collection 类型。

List<String> results = query.mapTo(String.class).collect(Collectors.toSet());

Or we can iterate over the results as a stream:

或者我们可以把结果作为一个流进行迭代。

query.mapTo(String.class).useStream((Stream<String> stream) -> {
    doStuffWith(stream)
});

Here, we explicitly typed the stream variable for clarity, but it’s not necessary to do so.

在这里，我们明确地输入了stream 变量，以使其清晰，但没有必要这样做。

5.4. Getting a Single Result

5.4.获得单一结果

As a special case, when we expect or are interested in just one row, we have a couple of dedicated methods available.

作为一种特殊情况，当我们期望或只对一行感兴趣时，我们有几个专门的方法可用。

If we want at most one result, we can use findFirst:

如果我们想要最多一个结果，我们可以使用findFirst。

Optional<Map<String, Object>> first = query.mapToMap().findFirst();

As we can see, it returns an Optional value, which is only present if the query returns at least one result.

我们可以看到，它返回一个Optional值，只有在查询至少返回一个结果时才会出现。

If the query returns more than one row, only the first is returned.

如果查询返回多条记录，只返回第一条。

If instead, we want one and only one result, we use findOnly:

如果我们想要一个且只有一个结果，我们使用findOnly。

Date onlyResult = query.mapTo(Date.class).findOnly();

Finally, if there are zero results or more than one, findOnly throws an IllegalStateException.

最后，如果有零个结果或超过一个，findOnly会抛出一个IllegalStateException。

6. Binding Parameters

6.绑定参数

Often, queries have a fixed portion and a parameterized portion. This has several advantages, including:

通常，查询有一个固定部分和一个参数化部分。这有几个优点，包括。

  • security: by avoiding string concatenation, we prevent SQL injection
  • ease: we don’t have to remember the exact syntax of complex data types such as timestamps
  • performance: the static portion of the query can be parsed once and cached

Jdbi supports both positional and named parameters.

Jdbi同时支持位置参数和命名参数。

We insert positional parameters as question marks in a query or statement:

我们在查询或语句中插入位置参数作为问号。

Query positionalParamsQuery =
  handle.createQuery("select * from project where name = ?");

Named parameters, instead, start with a colon:

命名的参数则以冒号开始。

Query namedParamsQuery =
  handle.createQuery("select * from project where url like :pattern");

In either case, to set the value of a parameter, we use one of the variants of the bind method:

在这两种情况下，为了设置一个参数的值，我们使用bind方法的一个变体。

positionalParamsQuery.bind(0, "tutorials");
namedParamsQuery.bind("pattern", "%github.com/eugenp/%");

Note that, unlike JDBC, indexes start at 0.

注意，与JDBC不同，索引从0开始。

6.1. Binding Multiple Named Parameters at Once

6.1.一次性绑定多个命名参数

We can also bind multiple named parameters together using an object.

我们还可以使用一个对象将多个命名的参数绑定在一起。

Let’s say we have this simple query:

比方说，我们有这个简单的查询。

Query query = handle.createQuery(
  "select id from project where name = :name and url = :url");
Map<String, String> params = new HashMap<>();
params.put("name", "REST with Spring");
params.put("url", "github.com/eugenp/REST-With-Spring");

Then, for example, we can use a map:

那么，比如说，我们可以使用一张地图。

query.bindMap(params);

Or we can use an object in various ways. Here, for example, we bind an object that follows the JavaBean convention:

或者我们可以以各种方式使用一个对象。例如，在这里，我们绑定了一个遵循JavaBean惯例的对象。

query.bindBean(paramsBean);

But we could also bind an object’s fields or methods; for all the supported options, see the Jdbi documentation.

但是我们也可以绑定一个对象的字段或方法；关于所有支持的选项，请参见Jdbi文档。

7. Issuing More Complex Statements

7.发布更复杂的报表

Now that we’ve seen queries, values, and parameters, we can go back to statements and apply the same knowledge.

现在我们已经看到了查询、值和参数，我们可以回到语句中并应用同样的知识。

Recall that the execute method we saw earlier is just a handy shortcut.

回想一下，我们之前看到的execute方法只是一个方便的快捷方式。

In fact, similarly to queries, DDL and DML statements are represented as instances of the class Update.

事实上，与查询类似，DDL和DML语句被表示为Update.类的实例。

We can obtain one by calling the method createUpdate on a handle:

我们可以通过在一个句柄上调用createUpdate方法来获得一个。

Update update = handle.createUpdate(
  "INSERT INTO PROJECT (NAME, URL) VALUES (:name, :url)");

Then, on an Update we have all the binding methods that we have in a Query, so section 6. applies for updates as well.url

然后，在Update中，我们拥有Query中的所有绑定方法，因此第6节也适用于更新。

Statements are executed when we call, surprise, execute:

语句在我们调用时被执行，令人惊讶的是，execute。

int rows = update.execute();

As we have already seen, it returns the number of affected rows.

正如我们已经看到的，它返回受影响的行的数量。

7.1. Extracting Auto-Increment Column Values

7.1.提取自动增量列的值

As a special case, when we have an insert statement with auto-generated columns (typically auto-increment or sequences), we may want to obtain the generated values.

作为一种特殊情况，当我们有一个带有自动生成列的插入语句时（通常是自动增加或序列），我们可能想获得生成的值。

Then, we don’t call execute, but executeAndReturnGeneratedKeys:

然后，我们不调用execute，而是executeAndReturnGeneratedKeys。

Update update = handle.createUpdate(
  "INSERT INTO PROJECT (NAME, URL) "
  + "VALUES ('tutorials', 'github.com/eugenp/tutorials')");
ResultBearing generatedKeys = update.executeAndReturnGeneratedKeys();

ResultBearing is the same interface implemented by the Query class that we’ve seen previously, so we already know how to use it:

ResultBearing是由我们之前看到的Query类实现的相同接口，所以我们已经知道如何使用它。

generatedKeys.mapToMap()
  .findOnly().get("id");

8. Transactions

8.事务

We need a transaction whenever we have to execute multiple statements as a single, atomic operation.

当我们需要将多个语句作为一个单一的原子操作来执行时，我们需要一个事务。

As with connection handles, we introduce a transaction by calling a method with a closure:

与连接柄一样，我们通过调用一个带有闭包的方法来引入一个事务。

handle.useTransaction((Handle h) -> {
    haveFunWith(h);
});

And, as with handles, the transaction is automatically closed when the closure returns.

而且，与手柄一样，当关闭返回时，交易会自动关闭。

However, we must commit or rollback the transaction before returning:

然而，我们必须在返回之前提交或回滚该事务。

handle.useTransaction((Handle h) -> {
    h.execute("...");
    h.commit();
});

If, however, an exception is thrown from the closure, Jdbi automatically rolls back the transaction.

然而，如果从关闭中抛出一个异常，Jdbi会自动回滚交易。

As with handles, we have a dedicated method, inTransaction, if we want to return something from the closure:

与句柄一样，如果我们想从闭包中返回一些东西，我们有一个专门的方法，inTransaction。

handle.inTransaction((Handle h) -> {
    h.execute("...");
    h.commit();
    return true;
});

8.1. Manual Transaction Management

8.1.手工事务管理

Although in the general case it’s not recommended, we can also begin and close a transaction manually:

虽然在一般情况下不建议这样做，但我们也可以手动开始和关闭一个交易。

handle.begin();
// ...
handle.commit();
handle.close();

9. Conclusions and Further Reading

9.结论和进一步阅读

In this tutorial, we’ve introduced the core of Jdbi: queries, statements, and transactions.

在本教程中，我们已经介绍了Jdbi的核心。查询、语句和事务。。

We’ve left out some advanced features, like custom row and column mapping and batch processing.

我们遗漏了一些高级功能，如自定义行和列的映射和批量处理。

We also haven’t discussed any of the optional modules, most notably the SQL Object extension.

我们也没有讨论任何可选模块，最值得注意的是SQL对象扩展。

Everything is presented in detail in the Jdbi documentation.

一切都在Jdbi文档中详细介绍。

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项目，所以应该很容易导入并按原样运行。