Introduction to Jooq with Spring – 使用Spring的Jooq简介

1. Overview

1.概述

This article will introduce Jooq Object Oriented Querying – Jooq – and a simple way to set it up in collaboration with the Spring Framework.

本文将介绍Jooq面向对象的查询–Jooq–以及与Spring框架合作设置的简单方法。

Most Java applications have some sort of SQL persistence and access that layer with the help of higher-level tools such as JPA. And while that’s useful, in some cases you really need a finer, more nuanced tool to get to your data or to actually take advantage of everything the underlying DB has to offer.

大多数Java应用程序都有某种SQL持久性，并在JPA等高级工具的帮助下访问该层。虽然这很有用，但在某些情况下，你真的需要一个更精细、更微妙的工具来获取你的数据，或真正利用底层数据库所提供的一切。

Jooq avoids some typical ORM patterns and generates code that allows us to build typesafe queries, and get complete control of the generated SQL via a clean and powerful fluent API.

Jooq避免了一些典型的ORM模式，生成的代码允许我们建立类型安全的查询，并通过一个干净和强大的流畅的API获得对生成的SQL的完全控制。

This article focuses on Spring MVC. Our article Spring Boot Support for jOOQ describes how to use jOOQ in Spring Boot.

本文重点介绍Spring MVC。我们的文章Spring Boot对jOOQ的支持介绍了如何在Spring Boot中使用jOOQ。

2. Maven Dependencies

2.Maven的依赖性

The following dependencies are necessary to run the code in this tutorial.

以下是运行本教程中的代码所需的依赖性。

2.1. jOOQ

2.1.jOOQ

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.14.15</version>
</dependency>

2.2. Spring

2.2.Spring

There are several Spring dependencies required for our example; however, to make things simple, we just need to explicitly include two of them in the POM file:

我们的例子需要几个Spring的依赖项；然而，为了简单起见，我们只需要在POM文件中明确包含其中两个。

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.2.2.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.2.RELEASE</version>
</dependency>

2.3. Database

2.3.数据库

To make things easy for our example, we will make use of the H2 embedded database:

为了使我们的例子变得简单，我们将利用H2嵌入式数据库。

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.191</version>
</dependency>

3. Code Generation

3.代码生成

3.1. Database Structure

3.1.数据库结构

Let’s introduce the database structure we will be working with throughout this article. Suppose that we need to create a database for a publisher to store information the books and authors they manage, where an author may write many books and a book may be co-written by many authors.

让我们介绍一下我们在本文中将要使用的数据库结构。假设我们需要为出版商创建一个数据库，以存储他们管理的书籍和作者的信息，其中一个作者可能写了很多书，一本书可能由很多作者共同撰写。

To make it simple, we will generate only three tables: the book for books, author for authors, and another table called author_book to represent the many-to-many relationship between authors and books. The author table has three columns: id, first_name, and last_name. The book table contains only a title column and the id primary key.

为了简单起见，我们将只生成三个表：book代表书，author代表作者，还有一个叫author_book的表代表作者和书之间的多对多的关系。author表有三列。id、first_name和last_name。book表只包含一个title列和id主键。

The following SQL queries, stored in the intro_schema.sql resource file, will be executed against the database we have already set up before to create the necessary tables and populate them with sample data:

以下SQL查询，存储在intro_schema.sql资源文件中，将针对我们之前已经设置好的数据库执行，以创建必要的表，并将样本数据填充其中。

DROP TABLE IF EXISTS author_book, author, book;

CREATE TABLE author (
  id             INT          NOT NULL PRIMARY KEY,
  first_name     VARCHAR(50),
  last_name      VARCHAR(50)  NOT NULL
);

CREATE TABLE book (
  id             INT          NOT NULL PRIMARY KEY,
  title          VARCHAR(100) NOT NULL
);

CREATE TABLE author_book (
  author_id      INT          NOT NULL,
  book_id        INT          NOT NULL,
  
  PRIMARY KEY (author_id, book_id),
  CONSTRAINT fk_ab_author     FOREIGN KEY (author_id)  REFERENCES author (id)  
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_ab_book       FOREIGN KEY (book_id)    REFERENCES book   (id)
);

INSERT INTO author VALUES 
  (1, 'Kathy', 'Sierra'), 
  (2, 'Bert', 'Bates'), 
  (3, 'Bryan', 'Basham');

