Getting Started with jOOQ – 开始使用jOOQ

1. Introduction

1.绪论

In this tutorial, we’re going to take a quick tour of running an application with jOOQ (Java Object Orientated Query). This library generates Java classes based on the database tables and lets us create type-safe SQL queries through its fluent API.

在本教程中，我们将快速浏览使用jOOQ（Java Object Orient Query）运行一个应用程序。这个库基于数据库表生成Java类，并让我们通过其流畅的API创建类型安全的SQL查询。

We’ll cover the whole setup, PostgreSQL database connection, and a few examples of CRUD operations.

我们将涵盖整个设置、PostgreSQL数据库连接和几个CRUD操作的例子。

2. Maven Dependencies

2.Maven的依赖性

For the jOOQ library, we’ll need the following three jOOQ dependencies:

对于jOOQ库，我们需要以下三个jOOQ依赖项。

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.13.4</version>
</dependency>
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-meta</artifactId>
    <version>3.13.4</version>
</dependency>
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen</artifactId>
    <version>3.13.4</version>
</dependency>

We’ll also need one dependency for the PostgreSQL driver:

我们还需要一个PostgreSQL驱动程序的依赖。

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.16</version>
</dependency>

3. Database Structure

3.数据库结构

Before we start, let’s create a simple DB schema for our examples. We’ll use a simple Author and an Article relationship:

在我们开始之前，让我们为我们的例子创建一个简单的DB模式。我们将使用一个简单的Author和Article关系。

create table AUTHOR
(
    ID         integer PRIMARY KEY,
    FIRST_NAME varchar(255),
    LAST_NAME  varchar(255),
    AGE        integer
);

create table ARTICLE
(
    ID          integer PRIMARY KEY,
    TITLE       varchar(255) not null,
    DESCRIPTION varchar(255),
    AUTHOR_ID   integer
        CONSTRAINT fk_author_id REFERENCES AUTHOR
);

4. Database Connection

4.数据库连接

Now, let’s take a look at how we’ll be connecting to our database.

现在，让我们看看我们将如何连接到我们的数据库。

Firstly, we need to provide the user, password, and a full URL to the database. We’ll use these properties to create a Connection object by using the DriverManager and its getConnection method:

首先，我们需要提供用户、密码和数据库的完整URL。我们将使用这些属性，通过使用DriverManager及其getConnection方法来创建一个Connection对象。

String userName = "user";
String password = "pass";
String url = "jdbc:postgresql://db_host:5432/baeldung";
Connection conn = DriverManager.getConnection(url, userName, password);

Next, we need to create an instance of DSLContext. This object will be our entry point for jOOQ interfaces:

接下来，我们需要创建一个DSLContext的实例。这个对象将是我们的jOOQ接口的入口点。

DSLContext context = DSL.using(conn, SQLDialect.POSTGRES);

In our case, we’re passing the POSTGRES dialect, but there are few other ones available like H2, MySQL, SQLite, and more.

在我们的案例中，我们传递的是POSTGRES方言，但也有一些其他可用的方言，如H2、MySQL、SQLite等等。

5. Code Generation

5.代码生成

To generate Java classes for our database tables, we’ll need the following jooq-config.xml file:

为了给我们的数据库表生成Java类，我们需要以下jooq-config.xml文件。

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.xsd">
    
    <jdbc>
        <driver>org.postgresql.Driver</driver>
        <url>jdbc:postgresql://db_url:5432/baeldung_database</url>
        <user>username</user>
        <password>password</password>
    </jdbc>

    <generator>
        <name>org.jooq.codegen.JavaGenerator</name>

        <database>
            <name>org.jooq.meta.postgres.PostgresDatabase</name>
            <inputSchema>public</inputSchema>
            <includes>.*</includes>
            <excludes></excludes>
        </database>

        <target>
            <packageName>com.baeldung.jooq.model</packageName>
            <directory>C:/projects/baeldung/tutorials/jooq-examples/src/main/java</directory>
        </target>
    </generator>
</configuration>

The custom configuration requires changes in the <jdbc> section where we place the database credentials and in the <target> section in which we configure the package name and location directory for classes that we’ll generate.

自定义配置需要在<jdbc>部分进行修改，我们在这里放置数据库凭证，在<target>部分，我们配置包名和我们将产生的类的位置目录。

To execute the jOOQ code generation tool, we need to run the following code:

为了执行jOOQ代码生成工具，我们需要运行以下代码。

GenerationTool.generate(
  Files.readString(
    Path.of("jooq-config.xml")
  )    
);

After the generation is complete, we’ll get the two following classes, each corresponding to its database table:

生成完成后，我们将得到以下两个类，每个类都对应于其数据库表。

com.baeldung.model.generated.tables.Article;
com.baeldung.model.generated.tables.Author;

6. CRUD Operations

6.CRUD业务

Now, let’s take a look at a few basic CRUD operations that we can perform with the jOOQ library.

现在，让我们来看看我们可以用jOOQ库进行的一些基本CRUD操作。

6.1. Creating

6.1.创建

Firstly, let’s create a new Article record. To do so, we need to invoke the newRecord method with a proper table reference as a parameter:

首先，让我们创建一个新的Article记录。要做到这一点，我们需要调用newRecord方法，并将一个适当的表参考作为参数。

ArticleRecord article = context.newRecord(Article.ARTICLE);

The Article.ARTICLE variable is a reference instance to the ARTICLE database table. It was automatically created by jOOQ during code generation.

Article.ARTICLE变量是对ARTICLE数据库表的一个引用实例。它是由jOOQ在代码生成时自动创建的。

