Using JDBI with Spring Boot – 在Spring Boot中使用JDBI

最后修改: 2019年 9月 19日

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

1. Introduction

1.绪论

In a previous tutorial, we covered the basics of JDBI, an open-source library for relational database access that removes much of the boilerplate code related to direct JDBC usage.

在上一篇教程中，我们介绍了JDBI的基础知识，这是一个用于关系型数据库访问的开源库，它删除了许多与直接使用JDBC有关的模板代码。

This time, we’ll see how we can use JDBI  in a Spring Boot application. We’ll also cover some aspects of this library that make it a good alternative to Spring Data JPA in some scenarios.

这一次，我们将看到如何在Spring Boot应用程序中使用JDBI。我们还将介绍这个库的一些方面，使其在某些情况下成为Spring Data JPA的良好替代品。

2. Project Setup

2.项目设置

First of all, let’s add the appropriate JDBI dependencies to our project. This time, we’ll use JDBI’s Spring integration plugin, which brings all required core dependencies. We’ll also bring in the SqlObject plugin, which adds some extra features to base JDBI that we’ll use in our examples:

首先，让我们在我们的项目中添加适当的JDBI依赖。这一次，我们将使用JDBI的Spring集成插件，它带来了所有需要的核心依赖。我们还将引入SqlObject插件，它为基础JDBI增加了一些额外的功能，我们将在我们的例子中使用。

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>2.1.8.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-spring4</artifactId>
    <version>3.9.1</version>
</dependency>
<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-sqlobject</artifactId>
    <version>3.9.1</version> 
</dependency>

The latest version of those artifacts can be found in Maven Central:

这些工件的最新版本可以在Maven中心找到。

We also need a suitable JDBC driver to access our database. In this article we’ll use H2, so we must add its driver to our dependencies list as well:

我们还需要一个合适的JDBC驱动程序来访问我们的数据库。在本文中，我们将使用H2，所以我们也必须将其驱动程序添加到我们的依赖项列表中。

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.199</version>
    <scope>runtime</scope>
</dependency>

3. JDBI Instantiation and Configuration

3.JDBI实例化和配置

We’ve already seen in our previous article that we need a Jdbi instance as our entry point to access JDBI’s API. As we’re in the Spring world, it makes sense to make an instance of this class available as a bean.

我们已经在之前的文章中看到，我们需要一个Jdbi实例作为我们访问JDBI的API的入口点。由于我们是在Spring的世界里，所以把这个类的一个实例作为一个Bean来使用是有意义的。

We’ll leverage Spring Boot’s auto-configuration capabilities to initialize a DataSource and pass it to a @Bean-annotated method which will create our global Jdbi instance.

我们将利用Spring Boot的自动配置功能来初始化一个DataSource，并将其传递给一个 @Bean注释的方法，该方法将创建我们的全局Jdbi实例。

We’ll also pass any discovered plugins and RowMapper instances to this method so that they’re registered upfront:

我们还将把任何发现的插件和RowMapper实例传递给这个方法，以便它们被预先注册。

@Configuration
public class JdbiConfiguration {
    @Bean
    public Jdbi jdbi(DataSource ds, List<JdbiPlugin> jdbiPlugins, List<RowMapper<?>> rowMappers) {        
        TransactionAwareDataSourceProxy proxy = new TransactionAwareDataSourceProxy(ds);        
        Jdbi jdbi = Jdbi.create(proxy);
        jdbiPlugins.forEach(plugin -> jdbi.installPlugin(plugin));
        rowMappers.forEach(mapper -> jdbi.registerRowMapper(mapper));       
        return jdbi;
    }
}

Here, we’re using an available DataSource and wrapping it in a TransactionAwareDataSourceProxy. We need this wrapper in order to integrate Spring-managed transactions with JDBI, as we’ll see later.

在这里，我们使用一个可用的DataSource并将其包装在TransactionAwareDataSourceProxy中。我们需要这个包装器，以便将Spring管理的事务与JDBI整合起来，我们将在后面看到。