INSERT INTO book VALUES 
  (1, 'Head First Java'), 
  (2, 'Head First Servlets and JSP'),
  (3, 'OCA/OCP Java SE 7 Programmer');

INSERT INTO author_book VALUES (1, 1), (1, 3), (2, 1);

3.2. Properties Maven Plugin

3.2.属性Maven插件

We will use three different Maven plugins to generate the Jooq code. The first of these is the Properties Maven plugin.

我们将使用三个不同的Maven插件来生成Jooq代码。其中第一个是属性Maven插件。

This plugin is used to read configuration data from a resource file. It is not required since the data may be directly added to the POM, but it is a good idea to manage the properties externally.

这个插件用于从资源文件中读取配置数据。它不是必需的，因为这些数据可以直接添加到POM中，但在外部管理这些属性是一个好主意。

In this section, we will define properties for database connections, including the JDBC driver class, database URL, username, and password, in a file named intro_config.properties. Externalizing these properties makes it easy to switch the database or just change the configuration data.

在本节中，我们将在一个名为intro_config.properties的文件中定义数据库连接的属性，包括JDBC驱动类、数据库URL、用户名和密码。将这些属性外部化使得切换数据库或只是改变配置数据变得容易。

The read-project-properties goal of this plugin should be bound to an early phase so that the configuration data can be prepared for use by other plugins. In this case, it is bound to the initialize phase:

这个插件的read-project-properties目标应该被绑定到一个早期阶段，以便配置数据可以被其他插件准备使用。在这种情况下，它被绑定到initialize阶段。

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>properties-maven-plugin</artifactId>
    <version>1.0.0</version>
    <executions>
        <execution>
            <phase>initialize</phase>
            <goals>
                <goal>read-project-properties</goal>
            </goals>
            <configuration>
                <files>
                    <file>src/main/resources/intro_config.properties</file>
                </files>
            </configuration>
        </execution>
    </executions>
</plugin>

3.3. SQL Maven Plugin

3.3.SQL Maven Plugin

The SQL Maven plugin is used to execute SQL statements to create and populate database tables. It will make use of the properties that have been extracted from the intro_config.properties file by the Properties Maven plugin and take the SQL statements from the intro_schema.sql resource.

SQL Maven插件用于执行SQL语句以创建和填充数据库表。它将利用属性Maven插件从intro_config.properties文件中提取的属性，并从intro_schema.sql资源中获取SQL语句。

The SQL Maven plugin is configured as below:

SQL Maven插件的配置如下。

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>sql-maven-plugin</artifactId>
    <version>1.5</version>
    <executions>
        <execution>
            <phase>initialize</phase>
            <goals>
                <goal>execute</goal>
            </goals>
            <configuration>
                <driver>${db.driver}</driver>
                <url>${db.url}</url>
                <username>${db.username}</username>
                <password>${db.password}</password>
                <srcFiles>
                    <srcFile>src/main/resources/intro_schema.sql</srcFile>
                </srcFiles>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.191</version>
        </dependency>
    </dependencies>
</plugin>

Note that this plugin must be placed later than the Properties Maven plugin in the POM file since their execution goals are both bound to the same phase, and Maven will execute them in the order they are listed.

请注意，在POM文件中，该插件必须放在属性Maven插件之后，因为它们的执行目标都绑定在同一个阶段，Maven会按照它们的排列顺序来执行它们。

3.4. jOOQ Codegen Plugin

3.4. jOOQ Codegen插件

The Jooq Codegen Plugin generates Java code from a database table structure. Its generate goal should be bound to the generate-sources phase to ensure the correct order of execution. The plugin metadata looks like the following:

Jooq Codegen插件从数据库表结构生成Java代码。它的generate目标应与generate-sources阶段绑定，以确保执行顺序正确。该插件的元数据看起来像下面这样。

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${org.jooq.version}</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
            <configuration>
                <jdbc>
                    <driver>${db.driver}</driver>
                    <url>${db.url}</url>
                    <user>${db.username}</user>
                    <password>${db.password}</password>
                </jdbc>
                <generator>
                    <target>
                        <packageName>com.baeldung.jooq.introduction.db</packageName>
                        <directory>src/main/java</directory>
                    </target>
                </generator>
            </configuration>
        </execution>
    </executions>
</plugin>