Next, we can set values for all needed properties:

接下来，我们可以为所有需要的属性设置值。

article.setId(2);
article.setTitle("jOOQ examples");
article.setDescription("A few examples of jOOQ CRUD operations");
article.setAuthorId(1);

Finally, we need to invoke the store method on the record to save it in the database:

最后，我们需要对记录调用store方法，将其保存在数据库中。

article.store();

6.2. Reading

6.2.阅读

Now, let’s see how we can read values from the database. As an example, let’s select all the authors:

现在，让我们看看如何从数据库中读取数值。作为一个例子，让我们选择所有的作者。

Result<Record> authors = context.select()
  .from(Author.AUTHOR)
  .fetch();

Here, we’re using the select method combined with from clause to indicate from which table we want to read. Invoking the fetch method executes the SQL query and returns the generated result.

在这里，我们使用select方法结合from子句来指示我们要从哪个表中读取。调用fetch方法执行SQL查询并返回生成的结果。

The Result object implements the Iterable interface, so it’s easy to iterate over each element. And while having access to a single record, we can get its parameters by using the getValue method with a proper field reference:

Result对象实现了Iterable接口，所以很容易对每个元素进行迭代。在访问单一记录的同时，我们可以通过使用带有适当字段引用的getValue方法来获得其参数。

authors.forEach(author -> {
    Integer id = author.getValue(Author.AUTHOR.ID);
    String firstName = author.getValue(Author.AUTHOR.FIRST_NAME);
    String lastName = author.getValue(Author.AUTHOR.LAST_NAME);
    Integer age = author.getValue(Author.AUTHOR.AGE);

    System.out.printf("Author %s %s has id: %d and age: %d%n", firstName, lastName, id, age);
});

We can limit the select query to a set of specific fields. Let’s fetch only the article ids and titles:

我们可以将选择查询限制在一组特定的字段上。让我们只获取文章的ID和标题。

Result<Record2<Integer, String>> articles = context.select(Article.ARTICLE.ID, Article.ARTICLE.TITLE)
  .from(Author.AUTHOR)
  .fetch();

We can also select a single object with the fetchOne method. The parameters for this one are the table reference and a condition to match the proper record.

我们也可以用fetchOne方法选择一个单一对象。这个的参数是表的引用和一个条件来匹配适当的记录。

In our case, let’s just select an Author with an id equal to 1:

在我们的例子中，让我们只选择一个id等于1的Author。

AuthorRecord author = context.fetchOne(Author.AUTHOR, Author.AUTHOR.ID.eq(1))

If no record matches the condition, the fetchOne method will return null.

如果没有符合条件的记录，fetchOne方法将返回null。

6.3. Updating

6.3.更新

To update a given record, we can use the update method from the DSLContext object combined with a set method invocations for every field we need to change. This statements should be followed by a where clause with a proper match condition:

为了更新一个给定的记录，我们可以使用来自DSLContext对象的update方法，结合我们需要改变的每个字段的set方法的调用。这个语句后面应该有一个带有适当匹配条件的where子句。

context.update(Author.AUTHOR)
  .set(Author.AUTHOR.FIRST_NAME, "David")
  .set(Author.AUTHOR.LAST_NAME, "Brown")
  .where(Author.AUTHOR.ID.eq(1))
  .execute();

The update query will run only after we call the execute method. As a return value, we’ll get an integer equal to the number of records that were updated.

更新查询只有在我们调用execute方法后才会运行。作为一个返回值，我们将得到一个等于被更新的记录数的整数。

It’s also possible to update an already fetched record by executing its store method:

也可以通过执行其store方法来更新一个已经获取的记录。

ArticleRecord article = context.fetchOne(Article.ARTICLE, Article.ARTICLE.ID.eq(1));
article.setTitle("A New Article Title");
article.store();

The store method will return 1 if the operation was successful or 0 if the update was not necessary. For example, nothing was matched by the condition.

如果操作成功，store方法将返回1；如果没有必要更新，则返回0。例如，没有任何东西被条件所匹配。

6.4. Deleting

6.4. 删除

To delete a given record, we can use the delete method from the DSLContext object. The delete condition should be passed as a parameter in the following where clause:

要删除一个给定的记录，我们可以使用delete方法从DSLContext对象。删除条件应作为参数在以下where子句中传递。

context.delete(Article.ARTICLE)
  .where(Article.ARTICLE.ID.eq(1))
  .execute();

The delete query will run only after we call the execute method. As a return value, we’ll get an integer equal to the number of deleted records.

只有在我们调用execute方法后，删除查询才会运行。作为返回值，我们将得到一个等于删除记录数量的整数。

It’s also possible to delete an already fetched record by executing its delete method:

也可以通过执行其delete方法来删除一个已经取来的记录。

ArticleRecord articleRecord = context.fetchOne(Article.ARTICLE, Article.ARTICLE.ID.eq(1));
articleRecord.delete();

The delete method will return 1 if the operation was successful or 0 if deletion was not necessary. For example, when nothing was matched by the condition.

如果操作成功，delete方法将返回1；如果没有必要删除，则返回0。例如，当没有任何东西被条件匹配时。

7. Conclusion

7.结论

In this article, we’ve learned how to configure and create a simple CRUD application using the jOOQ framework. As usual, all source code is available over on GitHub.

在这篇文章中，我们已经学会了如何使用jOOQ框架配置和创建一个简单的CRUD应用程序。像往常一样，所有的源代码都可以在GitHub上找到。。