将 jOOQ 与 Flyway 一起使用
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
在执行数据库迁移时，Data Geekery 建议将 jOOQ 与 Flyway - 数据库迁移变得简单 一起使用。在本章中，我们将研究一种使用这两个框架的简单方法。
理念
jOOQ 和 Flyway 在各种开发设置中可以通过多种方式相互交互。在本教程中，我们将仅展示这种框架团队合作的一种变体——我们发现对于大多数用例来说特别引人注目的一种变体。
以下方法的总体理念可以概括如下
- 1. 数据库增量
- 2. 数据库迁移
- 3. 代码重新生成
- 4. 开发
每当您需要修改数据库中的某些内容时，都可以一次又一次地重复以上四个步骤。更具体地说，让我们考虑
- 1. 数据库增量 - 您需要在数据库中添加一个新列，因此您在 Flyway 脚本中编写必要的 DDL
- 2. 数据库迁移 - 此 Flyway 脚本现在是您的可交付成果的一部分，您可以与所有开发人员共享，下次他们检出您的更改时，他们可以使用它来迁移他们的数据库
- 3. 代码重新生成 - 迁移数据库后，您可以在本地重新生成所有 jOOQ 工件（请参阅代码生成）
- 4. 开发 - 您继续开发业务逻辑，根据更新的、生成的数据库模式编写代码
Maven 项目配置 - 属性
以下属性在我们的 pom.xml 中定义，以便能够在插件配置之间重用它们
<properties>
<db.url>jdbc:h2:~/flyway-test</db.url>
<db.username>sa</db.username>
</properties>
0. Maven 项目配置 - 依赖项
虽然 jOOQ 和 Flyway 可以在独立的迁移脚本中使用，但在本教程中，我们将使用 Maven 进行标准项目设置。
这些是我们在 Maven 配置中使用的依赖项
<!-- We'll add the latest version of jOOQ and our JDBC driver - in this case H2 -->
<dependency>
<!-- Use org.jooq for the Open Source Edition
org.jooq.pro for commercial editions with Java 21 support,
org.jooq.pro-java-17 for commercial editions with Java 17 support,
org.jooq.pro-java-11 for commercial editions with Java 11 support,
org.jooq.pro-java-8 for commercial editions with Java 8 support,
org.jooq.trial for the free trial edition with Java 21 support,
org.jooq.trial-java-17 for the free trial edition with Java 17 support,
org.jooq.trial-java-11 for the free trial edition with Java 11 support,
org.jooq.trial-java-8 for the free trial edition with Java 8 support
Note: Only the Open Source Edition is hosted on Maven Central.
Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
See the JDK version support matrix here: https://jooq.org.cn/download/support-matrix-jdk -->
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
<version>3.20.5</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.197</version>
</dependency>
<!-- For improved logging, we'll be using log4j via slf4j to see what's going on during migration and code generation -->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-slf4j-impl</artifactId>
<version>2.11.0</version>
</dependency>
<!-- To ensure our code is working, we're using JUnit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
0. Maven 项目配置 - 插件
在依赖项之后，让我们简单地添加 Flyway 和 jOOQ Maven 插件。Flyway 插件
<plugin>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-maven-plugin</artifactId>
<version>3.0</version>
<!-- Note that we're executing the Flyway plugin in the "generate-sources" phase -->
<executions>
<execution>
<phase>generate-sources</phase>
<goals>
<goal>migrate</goal>
</goals>
</execution>
</executions>
<!-- Note that we need to prefix the db/migration path with filesystem: to prevent Flyway
from looking for our migration scripts only on the classpath -->
<configuration>
<url>${db.url}</url>
<user>${db.username}</user>
<locations>
<location>filesystem:src/main/resources/db/migration</location>
</locations>
</configuration>
</plugin>
上面的 Flyway Maven 插件配置将读取并执行来自 src/main/resources/db/migration 的所有数据库迁移脚本，然后再编译 Java 源代码。虽然官方 Flyway 文档可能建议在 compile 阶段进行迁移，但 jOOQ 代码生成器依赖于此类迁移在代码生成之前完成。
在 Flyway 插件之后，我们将添加 jOOQ Maven 插件。有关更多详细信息，请参阅手册中有关代码生成配置的部分。
<plugin>
<!-- Use org.jooq for the Open Source Edition
org.jooq.pro for commercial editions with Java 21 support,
org.jooq.pro-java-17 for commercial editions with Java 17 support,
org.jooq.pro-java-11 for commercial editions with Java 11 support,
org.jooq.pro-java-8 for commercial editions with Java 8 support,
org.jooq.trial for the free trial edition with Java 21 support,
org.jooq.trial-java-17 for the free trial edition with Java 17 support,
org.jooq.trial-java-11 for the free trial edition with Java 11 support,
org.jooq.trial-java-8 for the free trial edition with Java 8 support
Note: Only the Open Source Edition is hosted on Maven Central.
Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
See the JDK version support matrix here: https://jooq.org.cn/download/support-matrix-jdk -->
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>${org.jooq.version}</version>
<!-- The jOOQ code generation plugin is also executed in the generate-sources phase, prior to compilation -->
<executions>
<execution>
<phase>generate-sources</phase>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<!-- This is a minimal working configuration. See the manual's section about the code generator for more details -->
<configuration>
<jdbc>
<url>${db.url}</url>
<user>${db.username}</user>
</jdbc>
<generator>
<database>
<includes>.*</includes>
<inputSchema>FLYWAY_TEST</inputSchema>
</database>
<target>
<packageName>org.jooq.example.flyway.db.h2</packageName>
<directory>target/generated-sources/jooq-h2</directory>
</target>
</generator>
</configuration>
</plugin>
此配置现在将读取 FLYWAY_TEST 模式并将其反向工程到 target/generated-sources/jooq-h2 目录中，并在其中反向工程到 org.jooq.example.flyway.db.h2 包中。
1. 数据库增量
现在，当我们开始开发我们的数据库时。为此，我们将创建数据库增量脚本，我们将其放入 src/main/resources/db/migration 目录中，如先前为 Flyway 插件配置的那样。我们将添加以下文件
- V1__initialise_database.sql
- V2__create_author_table.sql
- V3__create_book_table_and_records.sql
这三个脚本模拟了我们的模式版本 1-3（注意大写 V！）。以下是脚本的内容
-- V1__initialise_database.sql DROP SCHEMA flyway_test IF EXISTS; CREATE SCHEMA flyway_test;
-- V2__create_author_table.sql CREATE SEQUENCE flyway_test.s_author_id START WITH 1; CREATE TABLE flyway_test.author ( id INT NOT NULL, first_name VARCHAR(50), last_name VARCHAR(50) NOT NULL, date_of_birth DATE, year_of_birth INT, address VARCHAR(50), CONSTRAINT pk_author PRIMARY KEY (ID) );
-- V3__create_book_table_and_records.sql CREATE TABLE flyway_test.book ( id INT NOT NULL, author_id INT NOT NULL, title VARCHAR(400) NOT NULL, CONSTRAINT pk_book PRIMARY KEY (id), CONSTRAINT fk_book_author_id FOREIGN KEY (author_id) REFERENCES flyway_test.author(id) ); INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'George', 'Orwell', '1903-06-25', 1903, null); INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'Paulo', 'Coelho', '1947-08-24', 1947, null); INSERT INTO flyway_test.book VALUES (1, 1, '1984'); INSERT INTO flyway_test.book VALUES (2, 1, 'Animal Farm'); INSERT INTO flyway_test.book VALUES (3, 2, 'O Alquimista'); INSERT INTO flyway_test.book VALUES (4, 2, 'Brida');
2. 数据库迁移和 3. 代码重新生成
以上三个脚本由 Flyway 拾取并按版本顺序执行。这可以通过执行以下操作非常简单地看到
mvn clean install
然后观察来自 Flyway 的日志输出...
[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example --- [INFO] Database: jdbc:h2:~/flyway-test (H2 1.4) [INFO] Validated 3 migrations (execution time 00:00.004s) [INFO] Creating Metadata table: "PUBLIC"."schema_version" [INFO] Current version of schema "PUBLIC": << Empty Schema >> [INFO] Migrating schema "PUBLIC" to version 1 [INFO] Migrating schema "PUBLIC" to version 2 [INFO] Migrating schema "PUBLIC" to version 3 [INFO] Successfully applied 3 migrations to schema "PUBLIC" (execution time 00:00.073s).
...以及来自 jOOQ 在控制台上的日志输出
[INFO] --- jooq-codegen-maven:3.20.5:generate (default) @ jooq-flyway-example --- [INFO] --- jooq-codegen-maven:3.20.5:generate (default) @ jooq-flyway-example --- [INFO] Using this configuration: ... [INFO] Generating schemata : Total: 1 [INFO] Generating schema : FlywayTest.java [INFO] ---------------------------------------------------------- [....] [INFO] GENERATION FINISHED! : Total: 337.576ms, +4.299ms
4. 开发
请注意，每次有人向 Maven 模块添加新的迁移脚本时，所有先前的步骤都会自动执行。例如，团队成员可能提交了一个新的迁移脚本，您将其检出，重建并获取最新的 jOOQ 生成的源，用于您自己的开发或集成测试数据库。
现在，这些步骤已完成，您可以继续编写数据库查询。想象一下以下测试用例
import org.jooq.Result;
import org.jooq.impl.DSL;
import org.junit.Test;
import java.sql.DriverManager;
import static java.util.Arrays.asList;
import static org.jooq.example.flyway.db.h2.Tables.*;
import static org.junit.Assert.assertEquals;
public class AfterMigrationTest {
@Test
public void testQueryingAfterMigration() throws Exception {
try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
Result<?> result =
DSL.using(c)
.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
BOOK.ID,
BOOK.TITLE
)
.from(AUTHOR)
.join(BOOK)
.on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.orderBy(BOOK.ID.asc())
.fetch();
assertEquals(4, result.size());
assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));
}
}
}
迭代
一旦您开始以这种方式执行数据库修改，这种方法的力量就会变得清晰起来。让我们假设我们团队中的法国人更喜欢按照自己的方式做事
-- V4__le_french.sql ALTER TABLE flyway_test.book ALTER COLUMN title RENAME TO le_titre;
他们将其检入，您检出新的数据库迁移脚本，运行
mvn clean install
然后观察日志输出
[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example --- [INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example --- [INFO] Database: jdbc:h2:~/flyway-test (H2 1.4) [INFO] Validated 4 migrations (execution time 00:00.005s) [INFO] Current version of schema "PUBLIC": 3 [INFO] Migrating schema "PUBLIC" to version 4 [INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.016s).
到目前为止一切顺利，但是后来
[ERROR] COMPILATION ERROR : [INFO] ------------------------------------------------------------- [ERROR] C:\...\jOOQ-flyway-example\src\test\java\AfterMigrationTest.java:[24,19] error: cannot find symbol [INFO] 1 error
当我们回到我们的 Java 集成测试时，我们可以立即看到仍然引用了 TITLE 列，但它不再存在
public class AfterMigrationTest {
@Test
public void testQueryingAfterMigration() throws Exception {
try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
Result<?> result =
DSL.using(c)
.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
BOOK.ID,
BOOK.TITLE
// ^^^^^ This column no longer exists. We'll have to rename it to LE_TITRE
)
.from(AUTHOR)
.join(BOOK)
.on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.orderBy(BOOK.ID.asc())
.fetch();
assertEquals(4, result.size());
assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));
}
}
}
自动化
可以使用另一个称为 testcontainers 的第三方在构建中自动化上述步骤。请查看此处的文章，了解如何执行此操作的示例：https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/
结论
本教程非常容易地展示了如何使用 Flyway 和 jOOQ 构建可靠的开发流程，以便在开发的早期阶段（立即在编译时）而不是在生产中防止与 SQL 相关的错误！
请访问 Flyway 网站 了解有关 Flyway 的更多信息。
反馈
您对此页面有任何反馈吗？我们很乐意听到您的反馈！