3.5. Generating Code

3.5.生成代码

To finish up the process of source code generation, we need to run the Maven generate-sources phase. In Eclipse, we can do this by right-clicking on the project and choosing Run As –> Maven generate-sources. After the command is completed, source files corresponding to the author, book, author_book tables (and several others for supporting classes) are generated.

为了完成源代码的生成过程，我们需要运行Maven的generate-sources阶段。在Eclipse中，我们可以通过右键点击项目，选择Run As -> Maven generate-sources来完成。命令完成后，author、book、author_book表（以及其他几个支持类）对应的源文件就生成了。

Let’s dig into table classes to see what Jooq produced. Each class has a static field of the same name as the class, except that all letters in the name are capitalized. The following are code snippets taken from the generated classes’ definitions:

让我们挖掘一下表类，看看Jooq产生了什么。每个类都有一个与该类同名的静态字段，只是名称中的所有字母都大写。下面是取自生成的类的定义的代码片段。

The Author class:

Author类。

public class Author extends TableImpl<AuthorRecord> {
    public static final Author AUTHOR = new Author();

    // other class members
}

The Book class:

书类。

public class Book extends TableImpl<BookRecord> {
    public static final Book BOOK = new Book();

    // other class members
}

The AuthorBook class:

AuthorBook类。

public class AuthorBook extends TableImpl<AuthorBookRecord> {
    public static final AuthorBook AUTHOR_BOOK = new AuthorBook();

    // other class members
}

The instances referenced by those static fields will serve as data access objects to represent the corresponding tables when working with other layers in a project.

这些静态字段所引用的实例将作为数据访问对象，在项目中与其他层一起工作时代表相应的表。

4. Spring Configuration

4.Spring配置

4.1. Translating jOOQ Exceptions to Spring

4.1.将jOOQ的异常转化为Spring

In order to make exceptions thrown from Jooq execution consistent with Spring support for database access, we need to translate them into subtypes of the DataAccessException class.

为了使Jooq执行中抛出的异常与Spring对数据库访问的支持相一致，我们需要将它们翻译成DataAccessException类的子类型。

Let’s define an implementation of the ExecuteListener interface to convert exceptions:

让我们定义一个ExecuteListener接口的实现来转换异常。

public class ExceptionTranslator extends DefaultExecuteListener {
    public void exception(ExecuteContext context) {
        SQLDialect dialect = context.configuration().dialect();
        SQLExceptionTranslator translator 
          = new SQLErrorCodeSQLExceptionTranslator(dialect.name());
        context.exception(translator
          .translate("Access database using Jooq", context.sql(), context.sqlException()));
    }
}

This class will be used by the Spring application context.

这个类将被Spring应用上下文所使用。

4.2. Configuring Spring

4.2.配置Spring

This section will go through steps to define a PersistenceContext that contains metadata and beans to be used in the Spring application context.

本节将介绍定义PersistenceContext的步骤，该Context包含元数据和将在Spring应用程序上下文中使用的bean。

Let’s get started by applying necessary annotations to the class:

让我们开始对该类应用必要的注释。

• @Configuration: Make the class to be recognized as a container for beans
• @ComponentScan: Configure scanning directives, including the value option to declare an array of package names to search for components. In this tutorial, the package to be searched is the one generated by the Jooq Codegen Maven plugin
• @EnableTransactionManagement: Enable transactions to be managed by Spring
• @PropertySource: Indicate the locations of the properties files to be loaded. The value in this article points to the file containing configuration data and dialect of the database, which happens to be the same file mentioned in subsection 4.1.

@Configuration
@ComponentScan({"com.baeldung.Jooq.introduction.db.public_.tables"})
@EnableTransactionManagement
@PropertySource("classpath:intro_config.properties")
public class PersistenceContext {
    // Other declarations
}

Next, use an Environment object to get the configuration data, which is then used to configure the DataSource bean:

接下来，使用Environment对象来获取配置数据，然后用它来配置DataSourcebean。

@Autowired
private Environment environment;

@Bean
public DataSource dataSource() {
    JdbcDataSource dataSource = new JdbcDataSource();

    dataSource.setUrl(environment.getRequiredProperty("db.url"));
    dataSource.setUser(environment.getRequiredProperty("db.username"));
    dataSource.setPassword(environment.getRequiredProperty("db.password"));

    return dataSource; 
}