Registering plugins and RowMapper instances is straightforward. All we have to do is call installPlugin and installRowMapper for every available JdbiPlugin and RowMapper, respectively. After that, we have a fully configured Jdbi instance that we can use in our application.

注册插件和RowMapper实例是很简单的。我们要做的就是分别为每个可用的JdbiPlugin和RowMapper调用installPlugin和installRowMapper。之后，我们就有了一个完全配置好的Jdbi实例，可以在我们的应用程序中使用。

4. Sample Domain

4.样本领域

Our example uses a very simple domain model consisting of just two classes: CarMaker and CarModel. Since JDBI does not require any annotations on our domain classes, we can use simple POJOs:

我们的例子使用了一个非常简单的领域模型，仅由两个类组成。CarMaker和CarModel。由于JDBI不需要对我们的领域类进行任何注释，我们可以使用简单的POJO。

public class CarMaker {
    private Long id;
    private String name;
    private List<CarModel> models;
    // getters and setters ...
}

public class CarModel {
    private Long id;
    private String name;
    private Integer year;
    private String sku;
    private Long makerId;
    // getters and setters ...
}

5. Creating DAOs

5.创建DAO

Now, let’s create Data Access Objects (DAOs) for our domain classes. JDBI SqlObject plugin offers an easy way to implement those classes, which resembles Spring Data’s way of dealing with this subject.

现在，让我们为我们的领域类创建数据访问对象（DAO）。JDBI SqlObject插件提供了一种简单的方法来实现这些类，它类似于Spring Data处理这个问题的方式。

We just have to define an interface with a few annotations and, automagically, JDBI will handle all low-level stuff such as handling JDBC connections and creating/disposing of statements and ResultSets:

我们只需要定义一个带有一些注解的接口，并且自动地，JDBI将处理所有底层的东西，如处理JDBC连接和创建/处置语句和ResultSets。

@UseClasspathSqlLocator
public interface CarMakerDao {
    @SqlUpdate
    @GetGeneratedKeys
    Long insert(@BindBean CarMaker carMaker);
    
    @SqlBatch("insert")
    @GetGeneratedKeys
    List<Long> bulkInsert(@BindBean List<CarMaker> carMakers);
    
    @SqlQuery
    CarMaker findById(Long id);
}

@UseClasspathSqlLocator
public interface CarModelDao {    
    @SqlUpdate
    @GetGeneratedKeys
    Long insert(@BindBean CarModel carModel);

    @SqlBatch("insert")
    @GetGeneratedKeys
    List<Long> bulkInsert(@BindBean List<CarModel> models);

    @SqlQuery
    CarModel findByMakerIdAndSku(@Bind("makerId") Long makerId, @Bind("sku") String sku );
}

Those interfaces are heavily annotated, so let’s take a quick look at each of them.

这些接口都有大量的注释，让我们快速看一下每一个接口。

5.1. @UseClasspathSqlLocator

5.1. @UseClasspathSqlLocator

The @UseClasspathSqlLocator annotation tells JDBI that actual SQL statements associated with each method are located at external resource files. By default, JDBI will lookup a resource using the interface’s fully qualified name and method. For instance, given an interface’s FQN of a.b.c.Foo with a findById() method, JDBI will look for a resource named a/b/c/Foo/findById.sql.

@UseClasspathSqlLocator注解告诉JDBI，与每个方法相关的实际SQL语句位于外部资源文件中。默认情况下，JDBI将使用接口的完全限定名称和方法来查找资源。例如，给定一个接口的FQN为a.b.c.Foo，有一个findById()方法，JDBI将查找一个名为a/b/c/Foo/findById.sql.的资源。

This default behavior can be overridden for any given method by passing the resource name as the value for the @SqlXXX annotation.

这个默认行为可以通过传递资源名称作为@SqlXXX注解的值来覆盖任何给定方法。

5.2. @SqlUpdate/@SqlBatch/@SqlQuery

5.2.@SqlUpdate/@SqlBatch/@SqlQuery

We use the @SqlUpdate, @SqlBatch, and @SqlQuery annotations to mark data-access methods, which will be executed using the given parameters. Those annotations can take an optional string value, which will be the literal SQL statement to execute – including any named parameters – or when used with @UseClasspathSqlLocator, the resource name containing it.

我们使用@SqlUpdate、@SqlBatch和@SqlQuery注解来标记数据访问方法，这些方法将使用给定参数执行。这些注解可以接受一个可选的字符串值，它将是要执行的字面SQL语句–包括任何命名的参数–或者当与@UseClasspathSqlLocator一起使用时，包含它的资源名称。

@SqlBatch-annotated methods can have collection-like arguments and execute the same SQL statement for every available item in a single batch statement. In each of the above DAO classes, we have a bulkInsert method that illustrates its use. The main advantage of using batch statements is the added performance we can achieve when dealing with large data sets.

@SqlBatch注释的方法可以有类似于集合的参数，并在一个批处理语句中对每个可用的项目执行相同的SQL语句。在上述每个DAO类中，我们都有一个bulkInsert方法，说明了它的用途。使用批处理语句的主要优点是在处理大型数据集时，我们可以获得额外的性能。

5.3. @GetGeneratedKeys

5.3.@GetGeneratedKeys

As the name implies, the @GetGeneratedKeys annotation allows us to recover any generated keys as a result of successful execution. It’s mostly used in insert statements where our database will auto-generate new identifiers and we need to recover them in our code.

顾名思义，@GetGeneratedKeys注解允许我们在成功执行后恢复任何生成的键。它主要用于insert语句中，我们的数据库将自动生成新的标识符，我们需要在代码中恢复它们。

5.4. @BindBean/@Bind

5.4 @BindBean/@Bind

We use @BindBean and @Bind annotations to bind the named parameters in the SQL statement with method parameters. @BindBean uses standard bean conventions to extract properties from a POJO – including nested ones. @Bind uses the parameter name or the supplied value to map its value to a named parameter.

我们使用@BindBean和@Bind注解将SQL语句中的命名参数与方法参数绑定。@BindBean使用标准的Bean惯例从POJO中提取属性–包括嵌套的属性。@Bind使用参数名称或提供的值将其值映射到一个命名的参数。

6. Using DAOs

6.使用DAO

To use those DAOs in our application, we have to instantiate them using one of the factory methods available in JDBI.

为了在我们的应用程序中使用这些DAO，我们必须使用JDBI中的一个工厂方法将它们实例化。

In a Spring context, the simplest way is to create a bean for every DAO using the onDemand method:

在Spring环境中，最简单的方法是使用onDemand方法为每个DAO创建一个Bean。

@Bean
public CarMakerDao carMakerDao(Jdbi jdbi) {        
    return jdbi.onDemand(CarMakerDao.class);       
}

@Bean
public CarModelDao carModelDao(Jdbi jdbi) {
    return jdbi.onDemand(CarModelDao.class);
}

The onDemand-created instance is thread-safe and uses a database connection only during a method call. Since JDBI we’ll use the supplied TransactionAwareDataSourceProxy, this means we can use it seamlessly with Spring-managed transactions.

创建的onDemand实例是线程安全的，并且只在方法调用期间使用数据库连接。由于JDBI我们将使用提供的TransactionAwareDataSourceProxy，这意味着我们可以将其与Spring管理的事务无缝连接。

While simple, the approach we’ve used here is far from ideal when we have to deal with more than a few tables. One way to avoid writing this kind of boilerplate code is to create a custom BeanFactory. Describing how to implement such a component is beyond the scope of this tutorial, though.

虽然简单，但当我们要处理超过几个表时，我们在这里使用的方法远非理想。避免编写这种模板代码的方法之一是创建一个自定义的BeanFactory。描述如何实现这样一个组件已经超出了本教程的范围。