Now we define several beans to work with database access operations:

现在我们定义几个Bean来处理数据库访问操作。

@Bean
public TransactionAwareDataSourceProxy transactionAwareDataSource() {
    return new TransactionAwareDataSourceProxy(dataSource());
}

@Bean
public DataSourceTransactionManager transactionManager() {
    return new DataSourceTransactionManager(dataSource());
}

@Bean
public DataSourceConnectionProvider connectionProvider() {
    return new DataSourceConnectionProvider(transactionAwareDataSource());
}

@Bean
public ExceptionTranslator exceptionTransformer() {
    return new ExceptionTranslator();
}
    
@Bean
public DefaultDSLContext dsl() {
    return new DefaultDSLContext(configuration());
}

Finally, we provide a Jooq Configuration implementation and declare it as a Spring bean to be used by the DSLContext class:

最后，我们提供了一个Jooq Configuration实现，并将其声明为一个Spring Bean，供DSLContext类使用。

@Bean
public DefaultConfiguration configuration() {
    DefaultConfiguration JooqConfiguration = new DefaultConfiguration();
    jooqConfiguration.set(connectionProvider());
    jooqConfiguration.set(new DefaultExecuteListenerProvider(exceptionTransformer()));

    String sqlDialectName = environment.getRequiredProperty("jooq.sql.dialect");
    SQLDialect dialect = SQLDialect.valueOf(sqlDialectName);
    jooqConfiguration.set(dialect);

    return jooqConfiguration;
}

5. Using jOOQ With Spring

5.使用jOOQ与Spring

This section demonstrates the use of Jooq in common database access queries. There are two tests, one for commit and one for rollback, for each type of “write” operation, including inserting, updating, and deleting data. The use of “read” operation is illustrated when selecting data to verify the “write” queries.

本节演示了Jooq在常见数据库访问查询中的使用。每种类型的 “写 “操作，包括插入、更新和删除数据，都有两个测试，一个用于提交，一个用于回滚。在选择数据以验证 “写 “的查询时，说明了 “读 “的操作的使用。

We will begin by declaring an auto-wired DSLContext object and instances of Jooq generated classes to be used by all testing methods:

我们将首先声明一个自动连接的DSLContext对象和Jooq生成的类的实例，供所有测试方法使用。

@Autowired
private DSLContext dsl;

Author author = Author.AUTHOR;
Book book = Book.BOOK;
AuthorBook authorBook = AuthorBook.AUTHOR_BOOK;

5.1. Inserting Data

5.1.插入数据

The first step is to insert data into tables:

第一步是将数据插入表内。

dsl.insertInto(author)
  .set(author.ID, 4)
  .set(author.FIRST_NAME, "Herbert")
  .set(author.LAST_NAME, "Schildt")
  .execute();
dsl.insertInto(book)
  .set(book.ID, 4)
  .set(book.TITLE, "A Beginner's Guide")
  .execute();
dsl.insertInto(authorBook)
  .set(authorBook.AUTHOR_ID, 4)
  .set(authorBook.BOOK_ID, 4)
  .execute();

A SELECT query to extract data:

一个SELECT查询来提取数据。

Result<Record3<Integer, String, Integer>> result = dsl
  .select(author.ID, author.LAST_NAME, DSL.count())
  .from(author)
  .join(authorBook)
  .on(author.ID.equal(authorBook.AUTHOR_ID))
  .join(book)
  .on(authorBook.BOOK_ID.equal(book.ID))
  .groupBy(author.LAST_NAME)
  .fetch();

The above query produces the following output:

上述查询产生以下输出。

+----+---------+-----+
|  ID|LAST_NAME|count|
+----+---------+-----+
|   1|Sierra   |    2|
|   2|Bates    |    1|
|   4|Schildt  |    1|
+----+---------+-----+

The result is confirmed by the Assert API:

该结果由Assert API确认。

assertEquals(3, result.size());
assertEquals("Sierra", result.getValue(0, author.LAST_NAME));
assertEquals(Integer.valueOf(2), result.getValue(0, DSL.count()));
assertEquals("Schildt", result.getValue(2, author.LAST_NAME));
assertEquals(Integer.valueOf(1), result.getValue(2, DSL.count()));

When a failure occurs due to an invalid query, an exception is thrown and the transaction rolls back. In the following example, the INSERT query violates a foreign key constraint, resulting in an exception:

当由于无效的查询而发生故障时，会抛出一个异常，事务会回滚。在下面的例子中，INSERT查询违反了一个外键约束，导致了一个异常。

@Test(expected = DataAccessException.class)
public void givenInvalidData_whenInserting_thenFail() {
    dsl.insertInto(authorBook)
      .set(authorBook.AUTHOR_ID, 4)
      .set(authorBook.BOOK_ID, 5)
      .execute();
}

5.2. Updating Data

5.2.更新数据

Now let’s update the existing data:

现在我们来更新现有的数据。

dsl.update(author)
  .set(author.LAST_NAME, "Baeldung")
  .where(author.ID.equal(3))
  .execute();
dsl.update(book)
  .set(book.TITLE, "Building your REST API with Spring")
  .where(book.ID.equal(3))
  .execute();
dsl.insertInto(authorBook)
  .set(authorBook.AUTHOR_ID, 3)
  .set(authorBook.BOOK_ID, 3)
  .execute();

Get the necessary data:

获得必要的数据。

Result<Record3<Integer, String, String>> result = dsl
  .select(author.ID, author.LAST_NAME, book.TITLE)
  .from(author)
  .join(authorBook)
  .on(author.ID.equal(authorBook.AUTHOR_ID))
  .join(book)
  .on(authorBook.BOOK_ID.equal(book.ID))
  .where(author.ID.equal(3))
  .fetch();

The output should be:

输出应该是。

+----+---------+----------------------------------+
|  ID|LAST_NAME|TITLE                             |
+----+---------+----------------------------------+
|   3|Baeldung |Building your REST API with Spring|
+----+---------+----------------------------------+

The following test will verify that Jooq worked as expected:

下面的测试将验证Jooq是否按预期工作。

assertEquals(1, result.size());
assertEquals(Integer.valueOf(3), result.getValue(0, author.ID));
assertEquals("Baeldung", result.getValue(0, author.LAST_NAME));
assertEquals("Building your REST API with Spring", result.getValue(0, book.TITLE));

In case of a failure, an exception is thrown and the transaction rolls back, which we confirm with a test:

在失败的情况下，会抛出一个异常，事务会回滚，我们用测试来确认。

@Test(expected = DataAccessException.class)
public void givenInvalidData_whenUpdating_thenFail() {
    dsl.update(authorBook)
      .set(authorBook.AUTHOR_ID, 4)
      .set(authorBook.BOOK_ID, 5)
      .execute();
}

5.3. Deleting Data

5.3.删除数据

The following method deletes some data:

下面的方法删除了一些数据。

dsl.delete(author)
  .where(author.ID.lt(3))
  .execute();

Here is the query to read the affected table:

下面是读取受影响表的查询。

Result<Record3<Integer, String, String>> result = dsl
  .select(author.ID, author.FIRST_NAME, author.LAST_NAME)
  .from(author)
  .fetch();

The query output:

查询的输出。

+----+----------+---------+
|  ID|FIRST_NAME|LAST_NAME|
+----+----------+---------+
|   3|Bryan     |Basham   |
+----+----------+---------+

The following test verifies the deletion:

下面的测试验证了这一删除。

assertEquals(1, result.size());
assertEquals("Bryan", result.getValue(0, author.FIRST_NAME));
assertEquals("Basham", result.getValue(0, author.LAST_NAME));

On the other hand, if a query is invalid, it will throw an exception and the transaction rolls back. The following test will prove that:

另一方面，如果一个查询是无效的，它将抛出一个异常，事务将回滚。下面的测试将证明这一点。

@Test(expected = DataAccessException.class)
public void givenInvalidData_whenDeleting_thenFail() {
    dsl.delete(book)
      .where(book.ID.equal(1))
      .execute();
}

6. Conclusion

6.结论

This tutorial introduced the basics of Jooq, a Java library for working with databases. It covered the steps to generate source code from a database structure and how to interact with that database using the newly created classes.

本教程介绍了Jooq的基础知识，这是一个用于数据库工作的Java库。它涵盖了从数据库结构生成源代码的步骤，以及如何使用新创建的类与该数据库进行交互。

The implementation of all these examples and code snippets can be found in a GitHub project.

所有这些例子和代码片段的实现都可以在一个GitHub项目中找到。