7. Transactional Services

7.事务性服务

Let’s use our DAO classes in a simple service class that creates a few CarModel instances given a CarMaker populated with models. First, we’ll check if the given CarMaker was previously saved, saving it to the database if needed. Then, we’ll insert every CarModel one by one.

让我们在一个简单的服务类中使用我们的DAO类，该类给定一个充满模型的CarMaker创建一些CarModel实例。首先，我们将检查给定的CarMaker是否以前被保存过，如果需要的话，将其保存到数据库中。然后，我们将逐一插入每个CarModel。

If there’s a unique key violation (or some other error) at any point, the whole operation must fail and a full rollback should be performed.

如果在任何时候出现唯一密钥违规（或其他错误），整个操作必须失败，并应进行全面回滚。

JDBI provides a @Transaction annotation, but we can’t use it here as it is unaware of other resources that might be participating in the same business transaction. Instead, we’ll use Spring’s @Transactional annotation in our service method:

JDBI提供了一个@Transaction注解，但我们不能在这里使用它，因为它不知道其他可能参与同一业务交易的资源。相反，我们将在我们的服务方法中使用Spring的@Transactional注解。

@Service
public class CarMakerService {
    
    private CarMakerDao carMakerDao;
    private CarModelDao carModelDao;

    public CarMakerService(CarMakerDao carMakerDao,CarModelDao carModelDao) {        
        this.carMakerDao = carMakerDao;
        this.carModelDao = carModelDao;
    }    
    
    @Transactional
    public int bulkInsert(CarMaker carMaker) {
        Long carMakerId;
        if (carMaker.getId() == null ) {
            carMakerId = carMakerDao.insert(carMaker);
            carMaker.setId(carMakerId);
        }
        carMaker.getModels().forEach(m -> {
            m.setMakerId(carMaker.getId());
            carModelDao.insert(m);
        });                
        return carMaker.getModels().size();
    }
}

The operation’s implementation itself is quite simple: we’re using the standard convention that a null value in the id field implies this entity has not yet been persisted to the database. If this is the case, we use the CarMakerDao instance injected in the constructor to insert a new record in the database and get the generated id.

该操作的实现本身非常简单：我们使用标准惯例，即id字段中的null值意味着该实体还没有被持久化到数据库中。如果是这种情况，我们使用构造函数中注入的CarMakerDao实例在数据库中插入一条新记录，并获得生成的id。

Once we have the CarMaker‘s id, we iterate over the models, setting the makerId field for each one before saving it to the database.

一旦我们有了CarMaker的id，我们就遍历模型，在保存到数据库之前为每个模型设置makerId字段。

All those database operations will happen using the same underlying connection and will be part of the same transaction. The trick here lies in the way we’ve tied JDBI to Spring using TransactionAwareDataSourceProxy and creating onDemand DAOs. When JDBI requests a new Connection, it will get an existing one associated with the current transaction, thus integrating its lifecycle to other resources that might be enrolled.

所有这些数据库操作将使用相同的底层连接发生，并且将成为同一事务的一部分。这里的诀窍在于我们使用TransactionAwareDataSourceProxy和创建onDemand DAO的方式将JDBI与Spring联系起来。当JDBI请求一个新的Connection时，它将得到一个与当前事务相关联的现有的Connection，从而将其生命周期与其他可能被注册的资源整合起来。

8. Conclusion

8.结语

In this article, we’ve shown how to quickly integrate JDBI into a Spring Boot application. This is a powerful combination in scenarios where we can’t use Spring Data JPA for some reason but still want to use all other features such as transaction management, integration and so on.

在这篇文章中，我们展示了如何将JDBI快速集成到Spring Boot应用程序中。在我们因某些原因不能使用Spring Data JPA，但仍想使用所有其他功能（如事务管理、集成等）的场景中，这是一个强大的组合。

As usual, all code is available over at GitHub.

像往常一样，所有的代码都可以在GitHub上找到。