JOOQ-入门手册-全-

JOOQ 入门手册（全）

原文：Beginning jOOQ

协议：CC BY-NC-SA 4.0

一、欢迎使用 jOOQ

我 15 岁开始从事软件工程(实际上，是严肃的计算机业务)，使用 Oracle 8i SQL。是的，从技术上来说，我从小就是个老人了。在我开始上大学之前，玩 SQL* Plus，尝试(并失败)我的第一次 Oracle SQL 认证考试，教会了我正确使用 SQL 的价值。别听我的，听这家伙的:

我是一个数据访问纯粹主义者:我喜欢我的数据被冷却，我的准备好的报表被准备好，我的 SQL 被像爱人一样细心和温柔地手写...世界已经发展到了 Hibernate、Java Persistence API (JPA)以及介于两者之间的一切...我仍然相信原始 SQL——一个精心制作的 SQL 语句将胜过对象关系映射(orm)。

—一个高大、黝黑、年轻而帅气的男人，有着一头乌黑飘逸的头发和一双锐利的棕色眼睛

SQL 的大杯饮料？大概是我；我不知道。关键是我非常欣赏结构化查询语言(SQL)和它所提供的一切。业界一直对 NoSQL 着迷，因为它“易于使用”并且“扩展迅速”，但事实是，SQL 仍然是在线分析处理(OLAP)中无可争议的王者。当您需要数据的健全性和完整性时，SQL 就在那里。当您想要(大多数 2 )可靠的事务处理保证(à la ACID)时，您的堆栈中仍然需要可靠的 SQL。数据库存储过程通常会优于应用层(例如，Java。Net)的处理。用已故伟大的灭霸的话说:“SQL 是不可避免的。把它做好符合您的应用的可伸缩性和正确性的利益。”

不幸的是，SQL 现在受到了开发者的冷遇。数据库只是另一个“黑匣子”，我们应该对着它大喊命令，所以它产生一些数据，然后我们继续前进。直到我们的查询由于可预防的问题而逐渐降级；在我们的应用的两个版本之后，我们的模式是一个不连贯的混乱；SQL 注入袭击暴露了我们的弱点；应用会因返回超过几百行的查询而阻塞。SQL 的一个缺点是，乍一看，您可能没有意识到您的 SQL 查询正在返回不正确或不完整的数据。你运行一个查询，它返回一些查询，就这样，对吗？哎呀。

这本书不是关于 SQL 的基础知识。或者甚至是 SQL 本身的乐趣(有很多)。这本书是关于用不同的眼光看待 Java 中的 SQL 工作。

Java 中的数据库操作:精彩部分

在 Java 世界中，处理 SQL 数据的选择相当简单:

  1. JDBC (Java 数据库连接):JDBC 是支持关系数据库管理系统(RDBMS)访问的最基本的 API。它提供了

    • 连接管理

    • 直接 SQL 语句控制

    • 存储过程和函数执行

    • 主要是 SQL 注入安全组件

    • 事务管理

除了一两个 JakartaEE 规范，Java 生态系统中几乎所有其他相关的 RDBMS 都是基于 JDBC 的。因为有了 JDBC，我们才能拥有…

  1. 映射框架:是的，我说的是像 Hibernate、MyBatis 和 EclipseLink 这样的对象关系映射(ORM)框架。这些是 très 便利的框架，其前提是开发人员不想花任何时间…开发 SQL 或其他与数据库相关的结构。太棒了。有了这些映射框架，您可以定义一些类，在它们和框架上添加一些注释:

    • 将 java 类(对象模型)映射到数据库表(域模型)。这种映射用于将查询结果转换成 java 对象，称为实体。这些实体是管理对象——就像礼宾服务——内存中实体的变化被 ORM 运行时跟踪和持久化。

    • 允许您使用 java 对象关系(is-a，has-a 类型的东西)以声明方式对 RDBMS 中的表之间的关系进行建模。大量的注释支持 ORM 的这个特性。

    • 完全免除了与所有这些魔术中涉及的 SQL 相关的任何细节。它只是工作。

    • 提供声明性的事务处理——带有更多注释。

    • 提供了另外一种查询语言，Hibernate 查询语言(HQL ),它为混合语言引入了面向对象的味道。这样就可以完全抛弃 SQL(！)而且只是一直说流利的 OOP！

大多数 ORM 提供者提供某种形式的数据库查询结果缓存。这里的目标是保存到数据库的行程时间，以便以后访问数据库。因此，当一个用户加载一些数据一次，如果他们请求相同的行，它已经在内存中。

然后我们有 Java 持久性 API (JPA)。这是一个 JakartaEE 规范，试图标准化 Java 平台中 orm 的使用和行为。各种提供者(Hibernate 等。)在不同程度上实现这个规范。它们都有 API 不支持的特定于实现的语法。如果您愿意，API 仍然允许您编写原始 SQL，并且结果仍然可以是托管对象。相当整洁。

除此之外，像 Spring 这样的框架提供了JdbcTemplate作为 JDBC 本身的包装。Java 系统中的 SQL 只是方便的一方。不错！

Java 中的数据库操作:不太好的部分

问问你自己:就前端开发而言，为什么 JavaServer Pages (JSP)和 JavaServer Faces (JSF)不如 React.js 或 Vue.js 那样受欢迎？许多基于 Java 的组织乐于拥有 Java 或 Kotlin Spring Boot 后端，但前端是而不是 Java 。因为当您在浏览器这样的领域中关心性能和资源效率时，没有什么比原始 JavaScript 更好的了。

我这么说是因为我花了相当多的时间讲授 JSF 并回答 StackOverflow 上的问题。不要误解我:JSF 是超级方便的。见鬼，这就是我最初进入 JSF 的原因:一种廉价、方便、实用的方式将标记和脚本输出到网页中。但是没人看的时候，我知道。我知道原始的 JavaScript 仍然存在。如果你想让你的浏览器跳舞，处理个别浏览器的怪癖和细微差别，你转向为浏览器发明的语言。这些 hips 样式表不会说谎。

然而现在，许多人认为 SQL 在与数据库交互时应该退居二线。将为平台构建的语言停放在车库中；王者语言 Java 更可取。在许多情况下，事实并非如此。以下是一些原因:

  1. JPA isn’t aware of what type of database you’re using, which is a shame, when you consider that there are specific quirks, features, and limitations of individual databases, for example:

    • MySQL 不支持交集或例外集操作；完全加入也在菜单之外。你不会知道，直到你试图使用它，你的手术窒息。

    • JPA 不知道如何处理ORDER BY子句中的空值；标准 SQL 也不支持ORDER BY NULLS FIRST子句。 3 这里就靠你自己了。

    • JPA 在某些场景中不能很好地处理IN子句:

      • 当您想要利用查询计划缓存时

      • 当传递给IN子句的参数列表中有空值时

    • PostgreSQL 支持大量数据类型，这些数据类型针对某些用例进行了高度特定和高度优化。在这个 RDBMS 中，您可以利用各种各样的数据类型，您必须做大量额外的工作来支持 Hibernate 中的UserType s。

    • 许多主流数据库提供商(至少包括 Oracle、PostgreSQL 和 MySQL)都提供文档存储和 SQL 查询——没错，您可以在这些数据库中保存您的 JSON 文档，使用 SQL 查询和导航文档中的。基本上将 NoSQL 和 SQL 结合在同一个盒子里。一些基准测试表明，其性能在一定程度上可以与 MongoDB 相媲美。这些不是你祖母的关系数据库。

    “但我想让我的应用具有可移植性。”您的企业在 Oracle 许可证上花费了巨额资金，但是您将使用其 5%的功能，就像一个非常漂亮的 Excel 电子表格？

  2. 即使有了原生的查询功能，JPA 和 Hibernate 也不能把你从自我中拯救出来。如果您犯了正确的错误，您的原始 SQL 仍然对 SQL 注入开放。您的 SQL 仍然可能是不正确的，直到您尝试执行本机查询时才会发现。Java 持久性查询语言(JPQL)和 Hibernate 查询语言(HQL)也救不了你。除非您尝试运行它，否则您不会发现您的查询语法有问题或不正确。如果您不小心对一个托管的 JPA 实体进行了更改，它会在第一时间提交给数据库。

  3. 还记得 Hibernate 和其他工具默认为你做的缓存吗？猜猜谁的 RAM 在慢慢被吞噬？继续猜。您可能会惊讶地发现，由单个 hibernate 会话检索和管理的每个实体都被缓存了——仅针对该 hibernate 会话——因此，在具有任意数量并发用户的大型企业应用中，他们都很容易在应用服务器的 RAM 中保存完全相同的数据副本。想象一下您的应用在大规模运行时会变得多么饥渴！

  4. 你能自信地说你知道 Hibernate 或 EclipseLink 内部发生了什么吗？您是否尝试过查看 Hibernate 生成的实际查询？你可能会很失望。在很多情况下，Hibernate 对于隐藏在视图之外的数据库访问是一种浪费:

    • 默认情况下，批量插入和更新是不启用的，您需要做一些工作来完全支持这两者。

    • 更具体地说，将GenerationType.IDENTITY与 PostgreSQL 和其他一些语言一起使用，Hibernate 仍然会忽略任何批处理指令。4

  5. 当您需要扩展时，ORM 的挑战真的会摆在您面前。几排，也许几百排，你可以滑过去。另一方面，较大的结果集有时并不实际(参见前面的讨论:将所有检索到的实体加载到内存中)。您可能很难处理查询量的增加。JPA 2.2 引入了对更高效的数据库流的支持；但是不同类型的数据库处理这个特性的方式也不同。因此，尽管您尽了最大努力，MySQL 和 PostgreSQL 仍然可以很好地检索整个ResultSet、、 5 、，忽略您对高效结果流的 JPA 2.2 期望。

  6. Fundamentally, ORM as a concept is at odds with relational data representations. The technical name for it is the Object-Relational Impedance Mismatch. Don’t take it from me; Ask the nerds at hibernate these times 6 :

    The way of accessing data in Java is fundamentally different from the way of accessing data in relational database. In Java, you navigate from one association to another in the object network. This is not an effective way to retrieve data from relational databases. You usually want to minimize the number of SQL queries, so that before you start traversing the object network, you can load several entities through connections and select the target entities.

关键是，过了某一点，你要么要处理一堆注释和一个复杂得令人绝望的实体类图，要么你需要卷起袖子去做 SQL-y。

如果要在 Java 中正确地进行数据访问，就不能避免编写 SQL。这不是在挖苦 Hibernate 的那帮人；这个框架对于许多用例来说是一个奇迹。但我们不是来讨论冬眠的，对吧？

你一定是朱青

是的，我是来告诉你 jOOQ 框架的好消息的。首先:jOOQ 不是 Hibernate、JPA 或该领域任何东西的完全替代品。JPA 实现了为 Java 开发人员标准化大多数 RDBMS 访问的目标。Hibernate 非常强大和方便；尤其是写操作，你可以随心所欲。我的意思是，有什么能比简单地更新 java 对象的一个字段并persist -ing 或flush -ing 更方便呢？

对于成人层，大规模的 SQL 数据角力？哪里有批读操作？你需要大枪。jOOQ 是我们这行里最大的一把枪。不要相信我，相信一个真正的 ORM 专家:

这个小伙子怎么样:

…虽然抽象 SQL 写语句是一项可行的任务，但在读取数据方面，没有什么能胜过原生 SQL...几乎在任何企业系统中，本机查询都是不可避免的...虽然您可以从 JPA 启动原生查询，但是不支持动态原生查询构建。jOOQ 允许您构建类型安全的动态原生查询，增强您的应用抵御 SQL 注入攻击的能力。jOOQ 可以与 JPA 集成，正如我在博客中已经证明的那样，JPA-jOOQ 组合可以提供一个可靠的数据访问栈。

——Vlad mialcea【7】

Vlad 是 Hibernate 平台的贡献者，并继续支持他自己的查询性能优化器，他是 Java 和关系数据库之间关系的权威。为此干杯。

如果你认为 JPA 和它的实现对面向对象编程(OOP)过于友好，jOOQ 会寻求一种平衡。提供对安全、有效和高效的 SQL 处理的细微差别的强烈意识，同时保持对事物的面向对象方面的牢固掌握。jOOQ 盒子里有什么？

jOOQ 特色游

好了，集合大家 8 ！让我为你唱首我的人民之歌。哦，这是一个关于…

数据库感知

jOOQ 是 RDBMS 供应商意识到的。与 ?? 不同，jOOQ 提供了专门为各大数据库厂商构建的 API 套件。这样，当你使用 jOOQ 工具和 API 时，你可以确保你的习惯用法是正确的；代码的语法和语义得到了透明的优化，并与您选择的数据库紧密匹配。令人高兴的是，它不会转化为与 Java 代码的紧密耦合。因为 jOOQ 更接近数据库，所以您的 Java 代码仍然可以保持技术上的数据库不可知；您的 jOOQ 库实现将悄悄地处理您选择的特定数据库的更好的细节和细微差别。如果您选择切换数据库，那么您可以很容易地在 jOOQ 中翻转一个配置来支持另一个配置。

代码生成

jOOQ 对于开发者体验来说是非常方便的。观察。

从以下任一项开始:

  • 现有数据库模式

  • 数据库表

  • 存储过程或函数

  • 顺序

  • JPA 实体

  • XML 文件

  • 包含 SQL 的明文文件

  • Liquibase YAML、XML 或 JSON 文件

  • 自定义数据类型

jOOQ 将生成 java 类、JPA 注释的实体类、接口，甚至是正确映射源材料中所表示内容的 XML。如果您从存储过程或函数开始，jOOQ 将生成“例程”:表示数据库代码的类，使它尽可能方便地使用已经可用的东西。

类型安全

您将使用的所有实体、数据和字段都是类型安全的——保证正确无误。数据库表中的字段，存储过程和函数中的绑定变量，等等。被翻译成 Java 中最接近的匹配项。

特定领域语言

jOOQ 提供了一个 DSL，它反映了由您选择的数据库供应商实现的特定 SQL 方言。除此之外，该库还集成了受支持的数据库供应商的怪癖和非标准特性。你知道的，那些坏小子数据库(看着你，甲骨文)。这种对 SQL 语法的编译时检查意味着您将不得不特意编写不完整的或不支持的 SQL。不用再等到运行时才发现你的 SQL 无法编译！

工具支持

您还可以获得一个强大的工具包，以支持开发人员方便和安全地使用该库:

  • Maven: jOOQ 提供了可配置的 Maven 插件和工具，以支持围绕代码生成特性的各种任务。

  • 命令行:也支持 jOOQ 强大的代码生成功能。此外，还有一个模式差异特性，可以帮助比较两个模式并输出差异。

  • Flyway 和 Liquibase:您可以通过 jOOQ 使用两个最流行的 DML 和 DDL 版本控制库。

  • 原始 SQL 供应商安全性:如果您选择不使用 SQL DSL，而是选择原始 SQL，那么您可以在代码中的任何地方使用 jOOQ 的Parser来确保您正在编写的 SQL 对于所选的数据库是正确和有效的。

  • SQL Disabler:PlainSQLChecker允许您在项目中禁用对原始 SQL anywhere 的支持。将它放到 Maven POM.xml中，它将禁止在您的项目中编译任何原始 SQL。这样，你可以保证任何参与你的项目或代码库的人都必须使用 jOOQ 生成的代码和 jOOQ 流畅的 API 消除不正确的 SQL 渗入代码的可能性。

JVM 语言

我们要带着 jOOQ 盒子巡回演出！您可以将 jOOQ 与

  • 我的锅

  • 斯卡拉

  • 绝妙的

这已经是 50000 英尺了。jOOQ 的飞越和它带来的东西。我想在这里强调 jOOQ 并不是 ORM 的替代品——合适的工具，合适的工作，等等。在哪里冬眠等等。不给你你需要的覆盖范围，这就是 jOOQ 开始行动的地方。

所以！到目前为止你觉得怎么样？很整洁，是吧？我就在这里歇一会儿，下一章见！

二、jOOQ 入门

让我们把这个东西旋转！但是首先，你应该了解这些类和接口；他们是 jOOQ 中 75%工作的核心:

  1. org.jooq.impl.DSL

    This is the sturdy, wise grandparent of most of jOOQ’s functionality. With this class, you can

    • 以类型安全的方式操作部分强类型 SQL 查询。

    • 访问数据库功能(concat、coalesce等)。)就好像它们是 java 方法一样。

    • 指定数据库方言。这是在您需要执行数据存储特有的操作时使用的(Oracle、Amazon Redshift、PostgreSQL 等。).

    • 进行数据库定义语言(DDL)操作，如ALTER、CREATE、DROP等。，所有这些都是以类型安全的方式进行的。

    • 执行更平凡的任务，如

      • 构造明文 SQL

      • 配置数据库连接

    它的几乎所有功能都是类中的静态方法，所以你通常只需要执行一个import static org.jooq.impl.DSL.*来使用它的特性。

  2. org.jooq.DSLContext

    Where DSL offers almost all its functionality in static methods without state, DSLContext is more object oriented. There’s some overlap between this component and the DSL class, but there’s a fundamental difference. It’s really in the name DSL: Domain-Specific Language Context. Keeping state in a context object provides runtime capabilities that you’re not going to get from DSL – comes in handy when your SQL operation is a multi-step one or is part of a longer process. Overall, DSLContext offers

    • 流畅的编程风格

    • 有状态组件

    • 与 Spring 等依赖注入框架更好的集成(稍后会详细介绍)

    DSLContext流畅的编程风格可圈可点。这不是标准的构建器模式风格的方法调用链。这是一个真正的 DSL，可以防止您意外地(甚至是故意地)构造不正确的 SQL。只有在前一个方法调用正确支持时，链中的每个方法调用才是可能的。事实上，您需要非常努力地在 jOOQ 中构造不正确的 SQL。因为 DSLContext 的使用主要是有状态的，所以在使用这个类时，您需要更加注意线程安全。

  3. org.jooq.conf.Settings

    The Settings class will let you further customize your jOOQ context with simple, straightforward parameters that change the behavior of the API. An instance of this class can help you control factors like

    • jOOQ 使用的那种 JDBC——常规语句或 ??。 1

    • 在相同的 jOOQ 上下文中映射不同的模式。

    • 控制 jOOQ 正在执行的 SQL 语句的日志记录。

    • 禁用对 Java Persistence API (JPA)注释的支持，以略微提高性能。

    • 配置 jOOQ 内部 SQL 解析器的行为——例如，为特定的 SQL 方言进行设置。如果您使用 jOOQ 而不是它的 typesafe 选项来处理原始的 SQL 字符串，这将适用。

    • 配置 JDBC 特有的选项，如queryTimeout和maxRows。

    • 为批处理操作配置批处理大小。

  4. org.jooq.Configuration

    Configuration类包含控制 jOOQ 运行时行为的核心配置项。Configuration负责管理您的数据库连接，插入 jOOQ 引擎以在更广泛的范围内定制其行为，而不仅仅是单个 SQL 查询。org.jooq.Configuration提供了允许您插入定制代码的方法，这些代码可以完全替换或支持标准 jOOQ 功能。

  5. org.jooq.meta.jaxb.Configuration

    您将使用这个类来控制 jOOQ 的代码生成特性。这是一个名为library.xml的 jOOQ 配置文件的直接模拟。生成数据库表、行、列、存储过程等的 java 表示。是 jOOQ 平台的一个主要特性，我很期待这本书的这一部分！是的:org.jooq.Configuration对org.jooq.meta.jaxb.Configuration可能会导致一些尴尬。

img/500754_1_En_2_Figa_HTML.png

哪款进口声明穿起来最好？恶作剧问题:是贝基 。

伊甸园汽车市场

我将用汽车经销商作为我将在本书中展示的所有例子的背景。Eden Auto 是一家通过门户网站销售新车和二手车的汽车经销商，客户可以

  • 通过汽车本身的许多不同属性来搜索汽车

  • 搜索不同品牌的车辆

  • 在一个价格范围内搜索

在后端，Eden Auto 的员工可以

  • 上传待售汽车

  • 更新现有价格和其他属性

  • 从库存中删除汽车

  • 查看现有库存

  • 通过多种属性搜索汽车库存

这里我们将使用一个相对简化的数据模型，只是为了演示 jOOQ 的具体特性。这是它的样子。

img/500754_1_En_2_Figb_HTML.jpg

为了帮助运行本书中的一些示例，您可以使用本书附带的代码示例中的数据库定义进行引导。

设置 jOOQ

首先，您需要考虑将要使用的 RDBMS 的细节。jOOQ 的魅力在于它允许你使用几乎所有的数据库，非常方便。jOOQ API 可以透明地模拟所选数据库中没有的特性。对于这本书，我将使用 MySQL 做大部分的演示，偶尔会使用一些其他流行的数据库。

我需要 MySQL 的 Connector/J 数据库驱动程序作为依赖项:

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>${mysql-driver-version}</version>
</dependency>

在 jOOQ 的实际设置上，事情变得有点…有趣。你看，jOOQ 既有免费软件也有商业软件——双重许可模式。许可证模型决定了(除其他事项外)

  • 您可以在应用中使用的 SQL 方言

  • 你在项目中设置 jOOQ 的方式

对于 jOOQ 的免费版本，这是一个简单的 maven 条目，因为它在全球 Maven repo 中公开提供:

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

很简单，对吧？布埃诺。然而，当你为商业版本付费时，事情变得更加手动。事情是这样的。

为商业许可的 jOOQ 安装依赖项

jOOQ 的商业 2 版本的依赖项在公共存储库中不可用，因为它们不是免费的，因此不能公开下载。要获得商业版(或试用版)，请访问 www.jooq.org/download/versions 下载与您的 Java 版本相匹配的版本。

<screenshot of versions page>

一旦你输入了你的详细信息，系统会提示你下载一个 zip 文件，其中包含

  • 来源

  • 编译的 jar

  • 助手脚本

zip 文件中有两个助手脚本:

  • maven-deploy

  • maven-install

这两个助手对不同的目标做同样的事情:构建 jOOQ jars 并将其安装到存储库中。将在远程存储库中设置 jOOQ JARs，因此需要在中央 Artifactory 或类似的依赖存储库中设置 JAR。对于本地 maven 存储库，找到maven-install脚本，你就被排序了。在所有这些脚本操作之后，您可以像这样将 commercial jOOQ 添加到您的项目中:

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

它和免费版的artifactId一样，但是有一个不同的groupId:org.jooq.pro。你可以通过使用groupId的org.jooq.trial来使用 jOOQ 商业版的试用许可。开源版本对于许多用例来说功能一样，但是数据库供应商支持有限，功能也更少。 3 同样值得注意的是，jOOQ 的 JDK 支持从 JDK 6 开始，一直到最新的(17，截至本文撰写之时)。旧的 JDK 版本只支持付费版本。

Tip

商业 jOOQ 发行版的试用版将打印一条消息，表明是的，您确实在使用商业 jOOQ 发行版的试用版。但是值得庆幸的是，试用版的用户们，对于 jOOQ 来说是有效的，因为它是慷慨的:设置 JVM 属性org.jooq.no-logo=true来禁用试用许可警告消息。

就这样！jOOQ 设置在您的项目中。现在用它来做一些事情…

jOOQ 的 CRUD

有了 jOOQ JARs，配置了数据库驱动程序，我们现在应该开始使用这个坏小子了。我们需要获取连接，加载驱动程序，等等。如果您以前没有用 Java 做过数据库工作，我将向您展示如何用 Java 做这件事:

try(Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/test?user=testuser&password=thisisaterriblepassword")){
      //more to come
}catch(SQLException sqlex){

}

上述代码片段的目标是获取数据库连接；jOOQ 可以处理接下来的所有事情。如果你遵循一些常识性的原则，jOOQ 本身并不太在意你如何获得这种联系。在现代应用中，这不是一种特别好的获取连接的方式。您应该使用javax.sql.Datasource类和连接池，而不是手动争论连接驱动程序。但稍后会详细介绍。现在，我们有了一个数据库连接，因此是时候启动 jOOQin 了。

Remember

DSL和DSLContext是 jOOQ 生态系统的主要入口。

从根本上说，大多数 jOOQ 操作将从以下内容的一些变体开始:

DSLContext context = DSL.using(connection,SQLDialect.MYSQL);

在前面的示例中

  • 我提供了 JDBC 连接对象(我如何获得连接现在并不重要)。

  • 我从SQLDialect枚举中提供一种方言传递给DSLContext。

根据制造商的说法，DSLContext并不总是保证是线程安全的——毕竟它是一个上下文对象。然而，如果你个人采取合理的预防措施，你可以享受这个类的线程安全。已经为DSLContext提供了有效的 JDBC 连接，现在可以进入创建、读取、更新和删除 (CRUD)的本质。但首先，我们的赞助商要说一句话…

你的 SQL 方言和你

虽然在您的DSLContext上设置 SQL 方言不是强制性的，但是您最好这样做。看，RDBMSes 有许多不同的怪癖，我已经介绍过了。其中一些很显眼，很容易被发现。其他更琐碎的事情会出乎意料地绊倒你。以我选择的 MySQL 数据库为例:

  • 标识符样式:您可能已经意识到不同的数据库对标识符使用不同的引用样式。因此，标准 SQL 将具有
select "e"."first_name", "e"."last_name" from "employees" "e"

但是 MySQL 有 4

select `e`.`first_name`, `e`.`last_name`  from `employees` `e`

反斜线，而不是双引号。这并不是说，如果不指定一种方言，您将被拒绝许多功能。与此相反，jOOQ 通常会尽力模拟一两个数据库本身支持但其他数据库不支持的特性，例如:

  • 返回键:PostgreSQL 中的 SQL INSERT... RETURNING将返回已插入行的生成键，并且由于 insert 操作，它立即可用。这是由 SQL 标准提供的，但数据库并不统一支持。其中，实现细节各不相同。jOOQ 可以为您模拟这个特性，不管您使用的是哪种数据库。这里有一个合理的警告:根据您的数据库对它的支持类型，jOOQ 可能会被迫对数据库进行第二次SELECT访问，以检索生成的密钥。

  • 虚拟表:不同的 SQL 数据库允许您以不同的方式从虚拟表中进行选择。例如，Oracle 有著名的DUAL表。有时，您只想运行一些函数，但是 RDBMS 要求您指定一个FROM子句——而不提供虚拟表。jOOQ 支持不带 from 子句的 select 语句，因此无论有没有虚拟表，您都可以随心所欲。

如果你不指定任何方言，你将得到默认的SQLDialect.DEFAULT，然后通过 con Dios 得到！ 5

Caution

为了您自己的安心，继续在您的Settings对象上配置org.jooq.conf.Settings.backslashEscaping属性。MySQL 和 PostgreSQL 的一些版本支持非标准的转义字符，这会在您最意想不到的时候给您带来很多麻烦。这个属性让 jOOQ 正确地处理 MySQL 的这个“特性”。

这是我们的赞助商说的话！

img/500754_1_En_2_Figc_HTML.png

感谢您的阅读！

好的，现在让我们进入…

jOOQ 中的 CRUD 工具

jOOQ 为 SQL 语句的整体提供了很多细粒度的控制——实际上，这是一种唾手可得的控制。从一个非常高的层次开始，我将介绍一些关键组件，如果您要使用 jOOQ 进行 CRUD，您需要熟悉这些组件:

  1. org.jooq.Query

    This is the fundamental unit of a SQL query in jOOQ-land. It extends org.jooq.Statement along with the jOOQ representations of other executable database units of work like stored procedures and functions. You can use it to execute

    • 作为参数传入的纯文本 SQL

    • 使用 jOOQ API 构造的强类型 SQL 片段

它可以帮助您将手动导出的明文 SQL 转换为 jOOQ DSL 类型的 SQL 对象，反之亦然。这是 SQL 查询的典型表示；当不需要操作返回值时(如数据操作语言操作)，使用它来执行 SQL 语句。

  1. org.jooq.ResultQuery

    This class is basically org.jooq.Query, but you can obtain return values from it, like query results. Some notable qualities of this class:

    • 它的高效之处在于，它不需要一个活动的数据库连接来提供对其结果的访问——一旦查询成功执行，就可以释放连接，并且您可以向该类的实例查询所有查询结果。 6 将此功能与分离的 hibernate 实体进行比较。

    • 它提供了类型化和非类型化的结果获取，类似于使用 Spring JdbcTemplate所获得的结果。

    • 它映射到定制的普通旧 Java 对象(POJOs)。

    • 它支持使用底层数据库游标延迟获取查询结果。如果您走这条路，请注意它依赖于维护实时数据库连接。

    • 它支持反应式编程。

    • 它通过java.util.stream.Stream接口支持流。

总之，它为争论查询结果提供了一个非常灵活的接口。它和你用 Hibernate 或 JPA 得到的任何东西一样强大，比 JDBC 让你经历的同样的结果要少得多。

  1. org.jooq.QueryPart

    On its own, not too powerful. But it’s the parent type of a large suite of classes that help you construct complex queries dynamically. Compare this class to the CriteriaBuilder in JPA or the Specification from Spring Data JPA. To be clear, this API is exorbitantly richer than either of what JPA or Spring Data provide. Pretty much every fragment of the SQL standard specification can be represented as variant of org.jooq.QueryPart, for example:

    • Field表示可以在任何地方、任何种类的 SQL 语句中使用的字段

    • Condition表示像WHERE、HAVING这样的谓词

    • SQL表示一条完整的 SQL 语句或片段

    • 在查询中需要表格的地方，表示整个表格

    • Row来表示元组(或者通俗地说，类似于表中的一行)

    • 代表…是的，一列

    • Constraint表示数据库约束，作为 DDL 操作的一部分

    • Schema表示任何 SQL 查询的模式部分

这是QueryPart能够达到的粒度级别的一个小样本。见鬼，每一种都有更专业的版本，提供特定的优势。如果您曾经梦想能够支持强大的动态过滤器作为搜索功能的一部分，但是不需要到处连接难看的纯文本 SQL 字符串，这就是您想要的地方。

您可以获得强大的、可重用的组件来构建任何类型的 SQL 用例。当您使用 jOOQ 的代码生成特性时，生成的工件也继承自这个家族。非常强大的东西。

  1. org.jooq.Record

    This is the parent class for representations of a row of data from a table. This is in addition to jOOQ’s capability to work with your custom POJOs and Data Transfer Object (DTOs). You get the following specializations of this class, among others:

    • org.jooq.UpdatableRecord

      与 hibernate 实体相比:它是一个活动的对象，在其生命周期内一直绑定到底层数据库行。这样，您可以修改刷新、修改或删除一个UpdatableRecord实例，并且它可以反映底层表中的变化。

      当您使用 jOOQ 生成的工件时，默认情况下您会得到扩展这个工件的类。这只适用于有主键的表——没有主键，您将得到一个不同的生成工件。此外，您将能够使用外键引用导航到可更新记录的相关实体。就像 JPA 一样！但是更好！没有缓存，因此可以保证您始终使用最新的实时数据。在 RAM 上也更容易。

    • org.jooq.TableRecord

      当底层表不使用主键时，这是您得到的而不是一个UpdatableRecord。因此，您将无法刷新或更新这些记录的一部分；但是，您仍然可以用这个类插入行。

    • org.jooq.RecordMapper

      使用此类对查询结果的翻译进行更细粒度的控制。将此与 Spring 的RowMapper类进行比较。

  2. org.jooq.Result

    这是一个保存查询结果的容器类。实际上，您的Record对象或Record对象列表将被一个Result实例包装。这是一个Collection、Iterable、List和Serializable——所以你可以用它做很多事情(函数式、反应式和 JDK 流编程)。它也可以与 JDBC 的ResultSet互操作。它还有一个额外的优势，那就是它不像ResultSet那样依赖于开放的数据库连接。

你感觉到力量了吗？

img/500754_1_En_2_Figd_HTML.png

你呢？

我希望如此，因为我们要用这种力量…

选择语句

让我们向我们的老朋友问好:

DSLContext context = DSL.using(connection,SQLDialect.MYSQL); //hullo!

首先，我们需要能够运行普通的 SQL 查询。这是它的样子。给定我手动创建的自定义CompleteVehicleRecord POJO:

public class CompleteVehicleRecord {
    Long id;
    String brand;
    String model;
    String trim;
    BigDecimal price;
    String color;
//getters, setters, toString and hashCode;
}

这个类在数据库中封装了一辆待售的汽车。我对选择数据库中所有可用的车辆感兴趣。用 SQL 术语来说，应该是这样的:

SELECT * FROM edens_car.complete_car_listing;

以下是 jOOQ 的情况:

DSLContext context = DSL.using(connection,SQLDialect.MYSQL);
                ResultQuery resultQuery = context.resultQuery("SELECT * FROM edens_car.complete_car_listing"); (1)
                List<CompleteVehicleRecord> allVehicles = resultQuery.fetchInto(CompleteVehicleRecord.class); (2)
                logger.info(allVehicles.toString());

没什么特别的，只是你普通的选择语句。 7 这里:

  1. 我将我的明文 SQL 传递给DSLContext#resultQuery方法来检索所有可用的汽车。我选择从执行中获得一个ResultQuery,因为我想从中获得结果。如果这是一个我不想要结果的查询(比方说，删除或插入语句)，我会使用Query类来代替。

  2. 在ResultQuery上，我调用了fetchInto方法来自动映射结果中的每一行

    • 将返回的每一行映射到CompleteVehicleRecord的一个实例中

    • 将所有映射的行放入列表CompleteVehicleRecord

jOOQ 能够推断出我的 POJO 的字段和 jOOQ select 语句中返回的列之间的映射。正如您可能已经猜到的，SQL 查询中的类变量和列的名称需要匹配。有其他方法可以有意地建立这种映射；我们将在本书的后面部分检查它们。

或者，我可以使用fetchMany方法，这样我可以更好地控制每一行的映射:

resultQuery.fetchMany().forEach(results -> {
                      //container for all the results
                      results.forEach(record -> {
           logger.info("New result row");
           logger.info("Brand: {}",record.getValue("brand"));
           logger.info("Model: {}",record.getValue("model"));
           logger.info("Trim: {}",record.getValue("trim"));
           logger.info("Color: {}",record.getValue("color"));
           logger.info("Price: {}",record.getValue("price"));
           });
      });

fetchMany方法保证永远不会返回空值，所以不要基于任何空值检查的逻辑。如果我只期待一个结果，我会使用fetch方法。您还可以使用这种方法在同一次执行中执行多个 SQL 语句——这是一种简单但非常强大的方法。继续阅读这种方法的更多用法。

我还可以向 SQL 语句提供查询参数，如下所示:

ResultQuery resultQuery2 = context.resultQuery("SELECT * FROM edens_car.complete_car_listing where color = ?","BLUE");

查询绑定——我说“蓝色”的地方——就是你如何为你的 SQL 语句提供参数。当与底层DSLContext API 上的PreparedStatement配置结合时，这种绑定查询参数的方法提供了一些 SQL 注入保护。

Remember

jOOQ 可以默认使用 JDBC PreparedStatement组件，如果你这样配置的话，那么你的参数仍然可以从该组件内置的 SQL 注入安全性中获益。还可以通过调用Query对象上的keepStatement方法来缓存底层的PreparedStatement；这为频繁使用的稳定查询提供了性能提升。

让我们看看 jOOQ 库的一些更酷、更有目的的用法。

选择 DSL

jOOQ 提供保护您免受畸形的 SQL，SQL 注入，缺少参数，过度限制和丑陋的代码。它以递增的强大方式做到这一点，因此您可以按照自己的速度前进。我们再来重温一下SELECT语句的语境(呵呵)中的DSLContext:

SELECT * FROM edens_car.complete_car_listing

我可以这样重写前面的明文SELECT *:

DSLContext context = DSL.using(connection,SQLDialect.MYSQL);
List<CompleteVehicleRecord> allVehicles = context.select().from(table("complete_car_listing")).fetchInto(CompleteVehicleRecord.class);

这两条语句在功能上是相同的，并且将返回相同的结果:

  1. select()是 jOOQ 对SELECT *的简写。jOOQ 中充满了这样的简写，有助于减少冗长。

  2. table是来自DSL类的函数，静态导入。它帮助我将表的明文 SQL 名称转换成一个org.jooq.Table的实例。这样做的效果是 jOOQ 可以把我的语句当作一个类型安全的版本。

我在这里所拥有的仍然有一部分在普通 SQL 领域中，因此，我必须使用DSL类中的 helper 函数来将我的普通 SQL 翻译成类似 jOOQ 的 typesafe API 的东西。可以这样想:如果我仍然在 select 语句的关键部分使用普通字符串，jOOQ 仍然不能 100%保证许多方面的正确性。这个 DSL 确保我的查询在语法上是正确的，但是它不能保证语义上的正确性——我仍然被允许在表、列等的名称上犯印刷错误。

现在，SELECT *有点浪费，所以我通常更喜欢使用 SQL 预测来更简洁地说明我感兴趣的列:

List<CompleteVehicleRecord> allVehicles = context.select(field(name("brand")),field("model"),field("price"))
            .from(table("complete_car_listing"))
            .fetchInto(CompleteVehicleRecord.class);

像table函数一样，field来自org.jooq.impl.DSL，我在这里使用它只是为了在我手工制作的、无麸质的、手工 SQL 和 jOOQ 的强类型、以 DSL 为中心的世界之间架起一座桥梁。field函数将我的原始 SQL 字段名转换成org.jooq.Field的实例，table将我的表名转换成org.jooq.Table，而name将创建org.jooq.Name的实例。所有这些都是QueryPart s，它们构成了能够构建强大而复杂的查询的基础。

构建 select 语句的另一种方法是单独设置相关字段:

Field<?> brandField = field("brand"); (1)
Field<?> modelField = field("model",String.class);
Field<BigDecimal> priceField = field("price", BigDecimal.class); (2)
List<Field> fieldList = Arrays.asList(brandField,modelField,priceField); (3)

List<CompleteVehicleRecord> allVehicles = context.select(fieldList)
                          .from(table("complete_car_listing"))
                          .fetchInto(CompleteVehicleRecord.class);
logger.info(allVehicles.toString());

在前面的代码片段中

  1. 我从明文“SQL”手动构造了一个org.jooq.Field的实例注意我在这里使用的通配符语法，Field<?>。这是有目的的:jOOQ 完全是关于类型安全的，所以在很多情况下它会喜欢类似类型信息的东西。习惯于指定Field的类型，当不能提供时，使用通配符。

  2. 更好的是，我可以在变量声明的两边提供类型信息。Field可以类型安全，field函数可以提供类型安全信息。因为我不太使用 jOOQ 的类型安全生成的代码，所以 jOOQ 可以使用我提供的任何附加信息来保护我的 SQL 语句的完整性和可靠性。

  3. 然后，我可以将所有必要的字段存储到一个标准 java List中。

  4. jOOQ 查询的任何部分都可以使用这个列表。

这个机制真的很强大。使用这种方法，我可以在代码中获得很大的可重用性和灵活性，即使我没有使用 jOOQ 生成代码。我将向您展示更多这种可重用性的场景——这就是 jOOQ 库中QueryParts组件的威力。

Tip

使用DSL#name函数以模式安全的格式处理原始 SQL 标识符。当为DSLContext启用报价时，它还可以提供 SQL 注入保护。

带select的其他选项包括

  • 用selectDistinct运行一个SELECT DISTINCT。

  • 通过运行selectOne().fetch从哑表中进行选择(比如 Oracle 的 DUAL，或者 PostgreSQL 的 nothing)。这个特性依赖于在DSLContext中正确设置的 SQLDialect 参数。

  • 组合单独构造的SELECT语句；本书后面会有更多的介绍。

如果我想在查询中包含一个查询呢？你知道的！是时候深入研究一下……

使用 WHERE DSL

在决定了我对我的vehicle表中的哪些列感兴趣之后，我希望对返回哪些行有更多的限制——输入全能的WHERE子句。以下是可能的情况:

List<CompleteVehicleRecord> allVehicles = context.select(field("brand"),field("model"),field("price"))
              .from(table("complete_car_listing"))
              .where(condition("color = 'BLUE'"))
              .fetchInto(CompleteVehicleRecord.class);

我在技术上使用 DSL，虽然效果不是很好。这在很大程度上仍然是一个纯文本的 SQL where 子句，我应该为自己感到羞耻——我确实是这样。因此，我将再次尝试使用绑定变量，如下所示:

context.select(field(name("brand")),field("model"),field("price"))
             .from(table("complete_car_listing"))
             .where(condition("color = ?","BLUE"))
             .fetchInto(CompleteVehicleRecord.class);

从 SQL 注入的角度来看，这更安全一些。我仍然负责确保前面代码片段的语法是正确的，并且当整个代码组合在一起时会正确地输出。总的来说，这仍然读起来更好，更流畅。我喜欢 jOOQ DSL 的一点是，它致力于确保在构造 SQL 时不会出错。例如，除了在from子句之后，我不可能在任何地方使用where节点。因此，即使我选择在特定的时间间隔继续使用普通的 SQL，我仍然可以得到一些保护，因为我的 SQL 在语法上是正确的。此外，我还受益于 jOOQ 在处理 JDBC 连接时使用的资源高效方法。

Pop Quiz

org.jooq.impl.Settings和org.jooq.impl.Configuration类有什么区别？

Answer

通过简单地改变一个设置，Settings类将允许你改变 jOOQ 运行时的预定义行为。Configuration类提供了对 jOOQ 中扩展点的访问，您可以提供定制代码来替换或改变 jOOQ 运行时的行为。

现在，对于 jOOQ API 中我最喜欢的组件之一…

情况

您提供给select DSL 的from节点的参数实际上是org.jooq.Condition的实例。Condition是一个强大的类，可以让您编写简单或复杂的谓词组件。然后，您可以附加到接受条件逻辑的 SQL 结构中的几乎任何位置。所以，我可以这样写一篇Condition:

Condition colourCondition = condition("color = ?","BLUE");
...and then pass that into my constructed {select} execution:
context.select(fieldList)
           .from(table("complete_car_listing"))
           .where(colourCondition)
           .fetchInto(CompleteVehicleRecord.class);

能够动态构造整个 SQL 查询的各个部分是 jOOQ 工作方式的基础。在你阅读这本书的时候，你会看到这种语句的不同风格，但是你的 SQL 语句的每一部分都可以独立于其他部分来构建。你的SELECT、WHERE或HAVING条款等？您可以独立构建它们，然后将它们组合在一起。

通过 jOOQ 用Condition提供的一些便利工具，事情甚至可以变得更加灵活。例如，我可以用可选的WHERE子句构造一个 jOOQ 查询，如下所示。考虑下面的 jOOQ 查询，我用它从complete_car_listing表中选择汽车细节:

List<CompleteVehicleRecord> allVehicles = context.select(field(name("brand")),field("model"),field("price"))
            .from(table("complete_car_listing"))
            .where(colourCondition)
            .fetchInto(CompleteVehicleRecord.class);

所以，这看起来就像你到目前为止看到的 jOOQ 查询，对吗？如何让这个查询使用一个可选的WHERE子句？这样，无论网站用户是否选择过滤标准，我都可以重用相同的查询。因此，考虑这样一个假设的用户界面:

*img/500754_1_En_2_Fige_HTML.jpg

伊甸园汽车网站的过滤条件选择框

有多个选项可以用来过滤搜索结果。您需要能够使用相同的 jOOQ 语句，无论用户是否选择任何过滤标准。jOOQ 查询如下所示:

public static void selectWithOptionalCondition(boolean hasFilter, Map<?,Object> filterValues) throws SQLException{
        ...
        Condition conditionChain = DSL.noCondition(); (1)
        if (hasFilter) {
                for(String key: filterValues.keySet()){
                    conditionChainStub = conditionChainStub.and(field(key).eq(filterValues.get(key))); (2)
                }
            }

     List<CompleteVehicleRecord> allVehicles = context.select(field(name("brand")),field("model"),field("price"))
           .from(table("complete_car_listing"))
           .where(conditionChain)
           .fetchInto(CompleteVehicleRecord.class);
           logger.info(allVehicles.toString());
           }
}

这是细目分类:

  1. 为了获得Condition }类的最大灵活性，jOOQ 提供了DSL#noCondition()方法。这将生成一个条件存根，我可以选择将其他条件链接到该存根。还有其他类似于DSL.trueCondition和DSL.falseCondition的，分别产生设置为true和false的条件。

  2. Condition提供了and运算符。使用这个工具，我可以组合从 web 层传入的过滤条件，如果它们存在的话。如果没有过滤参数传入(例如，hasFilter为假)，那么WHERE条件将不会应用于最终执行的 SQL 语句。否则，将应用构造的Condition。

您可能已经知道，Condition类提供了您需要的所有布尔运算符:or、not、exists，以及所有这些运算符的所有有用排列。我们不要忘记Field类中的比较操作符:

Condition condition = field(name("price")).greaterOrEqual(BigDecimal.valueOf(360000));

没错:Field类本身可以通过类本身提供的许多比较操作符产生条件。

为了让甚至更加灵活地构建您的条件，请查看CustomCondition类:

CustomCondition customCondition = CustomCondition.of(conditionChain ->{
                conditionChain.sql("color ='BLUE'")
                        .sql(" AND price < 35000");
            });

CustomCondition提供了在构建条件的过程中执行复杂逻辑的机会。通过提供一个接受 lambda 的功能接口，您可以对流程进行更多的控制。它仍然产生一个对象，你可以把它和你拥有的任何其他条件结合起来。

灵活性！

Pro Tip

Field类提供了isNull和isNotNull来满足您所有的空值比较需求。保持安全；使用适当的空值比较方法。请注意，除了专门用于处理空值的数据库功能外，还提供了这些功能(例如，COALESCE、NVL等)。).

子查询

例如，我想搜索某个特定制造商最近降价的车辆，因为我认为降价意味着没有人买车，经销商可能愿意给我打折。 9 下面是普通 SQL 查询的样子:

SELECT *                       (1)
FROM  complete_car_listing ccl
WHERE (ccl.vehicle_id , ccl.price) IN  (1a)
            (SELECT vpc.vehicle_id, vpc.revised_price     (2)
                FROM vehicle_price_change vpc
                WHERE vpc.revised_price < vpc.current_price
                AND (vpc.vehicle_id , vpc.last_changed) IN
            (SELECT vc.vehicle_id, MAX(vc.last_changed) (2a)
                FROM vehicle_price_change vc
                GROUP BY vc.vehicle_id))

该查询(从技术上讲，一个主查询和两个子查询)将

  1. 在该子集内，确保最近的价格修订是降价。

  2. 检索库存中汽车的所有详细信息

    1. 我通过使用 SQL 行值表达式机制与子查询的结果进行比较来匹配顶级查询的行。

  3. 查找价格下调的汽车

Caution

“jOOQ 是关于类型安全的”——我，几页前。这仍然是正确的，但是你会看到我在后续的代码示例中跳过了一些类型安全约定，例如，使用field("price")而不是field("price",BigDecimal.class)。这在很大程度上是为了删除一堆样板代码；类型安全的基本原理保持不变。与用 jOOQ 生成类型安全代码相比，使用纯文本 SQL 仍然是一个较差的选择。随着 jOOQ 之旅的继续，纯文本 SQL 将开始产生问题和怪异，这是没有类型安全的直接后果。请记住这一点。

明确地说，还有其他可能更好的方法来获得相同的结果:连接、窗口函数(后面会详细介绍)等等。这是一个虚构的例子，演示 jOOQ 中子查询的一些细节。如果您对本书附带的模式运行此查询，您应该会得到如下所示的结果:

'3', 'Lexus', 'ES 350', 'BASE', '36000.0000', 'BLUE', 'Car', 2018
'4', 'Acura', 'MDX', 'SPORT', '50000.0000', 'BLUE', 'Car', 2018

这在 jOOQ 中会是什么样子？首先，我将声明几个字段和表，以便在我将要构建的查询中重用:

Field<Long> vehicleId = field(name("vehicle_id"),Long.class);
Field<BigDecimal> vehicleRevisedPrice = field(name("revised_price"),BigDecimal.class);
Field<BigDecimal> vehicleCurrentPrice = field(name("current_price"),BigDecimal.class);
Field<BigDecimal> price = field(name("price"),BigDecimal.class);
Table vehiclePriceChange = table(name("vehicle_price_change"));
Field<LocalDateTime> lastPriceUpdate = field(name("last_changed"),LocalDateTime.class);

设置好这些之后，我可以着手设置实际的查询:

final SelectCorrelatedSubqueryStep<Record2<Long, LocalDateTime>> mostRecentPriceChange = context.select(vehicleId, max(lastPriceUpdate)).from(vehiclePriceChange).groupBy(vehicleId); (1)
final SelectConditionStep<Record2<Long, BigDecimal>> mostRecentPriceReduction =  context.select(vehicleId,vehicleRevisedPrice)
.from(vehiclePriceChange)
.where(vehicleRevisedPrice.lessThan(vehicleCurrentPrice))
.and(row(vehicleId, lastPriceUpdate).in(mostRecentPriceChange)); (2)

以下是我对这些问题的看法:

  1. 我构建的查询将提供每个车辆 ID 的最新价格变化。max方法来自可信的DSL类。

  2. 接下来，我使用(1)中的查询来构造查询，获取最近只降价的车辆。row方法也来自于DSL类，用于对子查询进行元组比较。

所发生的事情是，我已经单独构造了 jOOQ SQL 查询，以便于重用。现在，我们赞助商的一句话:“jOOQ 并没有正式推荐这种重用查询的方法，部分原因是考虑到可变性。”如果可读性不会太差，将子查询内联到主查询中会更安全。出于演示的目的，我已经分解了子查询，并将其命名为final。

好了，构造了子查询，让我们继续使用它们:

SelectConditionStep<Record> potentialDealsQuery = context.select().from(table(name("complete_car_listing")))
                             .where(row(vehicleId,price)
                             .in(mostRecentPriceReduction));
String sql = potentialDealsQuery.getSQL();
logger.info(sql);

potentialDealsQuery使用mostRecentPriceReduction子查询获取最近降价的汽车。为了查看生成的查询是什么样子，我可以用getSQL方法从potentialDealsQuery中获取 SQL。结果如下:

select * from `complete_car_listing` where (`vehicle_id`, `price`) in (select `vehicle_id`, `revised_price` from `vehicle_price_change` where (`revised_price` < `current_price` and (`vehicle_id`, `last_changed`) in (select `vehicle_id`, max(`last_changed`) from `vehicle_price_change` group by `vehicle_id`)))

很大程度上是对原始 SQL 意图的忠实翻译，是吗？像往常一样，我将获取结果:

List<CompleteVehicleRecord> potentialDeals = potentialDealsQuery.fetchInto(CompleteVehicleRecord.class);

太多了吧？你已经买了这本书；如果第一轮没有成功，请再看一遍这一部分。我在这里一次介绍了多个概念，这些概念对 jOOQ 总体来说是有帮助的。如果一开始没有全部点击通读img/500754_1_En_2_Figh_HTML.gif完全可以理解

Pop Quiz

:在 jOOQ 中使用纯文本 SQL 时，如何安全地引用字段？

a) Field myField = field("myField")

b) Field <?> myField = field("myField")

c) Field<Integer> myField = field("myField", Integer.class)

答案 : (b)和(c)是推荐的方法；(c)更好！

查询中的条件逻辑

如果您想变得更有趣，您可以在 SQL 中使用一些复杂的条件逻辑。以防我之前没有说清楚:我坚定地站在“让数据库做数据库非常擅长的事情，同时考虑到可维护性”的阵营中。为此，当我看到

  • 首先将查询结果检索到应用层

  • 然后执行数据库非常擅长的复杂分析

所以，我们已经确定我是一个小气鬼，我总是在寻找一辆划算的车——这两者是相互排斥的。我们已经看到了如何找到降价的汽车，因此可能会提供很好的事务。我们现在谈的事务有多好？我会说

  • 降低 5%，“好的”

  • 减少 10%，“好”

  • 10%以上？“太好了！”

SQL 查询将如何呈现这一点？使用CASE功能:

SELECT vpc.vehicle_id 'vehicle', vpc.current_price 'old price', vpc.revised_price 'new price', max(last_changed) 'last price update',
case when ((vpc.current_price - vpc.revised_price)/vpc.current_price)*100 <=5 then 'OK'
         when ((vpc.current_price - vpc.revised_price)/vpc.current_price)*100 BETWEEN 5 AND 10 then 'GOOD'
         when ((vpc.current_price - vpc.revised_price)/vpc.current_price)*100 > 10 then 'GREAT'

否则“不成交”

 end as 'deal'
FROM vehicle_price_change vpc
WHERE vpc.revised_price < vpc.current_price
GROUP BY vpc.vehicle_id, vpc.current_price, vpc.revised_price

对于类似这样的结果:

# vehicle   old price    new price   deal
2           48000.0000   47380.00    OK
3           37565.0000   36000.00    OK
4           55342.0000   50000.00    GOOD

您可能知道这将如何发展:如何在 jOOQ 中表示它？握紧你的键盘:

context.select(vehicleId, vehicleCurrentPrice, vehicleRevisedPrice, max(field("last_changed")),
                    when((vehicleCurrentPrice.subtract(vehicleRevisedPrice))
                            .divide(vehicleCurrentPrice)
                            .multiply(100)
                            .lessOrEqual(BigDecimal.valueOf(5)), "OK") (1)
                            .when(condition("((current_price – revised_price)/current_price)*100 BETWEEN 5 AND 10"), "GOOD") (2)
                            .when(condition("((current_price - revised_price)/current_price)*100 > 10"), "GREAT")
                            .otherwise("NO DEAL") (3)
                            .as("deal")) (4)
   .from(table("vehicle_price_change"))
   .where(vehicleRevisedPrice.lessThan(vehicleCurrentPrice))
   .groupBy(vehicleId)
         .fetch()
             .forEach(result -> {
                   logger.info("Vehicle Id: {} | Revised Price: {} | Former Price: {}| Deal Rating: {}", result.get(vehicleId), result.get(vehicleRevisedPrice), result.get(vehicleCurrentPrice), result.get("deal"));
                    });
        }
    }

让我们来分解一下:

  1. 跳过SELECT语句中的其他字段:我从when方法开始，在这个方法中，我使用了Field类的各种方法来构造构成事务的算法。我做算术，然后传递“OK”作为这个when条件的结果。请注意，这实际上并没有使用case关键字/方法。

    • 或者，如果你想使用CASE (column)的形式，你可以从choose方法开始，而不是when。

  2. 我在这个when中使用了普通的 SQL 选项来展示完全的灵活性。你会注意到我在这里使用了condition方法，因为这正是when方法所需要的:一个 jOOQ Condition。这意味着您可以在 select 语句中需要它们之前构造并重用Conditions。

  3. otherwise方法为我的CASE提供了ELSE子句——包罗万象。

  4. as给我整个 case 语句的别名。

所有这些给了我以下输出:

Vehicle Id: 2 | Revised Price: 47380.0000 | Former Price: 48000.0000| Deal Rating: OK
Vehicle Id: 3 | Revised Price: 36000.0000 | Former Price: 37565.0000| Deal Rating: OK
Vehicle Id: 4 | Revised Price: 50000.0000 | Former Price: 55342.0000| Deal Rating: GOOD

这是本书中我最喜欢的演示之一，因为它展示了 jOOQ 有多灵活。

一切都适用于任何地方，您可以从任何粒度级别编写 SQL 语句；这还不是这种力量的一个复杂例子。

jOOQ 支持一些特定于供应商的条件函数，比如

  • DECODE

  • COALESCE

  • NVL

  • NVL2

  • IIF和IF

  • NULLIF

所有这些都可以作为函数在…你猜对了:DSL类中得到！

WITH…WITH 子句

如果我对计算数据库中所有车辆的中值价格感兴趣，我必须有点创造性。看，这还不是一个标准的 SQL 函数。PostgreSQL 在某种程度上支持它， 10 但对于大多数其他数据库，它将需要一些修补。根据我的经验，在 SQL 中“修补”往往需要相当难看的 SQL；我真的希望能够在我的 SQL 查询中重用它。下面是我使用 SQL 窗口函数(稍后将详细介绍)计算库存中所有车辆的中值价格时的样子:

WITH median_cte as(SELECT ROUND(AVG(price)) median
                   FROM (select price, ROW_NUMBER() OVER (ORDER BY price ASC) AS rowpos, COUNT(*) OVER () AS total_cars from complete_car_listing) price_mod
                   WHERE rowpos BETWEEN total_cars / 2.0 AND total_cars / 2.0 + 1)
select brand, model, trim, price, CONCAT((ROUND((price - median_cte.median)/price,2) * 100),'%') 'relative to median'
from complete_car_listing, median_cte

在 MySQL 中，WITH子句运行一次中值查询，将结果存储在一个临时“表”中。 11 我可以在随后的SELECT语句中使用我指定的名字{ median_cte }引用结果，就像一个表一样。这就是所谓的“商业中”的常用表表达式(CTE)，你可能已经知道了。在 jOOQ 中这看起来像什么？

Field<BigDecimal> price = field("price",BigDecimal.class); //define field and table for reuse
Table completeCarListing = table("complete_car_listing");
CommonTableExpression<Record1<BigDecimal>> medianCte =
name("median_cte") (1)
      .as(context.select(round(avg(price)).as("median")) (2)
                    .from(select(price, rowNumber().over(orderBy(price.asc())).as(name("rowpos")),
                                    count().over().as("total_cars"))
                                    .from(completeCarListing))
                                    .where("rowpos BETWEEN (total_cars / 2.0) AND (total_cars / 2.0 + 1)")
            );

这里发生了什么巫术？我告诉你:

  1. 我构造了一个CommonTableExpression的实例，一个org.jooq.Table的变体。这意味着在许多情况下，我可以像对待标准 SQL 表一样对待这个对象。此块中的逻辑是一个窗口函数，用于计算数据库中车辆的中值价格。我将在本书的后面部分介绍窗口函数；你可以暂时忽略它。最重要的是，我已经将查询封装在这里，并将其命名为median_cte。

  2. 我在这里执行的中值计算是一个名为median的字段。

随着 CTE 对象的连接和准备就绪，我可以在实际的 jOOQ 查询中使用它，如下所示:

context.with(medianCte) (1)
                    .select(field("brand"), field("model"),field("trim"),field("price"),
concat(round((price.subtract(medianCte.field("median"))).divide(2),2).multiply(100),field("'%'"))
                        .as("relative to median price"))
                    .from(completeCarListing, medianCte) (2)
                    .fetchMany();

它给了我一个简单得多的主要查询:

  1. 我只是将我的 CTE 放入DSLContext上的with方法中。

    • 注意我对medianCte.field("median")的用法。出于各种意图和目的，CTE 将被视为WITH子句后的一张桌子。因此，我可以引用(或“取消引用”)我的 CTE 中可用的列，就像我引用任何 SQL 表或子查询一样。

  2. 然后我就可以像使用任何旧桌子一样使用 CTE 了。

Pop Quiz

:jOOQ 的父类是什么，它包含了可以组成一个完整 SQL 语句的所有子句和更小的部分？

a) { Query }

b) { QueryPart }

c) { Field }

Answer

:{ QueryPart }是父类，可用于表示 SQL 语句的每个部分。您可以用{ Query Part }的所有子元素编写任何类型的 SQL 语句

通过 DSL 与组

在 jOOQ 中对查询结果进行分组就像您到目前为止看到的任何其他方法一样简单。在没有介绍你们俩认识的情况下，我也一直在悄悄地使用groupBy从句。不再有了！我现在想找回

  • 所有轿车

    • 按品牌分组

  • 所有轿车的总和，不考虑品牌

在 MySQL 中，对此的查询如下所示:

select brand, count(*) "# of units"
from complete_car_listing
where brand = 'Sedan'
group by brand with rollup

Oracle 使用GROUP BY ROLLUP (...)语法。翻译成 jOOQ 也是同样的语法:

Result<Record2<Object, Integer>> results = context.select(field("brand"),count().as("units"))
                    .from(table("complete_car_listing"))
                    .groupBy(rollup(field("brand")))
            .fetch()

值得信赖的老版本org.jooq.DSL提供了我完成这个查询所需的所有分组函数——count和rollup函数就来自那里。菜单上还有其他分组功能(cube、groupingSets)。祝你好运！

有了 DSL

不管有没有GROUP BY子句，您都可以拥有和HAVING子句来限制组——在 SQL 和 jOOQ 中。为了将我的车辆计数列表限制为平均价格高于 20，000 美元的品牌，我将使用 jOOQ 查询，如下所示:

Result<Record2<Object, Integer>> results = context.select(field("brand"),count().as("units"))
                    .from(table("complete_car_listing"))
                    .groupBy(rollup(field("brand")))
                    .having(avg(field("price",BigDecimal.class)).gt(BigDecimal.valueOf(20000L)))
            .fetch()

就这样。没什么特别的。

通过 DSL 订购

使用 jOOQ 对查询结果进行排序也和您想象的一样简单。到目前为止，我已经在很多查询中使用它来排序我的汽车搜索结果；我只是将orderBy子句添加到我构建的流畅链中:

List<CompleteVehicleRecord> allVehicles = context.select()
      .from(table("complete_car_listing"))
      .orderBy(field("year").desc() ,two())
      .fetchInto(CompleteVehicleRecord.class);

就像我一直在做的那样，我将我的普通 SQL year列转换成一个Field的实例。然后，我调用Field实例上的desc方法，将其转换成一个OrderField——一个专用于对查询结果进行排序的Field类型。此外，我使用two方法将文字“2”传递给ORDER BY子句。这将另外按照结果集中的第二列对查询结果进行排序。还要注意，您可以向orderBy方法提供一个排序字段列表。

按案例排序

一种被低估的排序方法是能够有条件地对查询结果进行排序。如果你想到像 Reddit 这样的论坛上的“固定”帖子或博客上的“粘性”文章，这是实现这一点的一种方式。这是一种机制，它将确保结果集的特定行将基于特定标准定位在结果中。对于伊甸园的汽车商店，我希望能够永久列出“特色”的车辆，将在每个搜索结果页面的顶部。对于 SQL，这可能看起来像

SELECT *
FROM edens_car.complete_car_listing
ORDER BY CASE featured
            WHEN true then 0
         ELSE 1 END ASC

在 jOOQ 中表示它将类似于

List<CompleteVehicleRecord> allVehicles = context
                    .select(field(name("brand")), field("model"), field("price"))
                    .from(table("complete_car_listing"))
                    .orderBy(
                            case_(field("featured"))
                                    .when(true,0)
                                    .otherwise(1))
                    .fetchInto(CompleteVehicleRecord.class);

我在这里做的是

  1. 指定我希望首先列出weight设置为-1的车辆。

  2. 然后，我希望动态应用传入的任何其他任意排序标准。

这将保证标记为“特色”的车辆总是出现在任何搜索结果的顶部。对排序施加更多控制的另一种方法是使用我的排序标准的映射来手动确定特定行在整个查询结果中的位置:

HashMap<String, Integer> sortMap = new HashMap<>();
sortMap.put("Toyota",Integer.valueOf(0));
sortMap.put("Acura",Integer.valueOf(5));

前面的地图显示丰田应该在结果列表中第一；讴歌应该从列表中的位置 6 开始；加权的行2应该出现在第 4 行。然后，我可以像这样将映射传递给orderBy子句:

.select(field(name("brand")), field("model"), field("price"))
                    .from(table("complete_car_listing"))
                    .orderBy(field(name("brand"),String.class).sort(sortMap))
                    .fetchInto(CompleteVehicleRecord.class);

或者，我可以使用…

按 NULL 排序

ORDER BY子句的行为因数据库而异。Oracle 和 PostgreSQL 会将空值视为比其他值更大，因此当您使用ORDER BY weight ASC时，带有空值的行将最后显示。MySQL、SQL Server 和 SQLite 以相反的方式处理空值:当您ORDER BY ASC时，这些行将首先显示。如果您希望在 RDBMSes 之间得到一致的结果，那么明确您希望空值排序的方向正是您希望进行的那种有意识的编码。因此，考虑这个 SQL 查询:

SELECT *
FROM edens_car.complete_car_listing
ORDER BY color NULLS FIRST

现在，MySQL 不支持这种语法；PostgreSQL 和几个其他的。然而对于 MySQL，空值被认为是最小的。因此，当您按升序排序时，空值将首先出现。jOOQ 将透明地模拟这个函数，因此不管底层服务器是什么，它都是可用的。为了用orderBy DSL 在 jOOQ 中表示相同的结果，我将

List<CompleteVehicleRecord> allVehicles = context
                    .select(field(name("brand")), field("model"), field("price"))
                    .from(table("complete_car_listing"))
                    .orderBy(field(name("trim"),String.class).asc().nullsLast())
                    .fetchInto(CompleteVehicleRecord.class);
Easy peasy. On to the next!

对查询结果分页

有标准的 SQL OFFSET...FETCH子句来限制从查询中返回的条目数量并对结果进行分页。MySQL 的方言提供了执行大致相同功能的LIMIT...OFFSET子句。MySQL、H2、PostgreSQL、SQLite 和 HSQLDB 都支持LIMIT关键字。Oracle 和其他几个数据库属于OFFSET...FETCH阵营。jOOQ 将使两种语法之间的差异变得无关紧要。在 MySQL 中检索前 10 辆最贵汽车的查询如下所示:

SELECT *
FROM complete_vehicle_listing
ORDER BY price DESC
LIMIT 10

同样的查询将在 Oracle 中写成这样:

SELECT *
FROM complete_vehicle_listing
ORDER BY price DESC
FETCH NEXT 10 ROWS ONLY;

任何一个数据库都可以在 jOOQ 中无缝地获得正确的 SQL 解释，代码如下:

context.select()
      .from(table("complete_car_listing"))
      .orderBy(field("price").desc())
      .limit(10)
      .fetchInto(CompleteVehicleRecord.class);

我只需要这个方法。我可以添加WITH TIES SQL 子句，以确保在我的 top-N 查询中，与ORDER BY列具有相同值的行(即 tied)将出现在结果中。这意味着，当我在数据库中查询前 10 辆最贵的车辆时，如果车辆#11 与#10 在价格上捆绑，它将被包括在查询结果中，而不管是否超过限制条款。这是 jOOQ 中的样子:

            context.select(fieldList)
                    .from(table("complete_car_listing"))
                    .orderBy(field("price").desc())
                            .limit(10)
                            .withTies()
                            .fetchInto(CompleteVehicleRecord.class);
            logger.info(allVehicles.toString());

为了获得按价格降序排列的车辆列表并支持分页，我将在 jOOQ DSL 中引入offset子句:

<jooq offset>

该查询将检索一页的value结果，起点为offset。注意，无论使用哪种类型的 RDBMS，您都将使用limit函数——jOOQ 处理翻译。

Pop Quiz

哪个 jOOQ 类允许您构造条件WHERE子句？

Answer

DSL类，具有DSL#noCondition()功能。

插入语句

CRUD 中名副其实的“C”。用 jOOQ 向数据库中插入一辆新车，如下所示:

context.insertInto(
table("vehicle"),
             field("vehicle_manufacturer"),field("vehicle_curr_price"),field("vehicle_model_year"),field("vehicle_status"),field("vehicle_color"),field("vehicle_model_id"),field("vehicle_trim"),field("vehicle_style"),field("featured"))
             .values(4,BigDecimal.valueOf(46350.00), LocalDate.parse("2021-01-01").getYear(),"ACTIVE","BLUE",13,2,1,1)
             .execute();

insertInto节点提供了流畅的 API 来交付我们对 jOOQ 的期望。对于 insert API 的所有实例，第一个参数是一个Table；之后，您可以有选择地提供要插入的单个字段，就像标准 SQL INSERT语句一样。

除了标准的INSERT...VALUES语法，jOOQ 还提供了一些其他的风格，比如…

具有多行

我可以将多辆车插入数据库，如下所示:

context.insertInto(table("vehicle"),field("vehicle_manufacturer"),field("vehicle_curr_price"),field("vehicle_model_year"),field("vehicle_status"),field("vehicle_color"),field("vehicle_model_id"),field("vehicle_trim"),field("vehicle_style"),field("featured"))
                        .values(4,BigDecimal.valueOf(46350.00), LocalDate.parse("2021-01-01").getYear(),"ACTIVE","BLUE",13,2,1,1)
                        .values(9,BigDecimal.valueOf(83000.00), LocalDate.parse("2021-01-01").getYear(),"ACTIVE","GREY",20,9,1,1)
                        .values(9,BigDecimal.valueOf(77000.00), LocalDate.parse("2016-01-01").getYear(),"ACTIVE","WHITE",20,9,1,1)
                    .execute();

Caution

虽然多值插入是 ANSI 标准 SQL 规范的一部分，但并不是所有数据库都一致支持它。jOOQ 将为不兼容的数据库模拟这一点。即使这样，由于数据库服务器允许的最大数据包大小，您仍然可能碰壁。对于 MySQL，这是max_allowed_packet服务器参数。

使用序列

虽然 MySQL 为自动生成的索引提供了AUTO_INCREMENT函数，但是您仍然可以获得自定义序列，并以这种方式触发生成:

BigInteger nextVehicleManufacturerId = context.``nextval

jOOQ 为你生成的序列使用起来更有趣，所以请继续关注！

使用选择

我可以使用INSERT...SELECT标准 SQL 语法将行从一个表复制到另一个表中。对于我的用例，我将使用这个语法来归档已经在库存中放置了很长时间的车辆。使用行的年龄作为过滤条件，我决定将车辆从vehicles复制到vehicle_archive。jOOQ 中的样子:

context.insertInto(table("vehicle_archive"))
                        .select(DSL.selectFrom("vehicle").where("datediff(date(now()),created) < 365"))
                       .execute();

…就是这样。继续前进。

更新语句

是的，我也不小心做了一个没有WHERE子句的UPDATE...SET。我不想谈论我造成了多大的破坏。

img/500754_1_En_2_Figf_HTML.jpg

女士，如果你告诉任何人把所有 50 万行的状态栏设置为相同...

当我想更新库存中现有车辆的价格时，它看起来是这样的:

context.update(table("vehicle"))
                    .set(field("featured"),false)
                    .where(field("vehicle_id").eq(7))
                    .execute();

也很直白。现在，jOOQ 可以用Settings类上的setExecuteUpdateWithoutWhere方法在没有 where 子句的情况下保护你(也就是我)免受意外的UPDATE...。

//Non! Disallow updates without a where clause by throwing an exception
new Settings().setExecuteUpdateWithoutWhere(ExecuteWithoutWhere.THROW);

与ExecuteUpdateWithoutWhere枚举一起，您可以配置是否

  • 用IGNORE忽略条件

  • 用WARN记录一个警告

  • 使用DEBUG在调试时记录

  • 使用INFO在信息级别记录

  • 用THROW取消所有尝试

没有更多的烂摊子要清理！

删除语句

是时候继续前进了。我已经把卖不出去的车存档了。现在是时候将它们从库存中彻底清除了。

img/500754_1_En_2_Figg_HTML.jpg

你们中的一个很快就会消失。说再见

很简单，删除 DSL(因为，当然是):

context.deleteFrom(table("vehicle_archive"))
                    .where(field("vehicle_id").eq(7))
                    .execute();

简单，是吗？太好了。现在让我们试试…

元组语法

我可以用我的 delete 语句变得更好一点。您可能已经注意到，jOOQ 支持元组语法(也称为行值表达式),我们可以在其中进行整行比较:

UPDATE vehicle_archive
SET (vehicle_status,featured) = ("ARCHIVE",0)
WHERE (vehicle_status,featured) = ("ACTIVE",1)

基本上是硬编码，但是对于 SQL。对于这个查询，我要求查询删除所有车辆，除了与我指定的特定列组合相匹配的特定行或元组。通过这种方式，我更新了除这些特定行或元组之外的所有内容。

MySQL 不支持这个。是的，我也可以把它作为单独的条款写在WHERE条件中，但是这有什么意思呢？

为了用 jOOQ 的术语来复制它，我将

context.update(table("vehicle_archive"))
                    .set(row(field("featured"), field("vehicle_status")),
                            row(1,"UNARCHIVED"))
                    .where(row(field("vehicle_status"),field("featured")).eq(row("ACTIVE",0)))
                    .execute();

再见了那些车辆(除了那些我因为某些原因选择保存的)！

替代数据访问模式

到目前为止，我只展示了同步数据访问操作:

  • 用户请求启动 CRUD。

  • 调用线程等待数据从数据库返回。

  • 在同一个调用线程中完成了一些转换工作。

  • 然后将数据返回给调用者。

所有这些都发生在一个线程中。org.jooq.Result类是处理数据库查询结果的基本单元，包含从查询返回的所有结果。这样做的好处是不需要打开数据库连接来访问所有结果。不利的一面是，对于大型查询结果，您将会占用大量内存。单线程处理大量结果还有一个额外的缺点。 12

<glutton image>

但是还有其他的方式——懒人的方式、流光的方式和被动的方式。先说流媒体。

流式访问

jOOQ 为从数据库中流式传输数据提供了一些便利，从“流”这个词的任何意义上来说都是如此:

  • org.jooq.Result类扩展了java.util.List。因此，您可以访问java.util.Stream提供给您的所有特性，只需在查询后在您的Result实例上打开一个流。

  • 出于同样的目的，您可以方便地调用Result类上的fetchStream方法。

在我们进一步讨论之前，我应该明确一点:使用以stream为中心的 jOOQ 功能会在某种程度上改变操作模型。其中org.jooq.Result将把所有结果加载到内存中，并断开与打开的数据库的连接，fetchStream将维持打开的数据库连接。这意味着您现在需要记住在完成处理后关闭连接。那么，jOOQ 中的流代码是什么样子的呢？还记得我有多喜欢汽车事务吗？

            DSLContext context = DSL.using(connection, SQLDialect.MYSQL);
            try(final Stream<Record> records = context.select().from(table("complete_car_listing"))
                        .fetchSize(100) (1)
                        .fetchStream()){ (2)
                records.parallel().forEach(recordList -> {
                   //deal with records
                });
            }

与我之前运行相同查询的方式不同，我现在在一个try...with resources块中运行它。这意味着在我退出该块后，所有相关的底层资源都将自动关闭。在底层，jOOQ 使用一个org.jooq.Cursor来有效地处理来自数据库的结果。

  1. 我使用fetchSize设置来提示 Connector/J(MySQL 驱动程序),我希望我的查询结果逐行流动，而不是全部加载到内存中。是的，除此之外，jOOQ 自己也在尽最大努力做同样的事情。我特别强调了这一点，因为在这一点上，当您尝试使用游标进行流式处理时，不同的数据库会给出不同的行为。

    • 因为我使用的是 MySQL，所以我必须消费完与这个查询相关的所有结果， 13 在我用来提供结果的 JDBC 连接的实例上。如果不这样做，将会导致该连接对同一应用中的任何其他线程都没有用——这在连接池场景中是自找麻烦。

    • 与结果中的行相关联的任何锁都将被保持，直到所有行都被读取。

    • 因此，我希望尽可能快地获得查询结果。

  2. 然后我将流并行化，这样我可以使用几个线程来更快地运行结果。

Note

标准的 Java 流规则仍然适用。例如，一旦我退出try块，流就不再可访问；你也不能在流中向后滚动——一旦一个项目被消费，它就完成了。

这是处理数据的一种方式，但它仍然是相当同步的。还有什么？

惰性访问

如果以“懒”开头，我已经成功了一半。jOOQ 提供了fetchLazy方法作为一种真正的方法来正确地获取可管理的数据块。当您不需要将org.jooq.Result转换成流时，它是有用的。这一次，我需要亲自处理org.jooq.Cursor:

  1. 光标是一种资源，所以我仍然在一个try-with-resources块中打开它。

  2. 我像往常一样完成这些项目。

DSLContext context = DSL.using(connection, SQLDialect.MYSQL);
            try(final Cursor<Record> records = context.select().from(table("complete_car_listing")).fetchSize(100).fetchLazy()){ (1)
                    while(records.hasNext()){ (2)
                        CompleteVehicleRecord completeVehicleRecord = records.fetchNextInto(CompleteVehicleRecord.class);
                    }
            }

同样的警告也适用于fetchLazy流:Cursor保持一个开放的 JDBC 连接和PreparedStatement,所以不要保持它超过需要的时间。这和fetchStream是运行大型查询的最佳选择。

处理

它们是在数据库中执行的每个 SQL 操作的基本单元。是的，无论您是否明确地定义了事务，它们都会在数据库中发生。这一节是关于在 jOOQ 中操作时如何设置事务的。我将向您展示如何使用 jOOQ 来实现 ACID 保证。酸代表

  • A tomicity:当您将一个代码块指定为事务性代码块时，该代码块中的任何执行失败或异常都将导致该代码块中所有数据库操作语言(DML)执行的反转。这意味着所有的插入、更新或删除。

  • 一致性:意味着任何执行 DML 变更的事务性代码块都保证遵守底层数据源中定义的任何完整性规则。以便任何修改数据的尝试都考虑到唯一性、约束、外键-主键关系。

  • 解决方案:可以配置数据库事务来保护正在操作的数据免受多线程相关的损坏。需要防范的一些问题包括

    • 脏读:一个线程可以读取另一个线程正在写入的未提交数据

    • 不可重复读取:一个线程在一个时间窗口内读取同一行，每次都会得到不同的结果

    • 幻像读取:在同一事务的连续读取过程中，数据基本上消失了

    • 更新丢失:对同一行执行更新的两个事务(线程)破坏了数据，每个事务都不知道对方的操作

  • 可用性:保证已经写入数据库并被 RDBMS 确认的更改被持久化和可检索。

所以，那是酸。这在 jOOQ 中是如何工作的？没有 Spring、JTA 或任何其他具有专用事务管理的框架的 Plain jOOQ 将遵从标准的 JDBC 语义。这意味着手动定义事务边界，如下所示:

  1. 这条线标志着事务边界的开始。这里:

    • jOOQ 为底层的 JDBC 驱动程序设置了AUTO_COMMIT。

    • 它提供了一个org.jooq.TransactionalRunnable的实例(不，不是来自java.lang.Runnable的那个Runnable)。这是我的句柄，开始在我的事务边界中执行。

  2. 我的insert和update正常执行，但是没有被提交。这样，如果它们中的任何一个因为任何原因失败，整个代码块都会被展开，并显示导致失败的异常。

    • 注意我是如何将Configuration对象传递到嵌套方法中的。这对于确保这些方法中的数据库操作参与同一个事务边界是必要的。

  3. 此外，我可以嵌套事务，以便

    • 该事务块继承自外部事务块。

    • 此数据块内的故障会将操作回滚到此数据块之前的最后一个保存点。这样，如果我选择的话，剩下的操作可以继续。

  4. 如果在这之前所有事情都没有因异常而阻塞，那么就定义了另一个事务边界。然后，这将整个事务标记为完成。

context.transaction(configuration -> { (1)
                updateVehiclePrice(configuration);
                insertPriceChange(configuration);   (2)
                configuration.dsl().transaction(innerConfig ->{
                    //more work
                });
            //profit??    (4)
            });

这是默认 jOOQ 事务提供者的方式。您可以选择以 Spring 框架或其他方式提供自定义事务提供者；这将在本书的后面部分讲述。

Caution

这种事务处理方法只交付 ACID 保证的原子性组件。大多数 RDBMSes 将提供开箱即用的一致性和持久性，也许需要一些调整。为了防止更新丢失和其他与隔离相关的问题，您需要做更多的工作——仍然在 jOOQ 框架内。

带锁定

锁定是获得 ACID 保证的隔离部分的方法。具体来说，您可以

  • 支持表行的并发读取

  • 通过导致并发更新和删除失败来防止与隔离相关的失败

这在 jOOQ 中意味着，当两个事务(或线程)试图同时修改一行时，只有一个会成功。另一个将在尝试提交其更改时获得一个org.jooq.exception.DataChangedException。

支持锁定有不同的方法:

  • SELECT FOR UPDATE是一个 SQL 标准查询，它锁定行，为更新或删除操作做准备。jOOQ 将在执行实际的 DML 之前透明地运行这个查询。这就是所谓的悲观锁定。

  • 使用多版本并发控制(MVCC)，一种被称为乐观锁定的形式。只有当您使用 jOOQ 生成的代码时，jOOQ 才提供这种方法；本书后面会详细介绍这种方法。

MVCC 方法只适用于 jOOQ 生成的代码。对于普通 SQL，您可以像这样进行悲观锁定:

context.select(field("vehicle_curr_price"))
           .from(table("vehicle")
           .where(field("vehicle_id").eq(11))
           .forUpdate()
           .fetch();

前面代码片段中的forUpdate调用将获得基础表中受影响行的排他锁。这意味着没有其他数据库事务(或应用线程)可以对该行执行 DML。使用 MySQL，您甚至可以阻止任何其他线程读取同一行。支持这一点的底层机制因数据库而异。

现在，锁定在快乐路径场景中是非常好的。当一个锁由于应用错误或某一天的恶劣天气而没有被释放时，这种能力可能会成为问题。像软件工程中的任何锁定机制一样，您通常需要某种形式的故障保险。我有几个选择:

  1. wait方法让我为尝试获取行锁或保持现有锁指定超时。这样，我就不会无意中永远等待获取一个锁:

    context.select(field("vehicle_curr_price"))
           .from(table("vehicle")
           .where(field("vehicle_id").eq(11))
           .forUpdate().wait(3000)
           .fetch();

它接受以毫秒为单位的超时。MySQL、Postgres、Oracle 和 MSSQL 支持这种语法。默认行为是根本不等待锁。

  1. MySQL 14 和 Postgres 提供了一个forShare子句作为支持可共享锁的增强。这样，其他线程仍然可以读取同一行，而拥有锁的线程可以提交更改:
context.select(field("vehicle_curr_price"))
           .from(table("vehicle")
           .where(field("vehicle_id").eq(11))
           .forShare().wait(3000)
           .fetch();

forShare也支持wait标志。

  1. jOOQ 还支持带有… skipLocked的SKIP LOCKED选项:
context.select(field("vehicle_curr_price"))
           .from(table("vehicle")
           .where(field("vehicle_id").eq(11))
           .forShare().skipLocked().wait(3000)
           .fetch();

skipLocked将使事务忽略已经被另一个事务锁定的行或表。这里也有安全阀wait。

总的来说，您希望谨慎使用悲观锁定。如果您的事务没有被提交，那么您的调用线程就会挂在锁上，我们都会有一段糟糕的时间。

配置

让我们尝试对 jOOQ 运行时进行一些更高级的控制。您可以将一些有趣的附件插入到运行时中，以便更好地控制 jOOQ 的工作方式。看看这些。

连接管理

到目前为止，我一直使用一个单独的 JDBC 连接作为我的示例:

try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/edens_car?user=test&password=thisisabadpassword")) {
    //business things
}

在生产级部署中，您需要更多的东西……production-y . jOOQ 提供了一个org.jooq.impl.DefaultConnectionProvider来处理 jOOQ 的默认用法——一个您提供给上下文的单一连接，如我在前面的代码片段中所示。在生产强度应用中，您很可能要处理一个连接池和一个javax.sql.DataSource实例。怎么办？

进入org.jooq.ConnectionProvider界面。这是一个扩展点，您可以实现它来对如何处理到数据库的连接承担更多的责任。ConnectionProvider提供了两种方法:

public class CustomConnectionProvider implements ConnectionProvider {
    DataSource dataSource;

    @Override
    public Connection acquire() throws DataAccessException {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void release(Connection connection) throws DataAccessException {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
}

jOOQ 运行时将调用acquire来获取语句执行的 JDBC 连接。当执行完成时，它将调用release来处理连接。这里，我感兴趣的是跟踪连接获取和处置之间的间隔。这是一种观察查询执行时间的粗略方法。处置机制取决于底层配置规定了什么。当您使用连接池时，连接实际上不会被关闭；它会被放回游泳池供以后再利用。

jOOQ 还提供了DataSourceConnectionProvider作为对javax.sql.DataSource的直接支持。例如，在一个 Spring Boot 应用中，这可能是您将要使用的。要使用我的连接提供程序:

Configuration config = new DefaultConfiguration();
CustomConnectionProvider customConnectionProvider = new CustomConnectionProvider();
//set it directly on the Configuration
config.set(customConnectionProvider);
//Alternative: pass it to the DSLContext
DSLContext context = DSL.using(new CustomConnectionProvider(), SQLDialect.MYSQL,settings);

就这么简单:将它传递给Configuration的一个实例，该实例又进入DSLContext；或者，直接将其传递给 DSLContext。除非你做了不该做的事情，DataSourceConnectionProvider应该是线程安全的。这意味着您可以设计您的应用，在应用中重用相同的DataSourceConnectionProvider，插入到相同的连接池中。您还可以将数据源直接传递到您的配置中，跳过所有其他麻烦。

Caution

当您实现一个定制的ConnectionProvider时，您将无法使用默认 jOOQ 实现提供的一些方便的方法。例如，commit和rollback都不在列表中。通过这个特性，你可以心照不宣地将一些事情掌握在自己手中。

模式、目录和多租户部署

数据库支持以下内容的一些组合:

  • 架构:相关表、视图、存储过程和函数的集合。它是你在数据库中创建的所有“东西”的袋子。并不是所有的数据库都这么看——例如，MySQL 认为数据库和其中的一切都是模式。Oracle 和 SQL Server 认为模式独立于数据库服务器的其他内容。

  • 目录 15 :目录是模式的更高层次的抽象。因此，一个目录可以包含多个模式(如果您喜欢，也可以是多个模式)。MySQL 只考虑目录，所以没有模式——目录是相关表的集合，等等。

这两种机制可用于在同一数据库服务器实例上支持单个应用的多个独立实例。这意味着，对于应用的不同客户机，它们可以共享相同的数据库服务器实例，它们的表是隔离的和独立的。这就是书呆子们所说的多租户。

鉴于我正在使用 MySQL 数据库作为我的在线汽车销售网站，我必须使用目录作为鉴别器来支持多租户。当您使用 jOOQ 生成的代码时，默认情况下，jOOQ 将限定所有组件(表、序列、视图等)。)与模式/目录名进行比较，以确保我们将所有查询路由到正确的模式。您可以使用以下设置禁用它:

new Settings()
  .withRenderCatalog(false)
  .withRenderSchema(false);

这样，只需用正确的目录作为 SQL 查询元素的前缀，就可以控制运行时使用的模式或目录。

如果你需要在运行时这样做呢？在开发时，您有一个“主”或“开发”模式。在部署时，您可能希望将“master”或“dev”转换为生产模式，甚至是运行时指定的动态模式。用 jOOQ 术语来说是什么样的呢？

Settings settings = new Settings()
                .withRenderMapping(new RenderMapping()
                .withDefaultSchema(“default_schema”)
                .withDefaultCatalog(“default_catalog)
                .withSchemata(new MappedSchema().withInput("master").withOutput(schemaInEffect))
                .withCatalogs(new MappedCatalog().withInput("master").withOutput(schemaInEffect)));
        ...
        DefaultConfiguration configuration = new DefaultConfiguration();
        configuration.setSQLDialect(SQLDialect.MYSQL);
        configuration.setConnection(connection)
        configuration.setSettings(settings);
        DSLContext context = DSL.using(configuration);

这个Settings片段演示了MappedSchema和MappedCatalog类的用法。

  • 有了这两个类，我可以指示 jOOQ 运行时将一个输入模式(在本例中为master)转换成一个不同的运行时模式。

  • withDefaultSchema和withDefaultCatalog方法让我为所有查询设置一个默认模式。这些模式不会被用作任何查询组件的前缀——这是默认的，所以没有必要在每个查询中指定它们。

  • 然后，我将封闭的Settings实例放入一个Configuration实例中。然后可以将该配置输入到DSLContext中。这意味着我的查询组件(表、序列、存储过程等。)将以一个org.jooq.Catalog或org.jooq.Schema类为前缀，指定我想在运行时处理哪个目录或模式。

另一种动态配置模式的方法是用

context.setSchema(selectedSchema).execute();

//或者

context.setCatalog(selectedCatalog).execute();

其中selectedCatalog或selectedSchema可以是一个org.jooq.Name，一个普通字符串，或者分别是org.jooq.Catalog和org.jooq.Schema的实例。对于查询中还没有模式前缀的对象，这将在运行时设置活动模式或目录。

为胜利查询零件！

查询管理

jOOQ 运行时有很多工具可以控制 SQL 语句的处理方式。大饱眼福。

  • 用withRenderFormatted漂亮地打印 SQL:

    new Settings().setRenderFormatted(true);

  • 控制标识符的呈现方式。根据标识符是否被引用，不同的数据库会提供一些额外的好处。

    .withRenderQuotedNames(RenderQuotedNames.ALWAYS);

RenderQuotedNames枚举提供了一些选项来控制这种行为:

  • 优化使用IN比较参数的 SQL 语句的性能。因为大多数数据库缓存PreparedStatement的方式，所以IN比较使用固定大小的条目列表是很重要的。这意味着这个声明

    SELECT * from complete_vehicle_listing where vehicle_manufacturer in (?)

    and this statement

    SELECT * from complete_vehicle_listing where vehicle_manufacturer in (?,?,?,?)

    will be treated as different statements by the database, even though the only difference is that the number of parameters in the IN list is different. In a high traffic RDBMS, this can yield shockingly poor performance. jOOQ’s solution is called “IN list padding.” With this feature, jOOQ will pad the query parameter with a constant multiplier. This will help increase the rate at which the database can cache PreparedStatements, yielding better performance. It’s a simple Settings operation:

    new Settings().withInListPadding(true) //defaults to false
             .withInListPadBase(4) //starting count to pad with

  • 设置 JDBC 参数，例如queryTimeout和maxRows:

    Settings settings = new Settings().withQueryTimeout(5) //in seconds
      .withMaxRows(1000)

查看Settings javadoc，获得更多有趣的参数。

查询生命周期集成

我将使用 jOOQ 的org.jooq.ExecuteListener键进入 jOOQ 运行时的查询执行。它提供了在多达 20 个事件期间截取流程的机会。jOOQ 附带了这两个监听器，其中包括:

  • org.jooq.tools.StopWatchListener是一个监听器，它将帮助您跟踪查询执行过程中关键事件的执行时间。

  • org.jooq.tools.LoggerListener在语句执行生命周期的关键事件期间打印日志语句。

非常适合引入交叉功能，如日志记录(正如您已经看到的)、异常处理，甚至是安全特性。

public class QueryIntrospectionListener extends DefaultExecuteListener { (1)

    final Logger logger = LoggerFactory.getLogger(QueryIntrospectionListener.class);

    @Override
    public void fetchStart(ExecuteContext ctx) { (2)
        logger.info("Executing: {}",ctx.sql()); (a)
        logger.info("Query type: {}",ctx.type()); (b)
        //ctx.query().getBindValues() to retrieve bind parameters for the query
    }

    @Override
    public void exception(ExecuteContext ctx) { (3)
        if(Objects.nonNull(ctx.sqlException())){
            //handle exception
        }
    }

}

首先，我应该提到，我是上下文对象 16 模式的忠实粉丝，所有我喜欢的框架都倾向于这种模式。下面是前面片段的情况:

  1. 扩展org.jooq.impl.DefaultExecuteListener是让你自己的监听器开始工作的推荐方法。该类包含许多您可以覆盖的生命周期方法。覆盖这些方法提供了对 jOOQ 运行时的特权访问，因此您可以将自己的逻辑和设计注入到整个查询执行过程中。

  2. 我选择了覆盖fetchStart方法。这意味着我可以在 jOOQ API 的fetch操作执行之前介入。这个方法(以及DefaultExecuteListener中的所有其他方法)由一个org.jooq.ExecuteContext实例提供。这个漂亮的上下文对象包含了您需要的关于当前正在执行的查询的所有上下文信息。我可以接触到

    1. 正在执行的原始 SQL

    2. 正在执行的查询类型:

    3. 实际的Query对象并检查参数。

  3. 在我被覆盖的exception方法中，我可以获得查询处理过程中发生的任何异常，并对它做任何我想做的事情。

定义了我的定制监听器后，下面是我如何将它插入到DSLContext中:

Configuration configuration = new DefaultConfiguration();
            configuration.set(connection)
                    .set(SQLDialect.MYSQL)
                    .set(new QueryIntrospectionListener());
            DSLContext context = DSL.using(configuration);

能够使用我的自定义ExecutionListener

  1. 我将使用set方法向我的org.jooq.Configuration实例提供该侦听器的实例。

  2. 然后，我使用该配置获得一个DSLContext，瞧！

能够将定制逻辑交织到查询的执行中，丰富的上下文为定制提供了很多机会。

伙计，我喜欢上下文对象。

*

三、使用 jOOQ

现在，在我的下一个演示中，我们将更深入地研究 jOOQ 工具箱。jOOQ 工具箱中有很多便利——当你想到它的时候，jOOQ 就是关于便利的。我在上一章中做的所有手工 SQL，手工制作的实体类和查询结果的解析；它可以变得非常简单，更重要的是类型安全。这是享受 jOOQ 的第二个关键:类型安全。总的来说，本章是对 jOOQ 提供便利和类型安全的特性的一次漫游。首先，我们来看看 jOOQ 的代码生成能力。

生成代码

这大概是 jOOQ 概念的一半:能够安全地引用列、表、序列，实际上是 SQL 查询的任何部分。除其他优势外，您会发现

  • 当 SQL 直接从您的数据库中导出时，不正确的 SQL 将不再是一件需要担心的事情；无需猜测。

  • 许多样板代码将被自动处理。根据经验，我可以告诉您，不必手工键入另一个实体类或数据访问对象(DAO)是一件好事。

  • 在使用数据库组件时，您可以利用代码完成、提示和“查找用法”等功能，这一事实显著改善了您的 IDE 体验。

  • 您的数据驱动的单元和集成测试更能保证代码的行为和质量。在打包模型中，您的数据访问组件(模式、实体等。)是与业务逻辑分开管理的，所以您可以构建应用来独立地验证对模式所做的任何更新，而无需经历一个完整的部署周期。稍后将详细介绍这种技术。

总的来说，类型安全生成的数据库工件是它的所在，所以让我们去那里吧！

jOOQ 代码生成工具

此时我们感兴趣的是使用 jOOQ 生成 Java 类来表示 Eden Auto Mart 数据库内容的方法。有三种方法可以解决这个问题:

  1. 编程:jOOQ 提供了一个简单的 API，您可以用它从数据库模式中生成类。

  2. 命令行:您也可以使用命令行界面生成工件。

  3. 构建工具:jOOQ 附带了 Maven 和 Gradle 工具 1 ，您可以使用它们来运行代码生成操作。

前面三种方法的结果是相同的:包中的 java 类，表示您的数据库目录或模式。它们还共享通用的配置元素。基本上，代码生成器的配置被表示为一个 XML 文档。生成 jOOQ 代码的三种模式在生命周期中的某个时刻总是以这个 XML 实体的形式结束，主要是作为一个 JAXB 注释的类。因此，从查看 XML 配置文档的样子开始是有意义的。

为代码生成配置 jOOQ

那么，配置是什么样子的？等等:这是相当多的 XML:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration>
    <!-- Configure the database connection here -->
    <jdbc>
        <driver>com.mysql.cj.jdbc.Driver</driver>
        <url>jdbc:mysql://localhost/edens_car</url>
        <user>eden_admin</user>
        <password>_*thisisabadpassword*_</password>
    </jdbc>
    <generator>
        <name>org.jooq.codegen.JavaGenerator</name>
        <database>
            <name>org.jooq.meta.mysql.MySQLDatabase</name>
            <inputSchema>edens_car</inputSchema>
            <includeTables>true</includeTables>
            <includes>.*</includes>
        </database>
        <generate>
            <javaTimeTypes>true</javaTimeTypes>
             <daos>true</daos>
            <pojos>true</pojos>
            <pojosAsJavaRecordClasses>true</pojosAsJavaRecordClasses>
           <pojosEqualsAndHashCode>true</pojosEqualsAndHashCode>
            <pojosToString>true</pojosToString>
        </generate>
        <target>
            <packageName>com.apress.jooq.generated</packageName>
            <directory>C:\Users\SIGINT-X\eclipse-workspace\jooq-demo\src\main\java</directory>
        </target>
    </generator>
</configuration>

好吧，你不需要现在就接受。请随意复制粘贴这里的内容。让我告诉你最重要的部分。在顶级<configuration>标签(及其相关的模式文档链接)之后，混合了可选和强制元素，控制 jOOQ 代码生成的行为:

  1. 数据库连接:<jdbc/>元素定义了代码生成器如何访问数据库。不能从你不能访问的数据库中生成类。

  2. 生成器语义:<generator/>元素是我为代码生成器提供额外上下文的地方。该节点是一些其他更高级关注点的容器。在这里，我定义了

    1. 我更喜欢使用java.time包中更新的时间类。这样我就可以用LocalDateTime等代替java.sql.Time等。

    2. 使用<dao>true</dao>，jOOQ 将为每个表生成数据访问对象 2 (DAO)。这意味着我又有了一个方便的组件来为整个对象运行类型安全查询。

    3. 对于我的 web 应用中实体的实际传输，我不想发送实际的 jOOQ 记录或Tables——那不是整洁的代码。不，我想要的是一个 POJO——一个没有任何框架代码的普通 Java 对象——jOOQ 的或者其他的。进入<pojo/>配置以生成 POJOs。

    4. 帮助我设置存储我生成的类的位置和包结构。

    5. <includes/>让我通过名字指定我想要在生成中包含什么工件。这就是用<includeTables/>说“我想在生成的代码中包含所有的表”和用<includes>说“我想在代码生成中包含这些特定的表”的区别。

    6. 我感兴趣的代码类型是 Java。其他选项包括 Scala 和 Kotlin。元素控制将要发生的代码生成的类型。

    7. 用<database/>我感兴趣的数据库方言。

    8. 数据库中的模式，包含我要从中生成类的工件。我还可以通过在这里嵌套一个包含我想要生成的每个<schema/>的<schemata>元素来提供多个模式。

    9. 标签允许我指定我想从数据库中生成什么类型的组件。没有这些，你很可能会得到一堆你并不真正需要的系统组件和其他东西。

配置 jOOQ 代码生成器有更多的功能和灵活性；我强烈建议您查看官方手册，了解高级选项。现在，让我们继续前进！

根据前面的配置，我应该能够使用一个可用的生成器选项从现有的数据库模式中生成代码。我有什么选择？

用 Maven 生成代码

jOOQ 提供了jooq-codegen-maven Maven 插件来执行您的代码生成业务。您使用的插件的groupId将取决于您正在使用的 jOOQ 的发行版(商业版或开源版)，以及您正在运行的 Java 版本:

  • 开源版—org.jooq

  • 商业版—org.jooq.pro；org.jooq.pro-java-8对于 Java 8 的支持

  • 商业版免费试用–org.jooq.trial

因为我对学校来说太酷了，而且对自己来说做得很好， 3 我将弹出我的商业 Maven 插件配置，如下所示:

<maven demo>

以下是我在前面的代码片段中所做的工作:

  1. 我将根据标准的 Maven 插件用法来定义 jOOQ 插件的用法。

  2. 我指定我希望插件在 Maven 构建生命周期的generate-sources阶段启动。

  3. 然后我宣布一个generate目标。这是我将用来触发 jOOQ 插件的代码生成过程的 Maven 目标。

  4. 提供skip.jooq.generation属性允许我在构建时动态地启用或禁用代码生成。

  5. configurationFile指向我的 XML 配置文件的位置，用于前面看到的代码生成。我还可以选择在我的 Maven POM.xml (Maven 的项目对象模型)文件中包含该配置文件的全部内容。这是可能的，但你可能不应该这样做，因为

    1. 代码生成配置文件可能会比 POM 有更多的变化。因此，最好将这两者清楚地分开，以尽量减少 POM 整体的混乱。

    2. 将代码生成配置从 POM 中分离出来，为配置文件的版本化提供了机会。

    3. 它使您的 POM 文件更短，因此可读性更好。

    4. 它支持可重用性。考虑一下:当您有一个用于代码生成的独立 XML 配置文件时，您可以从 Maven、命令行甚至以编程方式引用该文件。如果你把你的构建系统改成 Gradle，你不需要重写一堆配置！

有了所有这些，我就可以运行下面的 Maven 命令来生成我感兴趣的源文件:

mvn package

我可以在 IDE 中看到所有新的类和包。

img/500754_1_En_3_Figa_HTML.jpg

图为:成功

从命令行生成代码

如果 Maven 或其他构建工具不适合您，您可以直接从终端或命令行界面运行生成器。给定相同的 XML 配置文件，我可以从终端窗口执行以下命令:

java -cp  jooq-3.15.1.jar;jooq-meta-3.15.1.jar;jooq-codegen-3.15.1.jar;reactive-streams-1.0.2.jar;mysql-connector-java-8.0.24.jar;jaxb-api-2.3.1.jar ;r2dbc-spi-0.9.0.M1.jar; org.jooq.codegen.GenerationTool jooq-configuration.xml

是的，这正是你可能在想的。我正在运行代码生成器，就像运行普通的 java 代码一样。 4 使用与 JDK 标准捆绑的java工具，我运行 jOOQ 附带的org.jooq.codegen.GenerationTool类。此外:

  1. 我使用-cp标志来配置我的类路径。这个标志允许我为GenerationTool的业务需求提供 JAR 依赖关系。

    1. 注意包含了 JAXB 依赖关系 jaxb-api-2.3.1.jar 。这对于 JDK 11 及更高版本的环境是必要的。由于 Java 热衷于模块化，默认情况下排除了 JAXB 依赖性，所以我们现在需要明确地在需要时包含它。如果没有这个额外的 JAR，当从命令行运行代码生成时，您可能会得到各种各样的ClassNotFoundException。好玩。

  2. 我还提供了 XML 配置文件的位置。

我还可以选择用这些精细的环境变量来配置代码生成器的核心需求；享受:

  • -Djooq.codegen.configurationFile定义 XML 配置文件的位置

  • -Djooq.codegen.jdbc.driver配置将用于连接数据库的驱动程序类

  • -Djooq.codegen.jdbc.url配置连接数据库的 URL

  • -Djooq.codegen.jdbc.username和-Djooq.codegen.jdbc.password分别定义数据库连接的用户名和密码

  • -Djooq.codegen.logging设置代码生成过程的日志级别；标准测井级别，如DEBUG、WARN、INFO等。应用

  • -Djooq.codegen.skip完全禁用代码生成

环境变量选项是为 jOOQ 项目设置默认值的好方法。它们可以被 XML 文件、Maven 甚至编程代码生成选项中定义的内容覆盖。

以编程方式生成代码

所有选项中最强大的选项[雷霆一击/雷击]！您可以使用 jOOQ 提供的 API 以编程方式生成代码。事情大概是这样的:

  org.jooq.meta.jaxb.Configuration generatorConfiguration = JAXB.unmarshal(new File("src/main/resources/jooq-configuration.xml"), org.jooq.meta.jaxb.Configuration.class);
        GenerationTool.generate(generatorConfiguration);

这里，我只是重用了我之前定义的 XML 配置，这次包装成了一个org.jooq.meta.jaxb.Configuration的实例。香草 JDK 的 JAXB API 加载 XML 文件并将其解组到一个Configuration对象。然后我使用GenerationTool#generate静态方法来运行发电机。

Note

JAXB 模块已经从 JDK 核心中分离出来；您需要手动将它作为一个依赖项包含在您的 POM 中，以便能够运行前面的示例。

对于更细粒度的控制，我可以像这样完全去掉 XML 文件:

org.jooq.meta.jaxb.Configuration generatorConfiguration = new org.jooq.meta.jaxb.Configuration()
                .withJdbc(new Jdbc()
                        .withDriver("com.mysql.cj.jdbc.Driver")
                        .withUrl("jdbc:mysql://localhost/edens_car")
                        .withUsername("root").withPassword("admin"))
                .withGenerator(new Generator()
                        .withName("org.jooq.codegen.JavaGenerator")
                        .withDatabase(new Database()
                                .withName("org.jooq.meta.mysql.MySQLDatabase")
                                .withInputSchema("edens_car")
                                .withIncludeTables(true)
                                .withIncludes(".*"))
                        .withGenerate(new Generate()
                                .withJavaTimeTypes(true)
                                .withJavaBeansGettersAndSetters(true)
                                .withDaos(true)
                                .withPojos(true)
                                           .withPojosEqualsAndHashCode(true)
                                .withPojosToString(true))
                        .withTarget(new Target()
                                .withClean(true)
                                .withDirectory("src/main/java")
                                .withEncoding(StandardCharsets.UTF_8.toString())
                                .withPackageName("com.apress.jooq.generated")
                        ))
                .withLogging(Logging.DEBUG)
                .withOnError(OnError.LOG);
        GenerationTool.generate(generatorConfiguration);

这只是 XML 文件内容的忠实复制，以获得更大的灵活性和更大的马力——结果是一样的。您可以将这两种方法结合起来，在 XML 文件中预置一些值；那么一些值可以通过编程动态提供。

编程式代码生成是在 XML 文件或命令行参数无法满足需求的场景中利用 jOOQ 的绝佳机会，比如集成测试(稍后将详细介绍)。另一个机会是在稍微非常规的场景中使用 jOOQ，比如动态生成数据库连接。

Pop Quiz

jOOQ 配置模式的哪个元素允许您配置哪种类型的代码(Java、Scala 等)。)应该生成？

Answer

元素定义了 jOOQ 代码生成的输出类型。使用org.jooq.codegen.JavaGenerator生成 Java 代码。

代码生成的结果

无论您选择哪种代码生成方法，结果在很大程度上都是相同的。看看他们:

桌子

模式或目录中的每个表将主要产生以下结果:

  1. 扩展org.jooq.impl.TableImpl的类，它本身是org.jooq.Table的实现。我的 Eden Auto 数据库看起来有点像这样:
public class Vehicle extends TableImpl<VehicleRecord> {
    private static final long serialVersionUID = 1L;

    /**
     * The reference instance of <code>edens_car.vehicle</code>
     */
    public static final Vehicle VEHICLE = new Vehicle();

    /**
     * The class holding records for this type
     */
    @Override
    public Class<VehicleRecord> getRecordType() {
        return VehicleRecord.class;
    }
    /**
     * The column <code>edens_car.vehicle.vehicle_id</code>.
     */
    public final TableField<VehicleRecord, Long> VEHICLE_ID = createField(DSL.name("vehicle_id"), SQLDataType.BIGINT.nullable(false).identity(true), this, "");
...
}

正是这些类可以用来构造类型安全的 SQL 查询；这取代了我之前用来将原始 SQL 转换成 jOOQ 类型的DSL#table函数。这些类不可能出错。

  1. 扩展org.jooq.impl.*RecordImpl的类，它本身是org.jooq.Record的实现。听起来熟悉吗？Record是你从数据库查询中得到的结果。可能会产生两种常见的结果:

    1. UpdatableRecordImpl是当底层表有一个主键并且 jOOQ 在代码生成期间可以访问主键时得到的结果。

    2. TableRecordImpl是当底层表没有主键，jOOQ 不能访问主键数据，或者它甚至不是一个真正的表(例如，一个数据库视图)时，您将得到的结果。

      因此，使用您的*RecordImpl来迭代查询结果；UpdatableRecordImpl专门对表执行 DML 操作。

  2. Plain Old Java Objects (POJOs) also come out of the box automatically, also representing rows in your database tables. Typically, a generated POJO class will extend Serializable. Here’s what one looks like for Eden Auto:

    public class Vehicle implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long          vehicleId;
    private Long          vehicleManufacturer;
    private BigDecimal    vehicleCurrPrice;
    private LocalDate     vehicleModelYear;
    private String        vehicleStatus;
    private String        vehicleColor;
    private Long          vehicleModelId;
    private Long          vehicleTrim;
    private Long          vehicleStyle;
    private Byte          featured;
    private LocalDateTime created;

    public Vehicle() {}
  ...
}

    正如我前面提到的，这些作为数据传输对象(DTO)或值对象是很方便的，您可以使用它们将数据转移到应用中。更好的是:您可以让 Bean 验证 5 规范，如@NotNull和@Size从表的列信息中生成。相当整洁！

  3. 数据访问对象(Dao)类似于 Spring 的各种Repository方法。它们做的和听起来一样:帮助你从你的表中访问强类型数据。Dao 将帮助您查询它们各自的表，以检索所需的记录。默认情况下，只为带有主键的表生成 Dao。这意味着视图不会自动生成 Dao。

jOOQ 允许您在代码生成过程中对表主键进行大量控制。如果您想添加主键信息以及类似数据库视图的信息，可以使用这个特性来手动通知 jOOQ:

<database>
            <name>org.jooq.meta.mysql.MySQLDatabase</name>
            ...
            <syntheticPrimaryKeys>edens_car.complete_car_listing.vehicle_id</syntheticPrimaryKeys>
            ...
</database>

因为视图不是真正的表，所以大多数数据库不会提供与实际表相同的主键信息。因此，我必须为我感兴趣的数据库视图配置键列的路径。这个特性被称为合成主键。有了这个配置，complete_car_listing数据库里面就会产生一个 DAO。这里的缺点是它需要一些组合

  • 对列名进行硬编码

  • 主键列的一致命名约定

  • 需要根据主键列的名称进行验证的正则表达式

但是等等；还有更多:

全局工件

更方便的引入:jOOQ 还可以生成大多数横切组件作为高级“全局”类。这些将作为下列类定义的静态成员生成:

  1. Keys.java将包含静态字段，引用在每个表的基础上定义的所有主键、外键和唯一键。当您需要使用 SQL 连接构建类型安全查询时，这些将会派上用场。

  2. Sequences.java将给出在你的数据库模式中定义的所有序列。当您出于某种原因想要手动生成一个键值时，可以使用这些方法。

  3. Tables。java 将包含您的模式中定义的所有表，这在您构建查询等时非常有用。

jOOQ 代码生成过程中还有其他静态生成的组件，其中一些超出了本书的范围。我强烈建议查看官方文档，了解更多好东西。

Pop Quiz

:什么 jOOQ 方法会产生 SQL { CASE }子句？

a) { choose }

b) { when }

c) { case }

Answer

:{ choose }和{ when }是用 jOOQ 开始{ CASE }语句的有效方式

向生成的代码中添加自定义代码

如果你喜欢(像我一样)，你可能会对在所有(或部分)生成的代码中添加手工编织的代码块感兴趣。例如，一些公司环境可能对在所有代码中添加商标和版权信息感兴趣。要实现这一点，您需要一个JavaGenerator的实现。下面是向所有类文件添加一个头注释的样子:

import org.jooq.codegen.JavaGenerator;
import org.jooq.codegen.JavaWriter;
import org.jooq.meta.TableDefinition;

public class CopyrightGenerator extends JavaGenerator {

    protected void printClassJavadoc(JavaWriter out, String comment) { (1)
        out.println("/** This is proprietary code of Initech co */");
    }
    protected void generateRecordClassFooter(TableDefinition table, JavaWriter out){ (2)
        out.println();
        out.tab(1).println("public static String getInitTechWarning(){");
        out.tab(2).println("return \"This is proprietary code of Initech co\";");
        out.tab(1).println("}");
        out.println();
    }
}

img/500754_1_En_3_Figb_HTML.png

我告诉过你我很喜欢

这是这个类的一个非常简单的用法——几乎没有什么是你不能使用JavaGenerator工具重写或添加到生成的代码中的。

  1. 允许我将任意 Javadoc 内容添加到类声明的顶部(在导入之后)。

  2. generateRecordClassFooter让我将任意代码附加到一个Record类的末尾——基本上是任何表或视图。您可能认识到这里的风险:这个工具处理普通的类型不安全字符串(我知道这很讽刺)，但是有一些琐碎的方法可以确保您不会意外地包含无法编译的代码片段。

默认的JavaGenerator提供了一系列令人印象深刻的方法，可以覆盖和更改生成代码的任何部分。尽情享受吧！

使用生成的代码

当您拥有直接从数据库模式编织的代码时，类型安全本身就是一个好处。但不止于此。看，当 jOOQ 是您的数据访问代码的来源时，会产生一些独特的好处。

带有生成代码的 CRUD

为普通 CRUD 操作生成的代码工作得更好。我可以从vehicle表中检索强类型保证，如下所示:

import static com.apress.jooq.generated.EdensCar.EDENS_CAR;
import static com.apress.jooq.generated.Tables.VEHICLE; (1)
...
public static void selectWithGeneratedCode() throws SQLException {
       ...
       DSLContext context = DSL.using(connection, SQLDialect.MYSQL);
       List<Vehicle> vehicles = context.select(EDENS_CAR.VEHICLE.VEHICLE_ID, EDENS_CAR.VEHICLE.VEHICLE_COLOR, EDENS_CAR.VEHICLE.VEHICLE_CURR_PRICE) (2)
                    .from(VEHICLE)      (3)
                    .where(VEHICLE.VEHICLE_MANUFACTURER.eq(val(2L))) (4)
                    .orderBy(VEHICLE.VEHICLE_MODEL_YEAR)
                    .fetchInto(Vehicle.class); (5)
            logger.info(vehicles.toString());
        }
    }

我有目的地将一些风格和概念混合到前面的代码片段中，以说明您使用生成的工件所获得的灵活性。

使用生成的工件:

  1. 生成的模式(EDENS_CAR)和生成的表(VEHICLE)类的静态导入意味着我可以在以后需要它们的任何地方使用它们各自的内容。

  2. 我可以在select语句–Schema.Table.Column中使用列的完全限定路径。

  3. 我可以完全删除模式，只使用 generated com.apress.jooq.generated.Tables类提供的普通生成的表引用。

  4. 为过滤器表达式提供参数轻而易举，因为

    1. 我不必处理像“？”这样的占位符或者记住参数的顺序。我可以在需要的字段上直接指定参数。

    2. 在 jOOQ 语句执行期间，使用val函数将参数值绑定到正确的列；我得到的 SQL 注入保护是一个不错的奖励。然而，这并不总是必要的——jOOQ 在大多数情况下都会这样做。

  5. 最后，我可以将查询结果提取到从Vehicle表生成的 POJO 中。

目前为止一切顺利。我也可以直接在vehicle表的UpdatableRecord实现中做fetch:

Result<VehicleRecord> vehicle = context.fetch(VEHICLE, VEHICLE.VEHICLE_MANUFACTURER.eq(val(2, Long.TYPE)));

这是我所追求的简洁的代码。我已经去掉了许多样板文件；我只是提供了表类和我的过滤标准。除了简洁之外，这种类型的获取还有一个导航好处，我将很快介绍它。

对于对vehicle表的插入，我可以立即用returningResult方法检索与该插入相关联的主键:

long execute = context.insertInto(VEHICLE, VEHICLE.VEHICLE_MANUFACTURER, VEHICLE.VEHICLE_CURR_PRICE, VEHICLE.VEHICLE_MODEL_YEAR, VEHICLE.VEHICLE_STATUS, VEHICLE.VEHICLE_COLOR, VEHICLE.VEHICLE_MODEL_ID, VEHICLE.VEHICLE_TRIM, VEHICLE.VEHICLE_STYLE)
                    .values(4L, BigDecimal.valueOf(46350.00), LocalDate.parse("2021-01-01"), "ACTIVE", "BLUE", 13L, 2L, 1L)
                    .returningResult(VEHICLE.VEHICLE_ID)
                    .execute();

returningResult方法允许您从新插入的行中返回字段，作为插入响应的一部分。理想的用法是返回基础数据库支持的为插入生成的主键。对于其他非键字段，如果底层数据库本身不支持INSERT...RETURNING，jOOQ 可能会执行第二次 SQL 执行来检索请求的数据。

总之，表名或列名错误的风险被消除了。结合流畅的 jOOQ DSL，确保您的 SQL 总是语法正确:您拥有防弹的数据库查询。

来自 UpdatableRecord 的增强

通过生成的UpdatableRecord,您可以获得 CRUD 操作的一些强大优势。这个组件中我最喜欢的一点是如何导航记录的关系: 7

   DSLContext context = DSL.using(connection, SQLDialect.MYSQL);
            VehicleRecord vehicleRecord = context.fetchOne(VEHICLE, VEHICLE.VEHICLE_ID.eq(7L));
            VehicleManufacturerRecord vehicleManufacturerRecord = vehicleRecord.fetchParent(Keys.VEH_MANUFACTURER_ID); (1)
            VehicleModelRecord vehicleModelRecord = vehicleRecord.fetchParent(Keys.VEH_MODEL_ID); (1a)
            logger.info("Vehicle Manufacturer: {}",vehicleManufacturerRecord.getManufacturerName());
            logger.info("Vehicle Model Record: {}",vehicleModelRecord.getVehicleModelName());
            Result<VehicleModelRecord> vehicleModelRecords = vehicleManufacturerRecord.fetchChildren(Keys.MANUFACTURER_ID); (2)
            vehicleModelRecords.forEach(vmr ->{
                logger.info("{} {}",vehicleManufacturerRecord.getManufacturerName(),vmr.getVehicleModelName());
            } );

检索特定车辆后，我可以

  1. 通过使用vehicle和vehicle_manufacturer之间的外键关系来检索制造商记录。这通过使用从Keys类中适当生成的外键来实现。将它传递给fetchParent方法，我就被排序了。

    1. 我使用相同的机制来检索由外键关联到vehicle表的车辆型号记录。

  2. 我可以使用fetchChildren沿着家谱向下走，传入连接vehicle_manufacturer和vehicle_model_id的外键。

DML 呢？每个UpdatableRecordImpl都是一个活动记录——这意味着您不需要任何额外的组件来对检索到的记录执行数据操作:

  1. 我可以从DSLContext创建一个空的VehicleModelRecord对象。数据库中尚未创建任何记录。

  2. 在设置必要之后，我可以在对象上使用store方法来持久化记录。

  3. 我可以立即检索新插入记录的id。

  4. 我可以在方便的时候继续用同一张唱片打电话给store。

  5. 我将使用refresh方法获取我正在处理的记录的最新副本。

  6. 当我完成时，我可以调用delete方法来删除记录。

VehicleModelRecord vehicleModelRecord = context.newRecord(VEHICLE_MODEL); (1)
vehicleModelRecord.setVehicleManId(2L);
vehicleModelRecord.setVehicleModelName(“Tacoma”);
vehicleModelRecord.setVehicleStyleId(3L);
vehicleModelRecord.store(); (2)    //record saved
Long vehicleModelId = vehicleModelRecord.getVehicleManId(); (3)
vehicleModelRecord.setVehicleModelName(“Tacoma XD”);
vehicleModelRecord.store();    (4)
vehicleModelRecord.refresh(); (5)
vehicleModelRecord.delete();    (6)

Tip

我的UpdatableRecordImpl是连接到数据库连接的，但是很慢。这意味着我的VehicleRecord实例包含了对Configuration对象的引用，该对象引用了底层的 JDBC 连接池。所以，VehicleRecord不是线程安全的。好消息是，我不必担心连接对象的连接池不够用。

格式化

Result类提供了format*方法，允许您将查询结果转换成不同的格式，如

  • 战斗支援车

  • 超文本标记语言

  • 可扩展置标语言

  • 数据

因此，给定一个检索到的VehicleModelRecord的Result，我可以生成如下格式的输出:

  Result<VehicleModelRecord> vehicleModelRecords = vehicleManufacturerRecord.fetchChildren(Keys.MANUFACTURER_ID);
  vehicleModelRecords.formatJSON();

很可爱吧？我应该注意，这不是刚刚生成的代码的特性；您也可以从普通的 SQL jOOQ 执行中获得有益的东西。Record类也提供了这个特性，因此您可以打印整个结果集或单个的Record。

乐观锁定

为了启用 jOOQ 的乐观锁定机制，我像这样配置我的DSLContext:

Settings settings = new Settings().withExecuteWithOptimisticLocking(true);
DSLContext context = DSL.using(connection, SQLDialect.MYSQL, settings);

用 jOOQ 生成代码打开了这个新的，支持事务管理的乐观锁定。对于“手动”SQL，jOOQ 需要使用SELECT...FOR UPDATE语句来保护对行的并发访问——这意味着对数据库的两次访问。有了生成的代码，jOOQ 可以使用recordVersionFields或recordTimestampFields配置属性:

</database>
...
    <recordVersionFields>edens_car\.*\.version</recordVersionFields>
...
</database>
Or programmatically
...
.withGenerator(new Generator()
               .withDatabase(new Database()
               .withName("org.jooq.meta.mysql.MySQLDatabase")
               .withRecordVersionFields("edens_car\\.*\\.version")
...

使用一个正则表达式，我规定了在edens_car模式中有一个version列的每个表都应该符合乐观锁定的条件。这可以通过编程和 XML 配置来实现。它们的作用是一样的:当两个或多个事务试图在同一行上工作时，jOOQ 将在受影响的行上寻找已配置的列(version)。当然，这意味着您需要在表中添加一个版本或时间戳列来支持这一功能。在该行的副本中，无论哪个事务在recordVersionFields或recordTimestampFields中具有最新的值，都“获胜”当事务尝试对其数据进行store、update或delete处理时，具有该行的旧副本的事务将获得一个org.jooq.exception.DataChangedException。如果您曾经使用过 Hibernate/Spring Data JPA，您可能对这种机制很熟悉。

高级数据库操作

现在我们已经看到了 jOOQ(闪电和雷声效果)生成的代码的威力！)，让我们挑战一下极限。现在加入我，我们一起来看看…

连接

我对构建一个查询感兴趣，该查询将呈现一个车辆列表，给我一个类似这样的 UI 表示。

img/500754_1_En_3_Figc_HTML.jpg

每辆车，我想显示

  • 车辆制造商名称

  • 车辆模型

  • 模型修剪

  • 现价

  • 车辆颜色

  • 车辆年款

所有这些信息都需要来自不同的表。这样的查询看起来像什么？

 SELECT
        `v`.`vehicle_id` AS `vehicle_id`,
        `v_man`.`manufacturer_name` AS `brand`,
        `v_mod`.`vehicle_model_name` AS `model`,
        `v_trim`.`trim_name` AS `trim`,
        `v`.`vehicle_curr_price` AS `price`,
        `v`.`vehicle_color` AS `color`,
        `v_style`.`vehicle_style_name` AS `style`,
        `v`.`vehicle_model_year` AS `year`,
        `v`.`featured` AS `featured`
FROM  ((((`vehicle` `v`
        JOIN `vehicle_manufacturer` `v_man` ON ((`v`.`vehicle_manufacturer` = `v_man`.`manufacturer_id`)))
        JOIN `vehicle_model` `v_mod` ON ((`v_mod`.`vehicle_model_id` = `v`.`vehicle_model_id`)))
        JOIN `vehicle_trim` `v_trim` ON ((`v_trim`.`trim_id` = `v`.`vehicle_trim`)))
        JOIN `vehicle_style` `v_style` ON ((`v_style`.`vehicle_style_id` = `v`.`vehicle_style`)))

在这个查询中，我已经连接了下面的表:

  • vehicle

  • vehicle_manufacturer

  • vehicle_model

  • vehicle_trim

  • vehicle_style

这在 jOOQ 里是什么样子的？首先，我将把我需要的列组成一个可移植的包:

List<Field<?>> fields = Arrays.asList(
                    VEHICLE.VEHICLE_ID,
                    VEHICLE_MANUFACTURER.MANUFACTURER_NAME.as("brand"),
                    VEHICLE_MODEL.VEHICLE_MODEL_NAME.as("model"),
                    VEHICLE_TRIM.TRIM_NAME.as("trim"),
                    VEHICLE.VEHICLE_CURR_PRICE.as("price"),
                    VEHICLE.VEHICLE_COLOR.as("color"),
                    VEHICLE_STYLE.VEHICLE_STYLE_NAME.as("style"),
                    VEHICLE.VEHICLE_MODEL_YEAR.as("year"),
                    VEHICLE.FEATURED);

我将所有需要的字段放入一个整洁的包中，主要是为了可读性。但是这里有一个缺点，因为我通过使用Field<?>牺牲了类型信息。在某些场景中，jOOQ 可能会不同意这样做，因为它不能提供自己喜欢的类型安全保证。注意使用了as方法来设置列的别名。接下来是实际的查询。jOOQ 提供了对所有主要连接的支持，以及表达连接的灵活性。在这里，我希望构造一个内部连接。这里有一个方法来完成这个:

Result<Record> results = context.select(fields).from(VEHICLE)
.join(VEHICLE_MANUFACTURER).on(VEHICLE.VEHICLE_MANUFACTURER.eq(VEHICLE_MANUFACTURER.MANUFACTURER_ID)) (1)
                    .join(VEHICLE_MODEL).on(VEHICLE.VEHICLE_MODEL_ID.eq(VEHICLE_MODEL.VEHICLE_MODEL_ID))
.join(VEHICLE_TRIM).onKey() (2)
.join(VEHICLE_STYLE).on(VEHICLE.VEHICLE_STYLE.eq(VEHICLE_STYLE.VEHICLE_STYLE_ID))
.fetch();

所以，加入vehicle、vehicle_manufacturer、vehicle_model和vehicle_trim给了我想要的所有信息。以下是最新消息:

  1. 从我的第一个表开始，我使用join方法连接到下一个表。on方法帮助我定义我想要在其上构建关系的键。

  2. onKey是on的另一种变体。onKey是 jOOQ 独有的增强功能，它允许我跳过必须输入关系的关键列的部分。jOOQ 将通过定位表之间明确的外键-主键关系来透明地生成一个on SQL 子句。

    • onKey特性只在生成的代码上可用——jOOQ 需要确定底层表的结构。不幸的是，您的手动 SQL 诗歌不行。

    • 有一个变量可以用来指定要使用的列:onKey(VEHICLE.VEHICLE_TRIM)。

这就产生了标准的内部连接:两个或多个表之间的关系，其中键值出现在所有被引用的表中。如果您像我一样坚持清晰，您可能希望明确指定它是一个内部连接，如下所示:

context.select(fields).from(VEHICLE).innerJoin(VEHICLE_MANUFACTURER).on(VEHICLE.VEHICLE_MANUFACTURER.eq(VEHICLE_MANUFACTURER.MANUFACTURER_ID))

jOOQ 支持这种连接以及

  1. 与crossJoin交叉连接

  2. 用leftJoin左连接

  3. 用rightJoin右连接

  4. 与fullJoin完全连接

  5. 分区外部连接partitionBy–仅限 Oracle

…以及其他连接组合。

Caution

jOOQ 有一个 bug 8 ，在某些场景中，当执行与ON或USING的连接时，非独特的列会导致抛出InvalidResultException。一种情况是连接中的多个表中出现了相同的列名。要解决这个问题，请对与连接相关联的列使用 SQL 别名。或者，您可以对连接列使用完全限定的(schema.table.column)命名样式。

非 SQL 分组

考虑这样一个场景:我想得到与前一部分相同的结果(车辆名称、型号、制造商、装饰、颜色等)。)，但是按制造商对结果进行分组，这样我就可以得到如下所示的Map:

Map<String, List<VehicleRecord>> vendorMapping

SQL 查询可能是这样的:

SELECT
        `v`.`vehicle_id` AS `vehicle_id`,
        `v_man`.`manufacturer_name` AS `brand`,
        `v_mod`.`vehicle_model_name` AS `model`,
        `v_trim`.`trim_name` AS `trim`,
        `v`.`vehicle_curr_price` AS `price`,
        `v`.`vehicle_color` AS `color`,
        `v_style`.`vehicle_style_name` AS `style`,
        `v`.`vehicle_model_year` AS `year`,
        `v`.`featured` AS `featured`
FROM  ((((`vehicle` `v`
        JOIN `vehicle_manufacturer` `v_man` ON ((`v`.`vehicle_manufacturer` = `v_man`.`manufacturer_id`)))
        JOIN `vehicle_model` `v_mod` ON ((`v_mod`.`vehicle_model_id` = `v`.`vehicle_model_id`)))
        JOIN `vehicle_trim` `v_trim` ON ((`v_trim`.`trim_id` = `v`.`vehicle_trim`)))
<picture confused look caption: huh?>

那是什么？这与连接部分的查询完全相同？那里没有任何声明吗？为什么我要问你问题，其实我是在自言自语？朋友，这就是fetchGroups系列方法的魔力所在。

Map<String, Result<Record>> vehiclesGroupedByBrand = context.select(fields).from(VEHICLE)           .join(VEHICLE_MANUFACTURER).on(VEHICLE.VEHICLE_MANUFACTURER.eq(VEHICLE_MANUFACTURER.MANUFACTURER_ID))  .join(VEHICLE_MODEL).on(VEHICLE.VEHICLE_MODEL_ID.eq(VEHICLE_MODEL.VEHICLE_MODEL_ID))
.join(VEHICLE_TRIM).onKey()
.join(VEHICLE_STYLE).on(VEHICLE.VEHICLE_STYLE.eq(VEHICLE_STYLE.VEHICLE_STYLE_ID))
.fetchGroups(VEHICLE_MANUFACTURER.MANUFACTURER_NAME.as("brand"));
 vehiclesGroupedByBrand.forEach((manufacturer,vehicles) ->{
        logger.info("Available {} vehicles: \n {}",manufacturer,vehicles);
  });

我仍然可以像平常一样构造我的连接查询，但是使用fetchGroups，我可以通过 select 语句中的一个列的别名对我的结果进行分组和收集。这是 jOOQ 给我带来的另一个便利。如果没有这个工具，我将不得不在复杂的 SQL 语句或查询输出的手动组合之间进行选择，以获得相同的结果。

有许多版本的fetchGroups提供了极好的映射控制。例如，我可以按 POJO 对结果进行分组:

Map<VehicleManufacturer, List<CompleteCarListing>> vehiclesGroupedByBrand = context
                    .select(VEHICLE_MANUFACTURER.fields())
                    .select(fields) (1)
                    .from(VEHICLE)
.join(VEHICLE_MANUFACTURER).on(VEHICLE.VEHICLE_MANUFACTURER.eq(VEHICLE_MANUFACTURER.MANUFACTURER_ID))         .join(VEHICLE_MODEL).on(VEHICLE.VEHICLE_MODEL_ID.eq(VEHICLE_MODEL.VEHICLE_MODEL_ID))
.join(VEHICLE_TRIM).onKey()
.join(VEHICLE_STYLE).on(VEHICLE.VEHICLE_STYLE.eq(VEHICLE_STYLE.VEHICLE_STYLE_ID))
       .fetchGroups(keyRecord -> { (2)
               return keyRecord.into(VEHICLE_MANUFACTURER).into(VehicleManufacturer.class); (a)
             }, valueRecord -> {
                        return valueRecord.into(COMPLETE_CAR_LISTING).into(CompleteCarListing.class); (b)
       });
logger.info("{}",vehiclesGroupedByBrand);

这基本上是相同的连接查询，只是添加了一些特殊的调料:

  1. 我堆叠了两个 jOOQ select子句，这样这两个语句中的字段在查询的后面部分就可以用了。我调用了VEHICLE_MANUFACTURER表上的fields方法来轻松加载该表中所有可用的字段。fields变量是我在本书的这一部分一直使用的手工制作的org.jooq.Field列表。

    1. 查询的其余部分包含与我到目前为止用来演示的相同的连接。

  2. 当我到达 jOOQ 查询的fetchGroups子句时，上下文已经包含了将查询结果映射到 POJOs 所需的所有字段。注意，这些是生成的相同的 POJOs jOOQ。这个fetchGroups的变体允许我提供org.jooq.RecordMapper的实例来构建

    1. 通过将第一条记录关联到VEHICLE_MANUFACTURER表中，并依次关联到相应的VehicleManufacturer记录中，得到映射的键。

    2. 通过将第二个提供的记录关联到COMPLETE_CAR_LISTING“表”(它实际上是一个视图)中得到的映射值；这又被映射到适当的 POJO。

这个 jOOQ 查询生成的 SQL 查询是什么样子的？

select
`vehicle_manufacturer`.`manufacturer_id`,
 `vehicle_manufacturer`.`manufacturer_name`,
.`vehicle_manufacturer`.`status`,
`vehicle_manufacturer`.`version`,
`vehicle`.`vehicle_id`,
`vehicle_manufacturer`.`manufacturer_name` as `brand`,
`vehicle_model`.`vehicle_model_name` as `model`,
`vehicle_trim`.`trim_name` as `trim`,
`vehicle`.`vehicle_curr_price` as `price`,
`vehicle`.`vehicle_color` as `color`,
`vehicle_style`.`vehicle_style_name` as `style`,
`vehicle`.`vehicle_model_year` as `year`,
`vehicle`.`featured`
from `vehicle`
join `vehicle_manufacturer` on `vehicle`.`vehicle_manufacturer` = `vehicle_manufacturer`.`manufacturer_id`
join `vehicle_model` on `vehicle`.`vehicle_model_id` = `vehicle_model`.`vehicle_model_id`
join `vehicle_trim` on `vehicle_trim`.`vehicle_manufacturer_id` = `vehicle_manufacturer`.`manufacturer_id`
join `vehicle_style` on `vehicle`.`vehicle_style` = `vehicle_style`.`vehicle_style_id`

如您所见，堆叠我的select只是将所有可用的列添加到最终的SELECT语句中。也没有GROUP BY条款，不管是含蓄的还是其他的。从数据库返回行后，在内存中完成分组。结果是什么样的？

VehicleManufacturer (1, Lexus, ACTIVE, null)=[CompleteCarListing (1, Lexus, ES 350, BASE, 35000.0000, RED, Car, 2010-01-01, 0), CompleteCarListing (2, Lexus, ES 350, BASE, 49000.0000, GREY, Car, 2017-01-01, 0)],
VehicleManufacturer (4, Acura, ACTIVE, null)=CompleteCarListing (4, Acura, MDX, SPORT, 50000.0000, BLUE, Car, 2018-01-01, 0), CompleteCarListing (4, Acura, MDX, BASE, 50000.0000, BLUE, Car, 2018-01-01, 0)

有趣的事实:fetchGroups、fetchMap和intoGroup——三个非 SQL 分组函数——将保持查询结果的顺序。因此，如果您选择orderBy，那么分组之间的顺序保持不变。

Note

不要忘记在 POJOs 上生成equals和hashCode方法(如果没有生成，就自己实现它们)。Map数据结构需要hashCode和equals来惟一地标识它的每个元素。

当您需要在一个大的结果集上批量进行这种分组时，您需要恢复到fetchStream。这样，您可以像这样利用 JDK 流和并行化功能:

Map<VehicleManufacturer, List<CompleteCarListing>> vehiclesGroupedByBrand = context
                    .select(VEHICLE_MANUFACTURER.fields())
                    .select(fields) (1)
                    .from(VEHICLE)
                    ...
                              .join(VEHICLE_STYLE).on(VEHICLE.VEHICLE_STYLE.eq(VEHICLE_STYLE.VEHICLE_STYLE_ID))
                    .fetchSize(100) (1)
                    .fetchLazy() (2)
                    .collect( (3)
                            Collectors.groupingBy( (3a)
                                  record -> record.into(VehicleManufacturer.class),   (i) (Collectors.mapping(record ->
                                  record.into(CompleteCarListing.class),Collectors.toList())) (ii)
                    );

将操作链分解成fetchSize方法标志着懒惰业务的开始:

  1. 设置我希望光标一次检索的最大行数。不设置此项可能会导致整个结果集被加载到内存中。

  2. fetchLazy正式开始使用一个org.jooq.Cursor来有效地(惰性地)传输结果。

  3. 当我偷懒的时候，fetchGroups就不再在菜单上了。我将不得不把这个小组掌握在自己手中。collect方法接受一个java.util.stream.Collectors。从这一点，你也可以去parallel，因为我们现在在 JDK 溪流 API 领域。

    1. 生成密钥对象。

    2. 生成值对象并将组收集到数据结构中。便捷的Collectors.toList()功能帮助我实现了这一点。

    3. Collectors.groupingBy [9 将接受功能帮助

总之，我现在可以在不牺牲性能的情况下在内存中对大型数据集进行分组。是的，是的，这有点违背“SQL 知道最好”的原则， 10 但是为了可读性，这是复杂的窗口函数和非常低效的手工处理之间的一个折中。

批量操作

我需要从 Eden Auto 数据库中插入和/或导出一千辆汽车。我在 jOOQ 中有什么选择？首先，我可以更好地利用我的数据库连接:

DSLContext context = DSL.using(connection, SQLDialect.MYSQL);
            context.batched(batchedConnectionConfig -> {
               insertVehicle(batchedConnectionConfig);
               insertVehicleModel(batchedConnectionConfig);
               updateVehiclePrice(batchedConnectionConfig):
               //other inserts
            });

在前面的代码片段中，我组合了多个动态生成的 insert 语句来一次性执行:

  • DSLContext上的batched方法会将相同的语句添加到队列中。这些语句正在为 JDBC 批处理机制做准备，以便在一次数据库访问中执行。

  • 注意，我将batchedConnectionConfig传递给查询执行方法。DML 方法将需要使用这个配置，而不是原来的DSLContext对象。

  • 它们仍然作为独立的 DML 语句执行，因此每个语句都产生自己独立的INSERT、UPDATE或DELETE。当 jOOQ 在将这些语句发送到 DBMS 执行之前尽可能长时间地延迟它们的执行时，优势就来了。这就是 jOOQ API 中所谓的批量连接。

Note

当您试图从插入中检索结果(例如，生成的键)时，批处理连接不起作用。因此，如果您启用了Settings# returnIdentityOnUpdatableRecord，对您的UpdatableRecord上的store的调用将会立即执行，而不是等待批处理。

我可以用下面的Settings片段配置批量大小:

new Settings().setBatchSize(20);

这限制了一次性发送到我的数据库服务器的数据大小:最小化了网络连接或数据库本身不堪重负的风险；您需要调整这个配置来满足您的操作需求。

显式批处理

除了我前面演示的批处理连接之外，jOOQ 还为您期望的操作提供了方便的批处理方法:

  • batch

  • batchInsert

  • batchUpdate

  • batchDelete

  • batchStore

  • batchMerge

有了这些，我可以获得对批处理语义的更多控制，而不是等待BatchConnection隐式地做这件事。下面是batchStore的行动:

List<VehicleRecord> vehicleRecords = new ArrayList<>();
            //populate list of records to insert
context.batchStore(vehicleRecords).execute();

使用任何其他的batchXXX方法都一样简单。 11 你可以提供普通的 SQL，jOOQ DSL 语句，或者整个实体。

还有产生相同效果的batch模式:

context.batch(
                    context.insertInto(VEHICLE, VEHICLE.VEHICLE_MANUFACTURER, VEHICLE.VEHICLE_CURR_PRICE, VEHICLE.VEHICLE_MODEL_YEAR, VEHICLE.VEHICLE_STATUS, VEHICLE.VEHICLE_COLOR, VEHICLE.VEHICLE_MODEL_ID, VEHICLE.VEHICLE_TRIM, VEHICLE.VEHICLE_STYLE, VEHICLE.FEATURED)
                            .values((Long) null, (BigDecimal) null, null, null, null, (Long) null, (Long) null, (Long) null, (Byte) null))
                    .bind(4L, BigDecimal.valueOf(46350.00), null, "ACTIVE", "BLUE", 13L, 2L, 1L, Byte.valueOf("0"))
                    .bind(9L, BigDecimal.valueOf(83000.00),null, "ACTIVE", "GREY", 9L, 7L, 1L, Byte.valueOf("0"))
                    .bind(9L, BigDecimal.valueOf(77000.00),null, "ACTIVE", null, 9L, 7L, 1L, Byte.valueOf("0"))
                    .execute();
            }

batch方法允许我通过值绑定的方式批量执行 insert 语句。看，我可以使用多值插入来执行批处理，而不是单独的INSERT语句。唯一的规定是

  • 拥有一个 stub values语句作为“默认”值的提供者。这里，我在所有可用的槽中使用了空值。

  • 然后我将使用bind方法来设置我想要插入的实际值。

接下来，jOOQ 将一次性执行对数据库的插入，没有batchedConnection使用的延迟。

batchInsert和batchUpdate方法通常也会做您希望它们做的事情。他们都与TableRecord和UpdatableRecord一起工作，但是有一个问题。batchUpdate方法将只批处理相同的 SQL 语句。因此，当这三个VehicleRecords为batchInsert做准备时，结果可能不是你所期望的:

VehicleRecord vehicleRecord1 = context.newRecord(VEHICLE);
VehicleRecord vehicleRecord2 = context.newRecord(VEHICLE);
VehicleRecord vehicleRecord3 = context.newRecord(VEHICLE);
vehicleRecord1.setVehicleColor(null);
vehicleRecord2.setVehicleColor("GREY");
vehicleRecord3.setOptions(3L);
context.batchInsert(Arrays.asList(vehicleRecord1,vehicleRecord2,vehicleRecord3)).execute();

在前面的代码片段中，同一实体Vehicle的不同列有不同的空值和实际值组合。这样做的效果是，调用batchInsert或batchStore得到的 SQL 将生成功能上独立的INSERT语句。因此，jOOQ 将而不是批量更新。相反，它会单独执行每一个。在真实的批处理场景中，您已经对成百上千个包含不同组合的缺失/空字段的更新进行了排队，您将会得到一个令人不快的惊喜:

<unpleasant surprise image>

需要澄清的是，这不是 jOOQ 的问题。出于本书范围之外的一些原因，大多数数据库服务器(和 JDBC)不会像您预期的那样处理INSERT和UPDATE语句中的空值。为了确保 jOOQ 始终如一地处理我的批量插入和更新，我将像这样设置changed的值:

vehicleRecord1.changed(true);

changed标志是只有UpdatableRecord才有的属性。它向 jOOQ 运行时发出信号，表明这个特定实体上的一些字段已经被更改。因此，jOOQ 能够优化每个批处理项生成的INSERT或UPDATE语句。

Tip

UpdatableRecord提供修改实例的前一个值。在对象上调用original方法来获取修改前的上一个值。

批量导入

是的，你可以从你的数据库中吸入或呼出一堆数据。

img/500754_1_En_3_Figd_HTML.jpg

著名批量进口商

那看起来像什么？假设我有一个包含如下行的 CSV:

vehicle_brand,vehicle_price,model_year,status,color,model_id,vehicle_trim, style, featured
1,35000.0000,2010-01-01,ACTIVE,RED,1,1,1,0,2021-07-05 13:22:11.0,"","","",""
1,49000.0000,2017-01-01,ACTIVE,GREY,1,1,1,0,2021-07-05 13:22:11.0,"","","",""
1,36000.0000,2018-01-01,ACTIVE,BLUE,1,1,1,0,2021-07-05 13:22:11.0,"","","",""
4,50000.0000,2018-01-01,ACTIVE,BLUE,13,2,1,0,2021-07-05 13:22:11.0,"","","",""

比方说，我如何将 5000 个这样的 CSV 行放入我的数据库？

简单:使用 jOOQ 加载器 API。观察:

context.transaction(txn -> {  (1)
context.loadInto(VEHICLE)   (2)
                    .bulkAfter(50)         (3)
                    .batchAfter(10)       (4)
                    .commitAfter(2)      (5)
                    .loadCSV(csvString) (6)
                    .fields(VEHICLE.fields())
                    .ignoreRows(1)
                    .separator(',')
                    .nullString("")
                    .execute();
      }
);

这是细目分类:

  1. 我需要在一个事务块中执行批量装载，这将禁用autocommit。

  2. 使用Loader对象指定我想要导入 CSV 数据的表格。

  3. bulkAfter将配置发送到数据库的插入中包含的行项目数量的大小。在这里，我要求每个有效载荷包含 50 个INSERT...VALUES语句。

    1. bulkAll也是一个一次性将整个 CSV 发送到数据库的选项。小心使用，并在数据库服务器上进行调整。

  4. batchAfter配置通过网络一次发送到数据库的单个INSERT语句的数量。在这里，我要求一次发送十份声明。结合bulkAfter，该配置可以理解为“将 50 条VALUES语句添加到一条INSERT...VALUES语句中；然后一次性发送 10 条INSERT...VALUES语句到服务器。”所以总的来说，使用这些指令的批处理执行最多包含 50 行 x 10 条语句=总共 500 行。

    1. batchNone将单独执行每个INSERT。

  5. commitAfter将仅在达到设定的批次数量后提交我的插入。

    1. commitAll也是一种选择。在数据库服务器端，所有通过网络发送的 insert 语句都将在一个大型事务中提交。确保您的数据库可以支持事务块大小。

  6. 告诉 jOOQ 我想加载 CSV 格式——JSON 是另一个选项。

    1. 我定义了 CSV 文件中的列到数据库表中的列的映射。

    2. 我让 jOOQ 跳过 CSV 中的第一行，因为那是标题行。

    3. 我的 CSV 中“列”的分隔符用separator设置。

    4. 如何将 CSV 列标记为空？遇到这个值时，jOOQ 会用NULL替换任何“空白”CSV 列的插入。

…然后execute！

在撰写本文时，CSV 和 JSON 是 Loader API 唯一支持的文件格式。除了平面文件格式之外，我还可以用 Loader API 中的loadArray或loadRecords方法直接从内存中加载我的数据——分别加载数组或 jOOQ Record。太棒了。

高级查询语法

即使没有 jOOQ 生成的代码和类型安全，也有许多强大而方便的 SQL 特性可以使用 jOOQify。观察…

合并和向上插入

如何根据插入是否与现有数据冲突来有条件地插入或更新表中的数据？

进入花式插队双胞胎:合并和上插。两者都有助于将插入和更新合并到一个 SQL 语句中。不，“upsert”不是一个真正的单词，只是 update+insert 的组合词。MERGE语句是 SQL 的标准部分，受 Oracle、SQL Server、DB2 和 Sybase 等支持。MySQL 不支持MERGE功能，但它确实提供了一种替代方案。被称为INSERT...ON DUPLICATE KEY的语句，它的工作方式就像合并到支持

  • 向表中插入行。

  • 如果表中已经存在该行(结果出现了重复键错误)，则更新现有记录。

在 MySQL 中，我的 upsert 如下所示:

INSERT INTO vehicle_model(vehicle_model_name, vehicle_style_id, vehicle_man_id)
VALUES('ES 350', 2, 1)
ON DUPLICATE KEY UPDATE vehicle_style_id = 2, vehicle_man_id = 1

在 jOOQ 中，我可以这样写同样的查询:

context.insertInto(VEHICLE_MODEL,VEHICLE_MODEL.VEHICLE_MODEL_NAME,VEHICLE_MODEL.VEHICLE_MAN_ID, VEHICLE_MODEL.VEHICLE_STYLE_ID)
                    .values("ES 350",2L, 1L)
                    .onDuplicateKeyUpdate()
                    .set(VEHICLE_MODEL.VEHICLE_STYLE_ID,2L)
                    .set(VEHICLE_MODEL.VEHICLE_MAN_ID,1L)
                    .execute();

onDuplicateKeyUpdate方法允许我为任何由于数据已经存在而失败的插入尝试定义要更新的列。我仍然可以选择直接忽略任何重复的车辆，尝试用onDuplicateKeyIgnore插入。该指令的 SQL 等价物是 MySQL 独有的INSERT...IGNORE SQL 命令、 12 。对于 PostgreSQL，jOOQ 支持更新的INSERT...ON CONFLICT语句来达到同样的效果。

那么，如果你没有运行 MySQL 数据库呢？嗯，你很幸运，朋友会救你的。jOOQ 会透明地将onDuplicateKeyUpdate和onDuplicateKeyIgnore的任何用法转换成MERGE语句，其中后台数据库不是 MySQL:

context.mergeInto(VEHICLE_MODEL)        (1)
                    .using(selectOne())                    (2)
                    .on(VEHICLE_MODEL.VEHICLE_MODEL_NAME.eq("ES 350"))      (3)
                    .whenMatchedThenUpdate()     (4)
                    .set(VEHICLE_MODEL.VEHICLE_STYLE_ID,3L)
                    .set(VEHICLE_MODEL.VEHICLE_MAN_ID,1L)      .whenNotMatchedThenInsert(VEHICLE_MODEL.VEHICLE_MODEL_NAME,VEHICLE_MODEL.VEHICLE_MAN_ID,VEHICLE_MODEL.VEHICLE_STYLE_ID)     (5)
                    .values("ES 350",2L, 1L)
                    .execute();

这是一对一的翻译

  1. 它从流畅链中的mergeInto节点开始。

  2. 标准的MERGE语法需要数据的源表和目的表。在这个代码片段中，我的数据是手动构建的，而不是来自另一个数据库表。因此，我不会提供源表。这就是我在DSLContext上使用selectOne()方法的原因。这是一个方便的特性(许多特性中的一个),它从伪表(如 Oracle 的DUAL表)生成查询。对于任何其他场景，您应该在这个位置提供一个实际的表。

    1. 您也可以在using子句中使用Record和子查询。它们只需要被转换成表格。为了 jOOQ 查询的目的，table函数将几乎任何东西转换成表格，例如:

      List<VehicleModelRecord> vehicleModelRecords = ...
...
           context.mergeInto(VEHICLE_MODEL)
                     .using(table(vehicleModelRecords))
                      ...

  3. 然后我定义了一个条件，我想用它来确定一行是否重复。

  4. 设置完成后，whenMatchedThenUpdate会执行它所说的操作:匹配条件的行将被下面的set数据更新。

  5. 如果找不到匹配项，将启动；将插入新的一行。

Tip

onDuplicateKeyIgnore和onDuplicateKeyUpdate都可以用于加载器 API。onDuplicateKeyError仅适用于加载器 API。这意味着您可以将更加灵活的 upsert 语义应用于数据的批量装载。

MERGE肯定比INSERT...ON DUPLICATE KEY或INSERT...IGNORE的声明更有力。例如，在一些数据库方言中(Oracle、DB2、Sybase)，您可以DELETE不符合匹配条件的行。

窗口功能

在前一章中，我已经使用窗口函数构建了一个公共表表达式(CTE ),来计算库存中的汽车价格中位数。我没说太多细节，所以我们又见面了。

虽然这不是一本 SQL 教科书，但 13 窗口函数是一个神秘而强大的工具包，值得解释一下。以下是我库存中的车辆:

img/500754_1_En_3_Fige_HTML.jpg

没什么特别的，只是complete_car_listing表中的SELECT *辆车。我感兴趣的是从那个表中得到一个类似这样的报告:

img/500754_1_En_3_Figf_HTML.png

在上面的截图中，我有一个报告，显示了库存中的每辆车及其价格，以及其他信息。以下是关键字段:

  • vehicle_id明显指特定的车辆。我也有一些个别车辆的细节，如model、year和price。

  • price栏是每辆车的价格

  • 然后我有一个price_rank列，向我展示了单个车辆price相对于同一brand的类似车辆的价格的排名。

像AVG或MAX这样的常规分组函数会将所有数据合并成一个值，比如“这是库存中任何一辆丰田汽车的最高价格。”使用窗口函数，我可以说“这是库存中每辆车的价格，但是对于每一行，我想显示车辆价格的等级。”

TL；DR: Window 函数允许您组合组函数的汇总功能，同时保留显示组成组值的单个行的能力。

这…不是最容易解释的事情。那么，让我向你展示一个窗口函数如何产生我感兴趣的结果。SQL 查询如下所示:

  1. 前面的查询有标准的 SQL 组件和子句——AVG组函数、GROUP BY子句等。

  2. DENSE_RANK函数为从结果返回的行给出一个序号。它是排名函数家族中的一员。还有RANK和ROW_NUM函数，它们提供相似的特性，但是在处理行之间的联系时有一些不同。

  3. OVER标志着窗口功能的开始。它规定了窗口功能需要应用的范围。在这种情况下，

    1. 排名需要发生在车辆价格的基础上。

    2. 可选地，我希望通过brand对排名进行进一步分组。这样，排名在制造商的车辆范围内。我不是说“显示所有汽车价格的排名”，而是用PARTITION在这里说，“将排名分组到每个汽车制造商的桶中。”

select vehicle_id,brand,model, year, price, avg(price) as avg_price, dense_rank() over (partition by brand order by avg(price) desc) as price_rank
from edens_car.complete_car_listing
group by brand, model,vehicle_id

总的来说，我看得出来

  1. 个人汽车记录及其详细信息

  2. 根据平均价格将汽车记录分组的结果

  3. 单个车辆的价格相对于其组内平均价格的等级

让我们喘口气…然后我们看看 jOOQ 中会是什么样子:

context.select(COMPLETE_CAR_LISTING.VEHICLE_ID,
                            COMPLETE_CAR_LISTING.BRAND,
                            COMPLETE_CAR_LISTING.MODEL,
                            COMPLETE_CAR_LISTING.YEAR,
                            avg(COMPLETE_CAR_LISTING.PRICE).as("avg_price"),
                            rank().over(partitionBy(COMPLETE_CAR_LISTING.BRAND)
                                    .orderBy(avg(COMPLETE_CAR_LISTING.PRICE).asc()))
                                    .as("price_rank")
                    )
                    .from(COMPLETE_CAR_LISTING)
                    .groupBy(COMPLETE_CAR_LISTING.BRAND, COMPLETE_CAR_LISTING.MODEL, COMPLETE_CAR_LISTING.VEHICLE_ID);

我知道，这看起来像很多代码，但是您可以将它理解为与查询的 SQL 版本的一对一映射。rank函数和它后面的所有东西都由唯一的DSL类提供。

唷！这是一只可爱的小鸭子，因为它坚持读完了这本书。

img/500754_1_En_3_Figg_HTML.png

橡皮鸭调试:真人电影！

四、集成 jOOQ

也被称为“吃你的蛋糕，也有它。”如果你和 jOOQ 在一个新项目中重新开始，恭喜你，祝你好运！如果你有一个包含其他技术和 API 的现有项目呢？jOOQ 仍然可以发挥巨大的作用。总而言之，jOOQ 在以下几个方面做得非常好:

  • 生成高度表达性、类型安全、可重用的 SQL ,这样您就可以放心，您的 SQL 总是正确的。

  • 从数据库实体生成 Java 类,这样您就再也不用手动构建另一个实体、DTO 或活动记录了。

  • 顺利管理数据库方言、怪癖和缺点这样您就不必为不同数据库供应商之间的差异而烦恼。顶级便携性！

…当然，还要与数据库服务器的性能和可扩展性保持一致。话虽如此，jOOQ 并不想成为你唯一的爱。我的意思是，这很好，但是如果你已经使用了某些技术，jOOQ 非常乐意分担责任。

img/500754_1_En_4_Figa_HTML.png

只要我们都明白谁是这个 真正的 这个联盟中的数据库的朋友

在这一章中，我们将看看 jOOQ 如何通过将其独特的能力借给现有的 API 和库来增强您的应用实现。

使用 jOOQ 的 Java 持久性 API

Java 持久性 API (JPA)是 JakartaEE 规范，它定义了如何将数据库对象映射到 Java，也称为对象关系映射(ORM)。它展示了实现 API 应该如何处理将数据库组件、SQL 和其他数据库内容转换成 Java 类、接口以及相反的过程。它定义了特定环境下参考实现的预期行为。它还定义了 Java 持久性查询语言(JPQL)，这是一种 SQL 风格的查询语法，试图复制 SQL 的习惯用法，但用于 Java 类。然后，我们将目光投向供应商，如

  • 红帽/冬眠

  • Eclipse/EclipseLink

  • Oracle/TopLink

  • OpenJPA

最终，行业供应商将按照规范的指导实现一个功能 API。关键词是“指导”——规范是一个指导方针，供应商可以并且通常会违反规范的规则。或者，他们可以实现一些规范定义的功能，但是以非标准的方式。这意味着您的收获可能因 JPA 实现的不同而不同。

但是你可能已经知道了这一切。

这里我们不打算详细探讨 JPA 只想回答一个问题:在 JPA 的世界里，jOOQ 能为你做什么？

生成 JPA 实体

JPA 中的基本工作单元是实体。JPA 实体是一个 Java 类，用于表示数据库表或表中的行。因为这不是一本 JPA 教科书，所以我不打算详细介绍 JPA 实体。简单地说，jOOQ 可以为您创建一些基本的 JPA 实体。你只需要问:

<generate>
            <jpaAnnotations>true</jpaAnnotations>
</generate>

真的就这么简单。在 jooq 代码生成器设置中标记jpaAnnotations“on ”,您将得到如下内容:

/**
 * This class is generated by jOOQ.
 */
@Entity
@Table(
    name = "vehicle",
    schema = "edens_car",
    indexes = {
        @Index(name = "veh_manufacturer_id_idx", columnList = "vehicle_manufacturer ASC"),
        @Index(name = "veh_model_id_idx", columnList = "vehicle_model_id ASC"),
        @Index(name = "veh_style_idx", columnList = "vehicle_style ASC")
    }
)
public class Vehicle implements Serializable {

    private static final long serialVersionUID = 1L;
    private Long          vehicleId;
    private Long          vehicleManufacturer;
    private BigDecimal    vehicleCurrPrice;
    private LocalDate     vehicleModelYear;
    private String        vehicleStatus;
    private String        vehicleColor;
    private Long          vehicleModelId;
    private Long          vehicleTrim;
    private Long          vehicleStyle;
    //more fields
    public Vehicle() {}
    //getters, setters, constructors, toString etc
}

这里最重要的注释是@Entity。这对于 JPA 运行时来说意味着这个类的实例应该由 JPA 运行时来管理。这对 JPA 运行时如何看待这个类的实例有很大的影响。从这个实体类Vehicle的实例存在的那一刻起，JPA 运行时就开始关注了。实体的任何变化、它的任何新实例、从数据库的检索等等。所有这些都由 JPA 运行时跟踪。当两个线程试图修改支持特定Vehicle实例的底层表行时，JPA 运行时的工作就是确保只有一个线程或者没有一个线程成功地进行修改。

现在，我们的赞助商说一句话。

img/500754_1_En_4_Figb_HTML.png

不，不是你，先生！

我强烈主张像对待底层数据库行数据一样对待 JPA 实体的实例。像处理 POJO 或数据传输对象(DTO)这样的“哑”对象一样处理这些实体，这是一种非常普遍但阴险的代码味道。因为实体是活动的、受管理的对象，所以您面临

  1. 在正常的进程执行过程中意外地改变了对象的状态。

  2. 如果在只读操作中长时间持有实体的实例，就会引发类似StaleObjectStateException的状态管理异常。这在分布式环境和微服务中尤其容易发生。一个线程只是想读取一些数据，也许把它作为 web 服务响应发送出去。另一个线程同时想要对同一实体的底层数据进行更改。这些线程中的一个将会有一段糟糕的时间。

  3. 当您对数据库操作和 web 服务响应使用相同的实体类时，或者将它持久化为不同的格式(如 JSON)时，会泄漏数据。您将在几个方向上不加选择地传输表列。

TL；DR:将你的 POJO 需求和 ORM 需求分开。他们不是同一类型的班级。

一种解决方法是运行代码生成器两次:一次将jpaAnnotations设置为false，另一次将其设置为true。记得在两次运行之间更改输出包。

除了普通的 JPA 注释，jOOQ 还可以添加

  • Serializable与<serializablePojos>true</serializablePojos>的接口

  • 用<jpaVersion>2.2</jpaVersion>对特定版本的 JPA 支持

近了。

从 JPA 实体生成

*是的，你没看错:jOOQ 可以让你开始使用 jOOQing，即使你没有一个真正的数据库。如果你有 JPA 实体但没有数据库，jOOQ 仍然可以为你生成代码。考虑到我反对重用实体作为 dto 或 POJOs，这非常方便。这样，您的 JPA 实体可能已经预先生成并打包成一个 JAR 您所需要做的就是从这些实体中生成 POJOs，这样您就可以轻松度日了。观察。

首先添加以下 Maven(或等效的 Gradle 等。)项目的条目:

<dependency>
         <groupId>org.jooq.pro-java-11</groupId>
         <artifactId>jooq-meta-extensions-hibernate</artifactId>
         <version>3.15.1</version>
</dependency>

这将引入 jooq hibernate 扩展包。接下来，对代码生成器本身进行一些配置更改:

<database>
       <name>org.jooq.meta.extensions.jpa.JPADatabase</name> (1)
      <properties>
      <!-- A comma separated list of Java packages, that contain your entities -->
          <property>
              <key>packages</key>
              <value>com.apress.samples.jooq.jpa.entity, com.apress.samples.jooq.ext.jpa</value> (2)
         </property>
<!-- The default schema for unqualified objects:
- public: all unqualified objects are located in the PUBLIC (upper case) schema
- none: all unqualified objects are located in the default schema (default)
This configuration can be overridden with the schema mapping feature -->
         <property>
               <key>unqualifiedSchema</key>  (3)
               <value>none</value>
        </property>
    </properties>
</database>

这是什么？

  1. 为了从 JPA 实体类生成，需要更改名称。org.jooq.meta.extensions.jpa.JPADatabase定义生成器的数据来源。将这与我到目前为止一直使用的org.jooq.meta.mysql.MySQLDatabase进行对比，因为我的代码是从实际的数据库中生成的。

  2. 我指定了 jOOQ 应该扫描的包，以便能够解析 JPA 实体类。

  3. jOOQ 应该如何处理没有模式数据的实体？unqualifiedSchema属性接受none，这意味着所有缺少模式信息的实体都将被放入默认模式中。public也是有效的，意味着默认情况下这些实体将被放在公共模式中。你也可以用SchemaMapping覆盖所有这些。

对于我反对滥用实体类作为 dto 的理由来说，这是另一个很好的变通方法，尤其是当您已经有了遗留的 JPA 实体类时。只需从现有的 JPA 实体中生成 POJOs，并跳过代码生成器配置中的jpaAnnotations指令。

生成 SQL 查询

当然，最明显的用例。jOOQ 将永远超越 JPA 能想到的任何东西。因此，当您想要认真对待您的数据库时，您应该考虑将 SQL 查询生成委托给 jOOQ，这是显而易见的。JPA 提供了许多机会来提供您自己的 SQL。考虑我们钟爱的车辆选择查询:

Query jooqQuery = DSL.using(SQLDialect.MYSQL,new Settings()
                .withRenderQuotedNames(RenderQuotedNames.NEVER))
                .select(VEHICLE.VEHICLE_ID, VEHICLE.VEHICLE_COLOR, VEHICLE.VEHICLE_CURR_PRICE)
                .from(VEHICLE)
                .where(VEHICLE.VEHICLE_MANUFACTURER.eq(param("vehicle_manufacturer", Long.class))).getQuery();

前面的 jOOQ 语句

  • 使用DSL类配置即将生成的 SQL 语句的方言。它还指定引号不应该用在生成的 SQL 中——这可能变得很重要，这取决于为 JPA 实现配置的方言。例如，常规的双引号(")可能会导致 Hibernate 阻塞。

  • 从vehicle表中选择一些字段，但是我没有执行它，而是获得了org.jooq.Query的一个实例。这是所有 SQL 语句的 jOOQ 表示的父接口。你注意到我在这里没有使用DSLContext了吗？相反，我直接使用DSL类来创建我的 select 语句。这意味着我不需要为了构建 jOOQ SQL 查询而去构建DSLContext或 JDBC 连接。

  • 将VEHICLE.MANUFACTURER列作为查询参数与param函数绑定。这意味着我可以在运行时提供一个动态值。

  • 最后的getQuery方法产生了一个Query对象，从这个对象中我可以获得纯文本 SQL 语句，以及其他内容。

这对 JPA 世界有什么帮助？

JPA 为您提供了一系列机会来提供自己的 SQL 查询。你为什么想这么做？嗯，简单的事实是，对于任何比来自几个表的简单的SELECT语句更复杂的东西，JPA 都不是最好的选择，特别是在大规模的情况下。如果需要使用常用的表表达式、内联视图、窗口函数等。，您将需要创建自己的 SQL。JPA 菜单上没有分层查询。JPQL 尽可能地支持 SQL 规范的一个子集。这就是你的Query对象出现的地方。

@PersistenceContext
 EntityManager entityManager;  (1)
...
javax.persistence.Query nativeQuery = entityManager.createNativeQuery(jooqQuery.getSQL());  (2)
int parameterCount = 1; //JDBC parameter values begin their index at 1, not 0.
 long vehicleStyle = 4;
        for(Parameter parameter: nativeQuery.getParameters()){
             nativeQuery.setParameter(parameterCount++, vehicleStyle); (3)
        }
List<Vehicle> resultList = nativeQuery.getResultList(); (4)
logger.info("Results: count: {} \n list: {}",resultList.size(),resultList.toArray());

那好吧。让我们深入了解一下:

  1. EntityManager是进入 JPA 运行时的网关，也称为PersistenceContext。我提到过我对上下文对象模式有多着迷吗？这是其中之一。从数据库映射的所有数据库行都可以从该对象中获得。几乎所有您想用 JPA 做的事情都可以从这里开始。根据您使用的平台(JakartaEE、Spring Data、Quarkus 等),有多种方法可以获得这个对象的实例。);我不会在这里详细讨论。

  2. EntityManager对象提供了允许我提供定制 SQL 的createNativeQuery方法。这就是我的org.jooq.Query物体发光的地方。我使用getSQL方法获得从 jOOQ 查询中生成的明文 SQL。

  3. 因为我已经在 jOOQ 查询上定义了一个查询参数，所以 JPA 查询通过我传递给它的普通字符串 SQL 自动继承了这个参数。这意味着我可以动态地为 JPA Query对象识别的每个可用的Parameter设置值。总的来说，这是一个特别灵活的操作，例如:除了索引值之外，我还可以通过名称引用我的查询参数。

  4. 最后，我可以执行 SQL 语句并使用Query#getResultList检索我的查询结果。这个方法可以返回一个 JPA 实体类列表或一个Object列表，我可以透明地将它们转换成我选择的任何类。这里，我选择使用 jOOQ 为我生成的Vehicle POJO 类。这是一个非附加的、非托管的 java 对象，所以我不必担心通过这个查询的结果意外修改底层数据库数据。

在 JPA 世界中，还有其他机会来利用来源可靠、经过认证的无冲突和无麸质 SQL。您可以将一个 JPA 实体类传递给createNativeQueryMethod。

Query nativeQuery = entityManager.createNativeQuery(jooqQuery.getSQL(), VehicleEntity.class)

使用这种方法，EntityManager返回的任何VehicleEntity实例都是托管对象——如果对这些对象的状态进行更改，将会影响底层 db 行中的数据。这将把查询结果映射到该类的实例，前提是列名和其他内容匹配。

当数据库列与您的类声明不一致时怎么办？也许您正在使用列别名，或者您想从一个语句中返回多个实体类型？当你根本不想使用 JPA 实体类的时候怎么办？就我个人而言，我绝对喜欢不用担心通过实体意外修改表数据。我希望断开连接的对象用于只读目的。

看，SQL 结果映射的三个 JPA 骑手！

img/500754_1_En_4_Figc_HTML.png

"l ol 什么是 SQL？"

好吧，说真的，是这三个注解:

  1. @SqlResultSetMapping定义是否需要将 SQL 查询的结果映射到 java 对象。这个注释可以应用于任何带有Entity注释的 JPA 类。在一个类上定义了这个注释之后，你可以通过名字来引用它。请继续关注我，看看它的实际应用。

  2. 在 JPA 2.1 中引入，这样我们可以使用 JPA 来构造非托管的 Java 对象/实体。在此之前，一切都必须是一个托管的 JPA 实体。见我之前的警告，为什么这可能成为一件坏事。有了这个注释，即使你提供了一个用@Entity注释的类，JPA 运行时也会忽略它，并且不会试图管理这个构造的任何结果。

  3. @ColumnMapping允许您将 SQL 查询结果中的列映射到非 JPA 实体(即 POJO)的字段。这就是如何定义从 SQL 结果到 Java 类字段的列别名和其他不一致名称的映射。在 JPA 行话中，这样的列被称为标量列。

那么，这些是如何协同工作的呢？看看这个:假设我已经运行了 jOOQ 生成器并获得了一个 JPA 注释的 POJO com.apress.jooq.generated.tables.pojos.VehicleModel，我可以让映射注释像这样工作:

@SqlResultSetMapping(name="nonJPAManagedVehicleModel", (1)
        classes = {
        @ConstructorResult(targetClass =  com.apress.jooq.generated.tables.pojos.VehicleModel.class,   (2)
        columns = {                         (3)
                            @ColumnResult(name="vehicle_model_id"),
                            @ColumnResult(name="vehicle_model_name"),
                            @ColumnResult(name="vehicle_style_id"),
                            @ColumnResult(name="vehicle_man_id"),
                            @ColumnResult(name="version")
                             })
       })
@Entity
public class VehicleModel implements Serializable {
...
}

好吧，系好安全带，我来解释这里发生了什么:

  1. 一切都从这里的@SqlResultSetMapping开始，我说:“我想在 SQL 语句和 POJO 之间定义一个自定义映射。我已经将命名为查询nonJPAManagedVehicleModel,因为我就是这么做的。”

  2. 然后我定义这个自定义映射中涉及的类。对于这个例子，我只对 POJO VehicleModel感兴趣。这就是事情变得有点冗长的地方。

    1. 我需要为 JPA 运行时描述一个合适的构造函数，以便能够用@ConstructorResult创建我的 POJO 类的实例。有了这种映射，JPA 知道如何处理查询结果。

    2. 请记住:尽管这个类在技术上是一个 JPA 实体类，但是当我在这个上下文中使用它时，JPA 不会将这个查询的结果视为托管实体，这在我看来是非常棒的。

  3. @ColumnResult帮助我将查询结果中的name映射到VehicleModel POJO 类中的字段。JPA 如何知道将列映射到类中的哪个字段呢？由列表columns中列的位置决定。JPA 运行时将寻找一个合适的与这里的描述相匹配的构造函数，并只挑选指定的列传递给构造函数。

最后，我可以像这样使用我的命名查询:

entityManager.createNativeQuery(vehicleModelQuery.getSQL(),"nonJPAManagedVehicleModel");

JPA 运行时将尝试使用我提供的名称来查看我的 SQL 映射。这为它提供了执行查询和构建结果对象列表所需的所有信息。

这是相当冗长的，所以不要担心它是否能一下子完全理解——尽可能多的重复一遍。更简单的描述是告诉 JPA

  1. 要映射到哪个 POJO 或实体类

  2. 应该使用 SQL 结果集中的哪些列名

  3. 在 POJO 类上使用哪个构造函数

  4. POJO 类的构造函数中应该使用哪些列

总之，这些工具允许您将 SQL 查询打包到非常可移植和灵活的部署单元中；考虑数据库方言，并保证查询的有效性。

Caution

在撰写本文时，jOOQ 有一个错误 1 ，使得它需要对 JPA 实体进行@Column注释。jOOQ 生成的实体类不会有这个问题；但是如果您将自己的 JPA 实体带到 jOOQ 聚会，请确保将@Column添加到该实体的字段中。否则会发生一些奇怪的事情(例如，列值没有映射到结果对象)。

现在，我们向结果集映射的骑手说再见。

img/500754_1_En_4_Figd_HTML.png

Aww！振作起来，伙计们！

Spring Boot 和乔克

Spring Boot 是当前企业 Java 开发的领军人物。电流。几乎没有什么是你不能用 Spring 平台做的，我甚至不打算在这里讨论它的许多特性。让我们看看 jOOQ 如何美化您的 Spring Boot 应用。但首先，一些配置:

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/edens_car
spring.datasource.username=username
spring.datasource.password=thisisaterriblepassword
spring.jpa.show-sql=true

这为我的 Spring Boot 应用设置了到 MySQL 数据库的连接属性。这些属性放在标准的application.properties文件中。还有编程上的对等物。

Spring 通过以下组件支持 SQL 数据访问:

  • 香草 JDBC 访问的春季数据 JDBC

  • spring Data JPA for the sweet Hibernate+JPA combo

  • 用于反应式数据访问的 Spring 数据 R2DBC

因为 Spring Boot 平台是如此的庞大，我将在这一部分尽量保持简洁。还要注意，我们在上一节以及这一节中介绍的几乎所有内容都适用于 Hibernate。Hibernate 是 JPA 实现，它为 Spring framework 的许多数据访问功能提供了动力。

首先要知道的是，你可以用 Spring Boot 批量安装 jOOQ 作为你的整个数据访问组件。大概是这样的。

在 Spring Boot 配置 jOOQ

让我们通过编程配置设置使 jOOQ 在 Spring 应用上下文中随处可用:

@Configuration                                               (1)
@EnableTransactionManagement
public class JdbcConfig extends AbstractJdbcConfiguration {
    @Autowired
    private DataSource dataSource;                (2)

    @Bean
    DataSourceConnectionProvider connectionProvider() {
        return new DataSourceConnectionProvider(new TransactionAwareDataSourceProxy(dataSource));
    }

    @Bean
    DSLContext dsl() {
        return new DefaultDSLContext(dslConfig());         (3)
    }

    private org.jooq.Configuration dslConfig() {

        DefaultConfiguration defaultConfiguration = new DefaultConfiguration();
        defaultConfiguration.set(dataSource)
                .set(SQLDialect.MYSQL)
                .set(DefaultExecuteListenerProvider.providers(new QueryRuntimeListener()));
        return defaultConfiguration;
    }
}

这里有相当多的 Spring 框架样板文件，但是我将把重点放在与 jOOQ 相关的部分:

  1. 我用@Configuration注释和其他标准的 Spring 框架组件(如@EnableTransactionManagement)设置了我的 Spring 配置 bean，让 Spring 管理我的数据库事务；AbstractJdbcConfiguration所以我的配置类可以继承更多的样板文件。这是样板大杂烩。

  2. 我使用 Spring 的依赖注入来获得一个DataSource实例。DataSource是我的数据库连接和池的更成熟、可伸缩和健壮的表示，由 Spring Boot 运行时管理。这将在这里提供，因为我已经在标准的application.properties文件中配置了我的数据库属性。

  3. 我定义了一个可以按需构造DSLContext实例的方法。添加@Bean注释将它标记为 Spring Boot 的工厂方法。这意味着我可以在我的 Spring 应用中的任何地方获得一个新的DSLContext实例。

有了这个设置，我可以在应用的任何地方获得一个DSLContext:

@Autowired
DSLContext context;

public void selectWithJooq(){
    context.selectOne();
}

然后我就可以尽情地狂欢了。可以扩展前面的代码片段，为每个请求生成一个新的DSLContext实例，支持多租户等等。如果你能梦想到，jOOQ 可能会尽最大努力去实现它。更不用说 jOOQ 能为你生成的 Dao 了。不错。

自定义 SQL 查询怎么样？

使用自定义 SQL

如果有定制的 SQL 需要编写，jOOQ 会自动生成。为了使用 Spring Data JPA 的定制查询，我首先创建一个Repository:

public interface VehicleModelJooqRepository extends CrudRepository<VehicleModel, Long> { (1)
    @Query(nativeQuery = true, name="CustomDynamicSQL")
    List<VehicleModel> findVehicleModelByVehicleManId(long id);
}

请允许我解释:

  1. 我扩展了CrudRepository,作为使用 Spring Data JPA 的存储库特性的合同的一部分。将Vehicle和Long指定为该接口的类型，我将通知 Spring 数据运行时，该接口将用于从vehicle_model表中检索VehicleModel。

  2. 我定义了一个findVehicleModelByVehicleManId，它接受一个对应于vehicle_man_id的long参数来过滤结果

    1. 重要的是，我使用了@org.springframework.data.jpa.repository.Query注释。Spring Data JPA 允许我在这个注释中指定一个明文 SQL 查询；或者，我可以在其他地方定义查询，通过一些 Spring 魔法，它将被选中。敬请关注。到目前为止，这个 JPA 存储库期望在PersistenceContext的某个地方找到一个名为“CustomDynamicSQL”的原生查询。

    2. 我传递给查询方法findVehicleModelByVehicleManId的每个参数都将作为查询参数传递给该方法将要执行的本地查询。这一点很重要，因为要么必须将方法参数的位置与普通 SQL 中查询参数的位置相匹配；或者，您可以使用@Param注释将您的参数与其 SQL 等价物进行名称匹配。

现在，我需要连接我的定制 SQL 查询，由 jOOQ 赞助。为了将我的 SQL 查询真正插入 JPA 运行时，我求助于我们的老朋友，EntityManager:

javax.persistence.Query nativeQuery = entityManager.createNativeQuery(jooqQuery.getSQL());
entityManager.getEntityManagerFactory().addNamedQuery("CustomDynamicSQL",nativeQuery);

从我的org.jooq.Query中获得了javax.persistence.Query的实例:

  1. 我从EntityManager中获得一个EntityManagerFactory。

  2. JPA 2.1 中添加了addNamedQuery方法，允许动态构造命名查询。有了这个，我需要补给

    1. Spring Data JPA 可以通过其查找的查询的名称。注意我是如何使用我在前面的Repository接口中定义的方法的完全限定名的。这就是 Spring Data JPA 将如何尝试基于我添加到我的自定义Repository方法中的@Query注释来查找命名查询。

    2. 要执行的实际 SQL 查询。

剩下的就交给 Spring Boot 了。我只需注入我的自定义存储库，并根据需要使用它:

@Autowired
VehicleModelJooqRepository vehicleModelRepository;
...
List<VehicleModel> modelByVehicleManId = vehicleModelRepository.getVehicleModelByVehicleManId(vehicleManufacturer);

…就这样！这个动态 SQL 特性是对我们已经探索过的标准 JPA 特性的补充——Spring 也支持这些特性。

jOOQ Spring Boot 首发

据说 Spring Boot 提供了一个入门工具，可以帮助你用 jOOQ 引导你的 Boot 项目。

img/500754_1_En_4_Fige_HTML.jpg

start.spring.io

在实践中，我试图用它来引导，包括 Spring 数据 JPA 和 JDBC 模块。对我来说看起来不是很有效，因为

  • starter(目前)不包含任何 jOOQ 依赖项。

  • 它生成的代码存根甚至不包含任何对 jOOQ 的引用(参见前面的讨论)。

所以，也许暂时不要谈这个。

夸特斯和乔克

Quarkus 2 是首屈一指的云原生、容器和 Kubernetes 优先的微服务平台。它几乎支持你想用 Java web 服务平台做的任何事情。您可以集成现有的 JakartaEE 或 Spring beans，并使用相同的编程组件来获得

** 极快的启动时间

  • 低内存占用

  • 与 AWS、Google Cloud 和 Azure 等主要云提供商的功能和组件紧密集成

  • 轻量级部署包

  • 反应优先的编程风格

  • Kotlin 和 Scala 兼容性

Quarkus 确实是软件工程的天赐之物。我这么说是作为一个完全独立和公正的观察者。

img/500754_1_En_4_Figf_HTML.jpg

是的。完全无偏

那么，jOOQ 在夸库斯能为你做什么？就像 Spring Boot 一样，jOOQ 可能是您需要的所有 SQL 数据访问。它还可以与 Quarkus 中现有的 API 一起工作，比如

  • 作业的装配区（JobPackArea）

  • 冬眠

  • 反应式 SQL

  • SQL 结果集流

在撰写本文时，您唯一不能做的事情是在native模式下使用@Query注释。Quarkus 支持大部分 Spring 数据 JPA，除了这个位。那么，怎样才能让乔克进入夸库斯呢？

从 Quarkus jOOQ 扩展开始:

<dependencyManagement>
   <dependencies>
      <dependency>
        <groupId>io.quarkus</groupId>
        <artifactId>quarkus-bom</artifactId>
        <version>${quarkus.platform.version}</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>
<dependency>
      <groupId>io.quarkiverse.jooq</groupId>
      <artifactId>quarkus-jooq</artifactId>
      <version>0.2.2</version>
</dependency>

除了标准的 jOOQ 依赖项之外，前面的代码片段摘录了将 jOOQ 扩展添加到 Quarkus 需要添加到 Maven POM(或 Gradle 等价物)中的内容。这不一定是向 quarkus 添加扩展的最佳方式。理想情况下，您应该像这样使用 Quarkus maven 插件:

mvn quarkus:add-extension -Dextension=quarkus-jooq

这是安装 Quarkus 扩展的标准方式。它不适用于 jOOQ 扩展，因为它(还)不在 Quarkus 扩展的注册表中。jOOQ 扩展不是 Quarkus 的“官方”扩展，因为它不是由 Quarkus 核心团队构建和维护的。这是他们称之为 Quarkiverse 的一部分， 3 一个扩展的扩展生态系统，将所有权交给开发者社区。Quarkus-jOOQ 扩展是 Quarkus 团队之外的个人在大众需求的支持下努力工作的结果。大声喊出来！

接下来，为您选择的数据库安装合适的 JDBC 扩展:

mvn quarkus:add-extension -Dextension=jdbc-mysql

安装 Quarkus 扩展后，您可以在 Quarkus 应用的application.properties文件中配置您的数据源设置:

quarkus.datasource.db-kind=mysql
quarkus.datasource.username=dbuser
quarkus.datasource.password=thisisaterriblepassword
quarkus.datasource.jdbc.url= jdbc:mysql://localhost/edens_car
quarkus.jooq.dialect=mysql

要使这些工作正常进行，必须配置方言。有了这个基本配置，您可以在应用的任何地方获得一个DSLContext:

@Inject
DSLContext dslContext;

@Inject注释是 Spring 框架中@Autowired注释的上下文和依赖注入(CDI)等价物。Quarkus 出于同样的目的支持这两者。quarkus-jooq 扩展只在 jooq 的社区版中提供。如果 POM.xml 中有专业版，可以用专业版覆盖社区版。

现在，让我们谈谈包装和测试所有这些东西。

**

五、封装和测试 jOOQ

最后冲刺！让我们通过谈论 jOOQ 如何适应“现代”软件开发主题，如持续集成/持续开发(CI/CD)、容器(Docker、Podman 等)来结束 jOOQ 路演。)，等等。

但首先，在我们进入肉类(或者蔬菜，如果你喜欢的话)之前，让我们先做一点水平设定:

  • jOOQ 允许你自带 SQL (BYOS)。

  • jOOQ 将为您生成代码，您在编译时最可能需要的代码。

  • 你生成的代码成为你的业务逻辑的一部分，去做…事情。

在当今世界，你需要

  • 能够验证您的定制 SQL 是否有效——无论是您自己编写的 SQL，还是由另一个开发人员或团队打包并交给您的 SQL。

  • 能够管理对您的数据模型所做的增量更改——由您自己或您组织的其他部分发起的更改。您将如何支持添加到数据模型中的新表或列？

  • 对您生成的代码如何生存以及在哪里生存做出实际有效的决定。您的实体和 dto 被打包在一个单独的 JAR 文件中，并作为一个依赖项包含在多个软件项目中，这种情况并不少见(甚至可能是首选)。

  • 运行集成测试，在测试时不需要整个独立的数据库服务器。以 Jenkins 构建服务器为例:理想的情况是，您的构建工作不需要一个常设的 MySQL 服务器来运行您的集成测试。

那么，当您需要…

用 jOOQ 封装代码

抱歉，这将是以 Maven 为中心的。

我们已经看到了如何使用 jOOQ Maven 插件从命令行以编程方式生成 jOOQ 代码。我们还没有谈到的是在哪里放置生成的代码。

从 Maven 的角度来看，src/target/generated-sources是生成代码的推荐位置，不管是 jOOQ 还是其他什么。假设你已经在 POM.xml 中配置了jooq-codegen插件，就像我在第三章中演示的那样，运行mvn package -DskipTests=true将会

  • 按照jooq-configuration.xml中的配置连接到数据库

  • 生成必要的代码

  • 编译整个工具包

  • 跳过运行测试

  • 在target目录下生成一个 JAR 文件

让我们考虑几个场景，在这些场景中，您可能想要稍微偏离这条路径。

当您不需要代码生成时

代码生成很好，但有时，您只想构建自己的工具包，跳过代码生成这一步。也许你已经生成了一次代码，什么都没有改变；或者您有一个不想现在处理的大型模式；或者您正在不支持代码生成器的环境中运行构建。像这样配置 Maven 概要文件:

<profiles>
        <profile>
            <id>no-jooqing</id> (1)
            <build>
                <plugins>
                    <plugin>
                        <groupId>org.jooq</groupId>
                        <artifactId>jooq-codegen-maven</artifactId>
                        <version>3.15.1</version>
                        <executions>
                            <execution>
                                <id>jooq-codegen</id>
                                <phase>none</phase> (2)
                            </execution>
                        </executions>
                    </plugin>
                </plugins>
            </build>
        </profile>
    </profiles>

这是一个标准的 Maven 配置文件配置，您可以在 POM 中的任何位置将其作为顶级元素添加进来。我在这里做的是

  1. 配置名为no-jooqing的配置文件。在这个概要文件中，我定义了 jOOQ 代码生成器插件的基础。这个配置片段反映了 POM 的build部分中相同插件的配置。这里的想法是让这个绑定到概要文件的定义覆盖另一个主插件定义。

  2. 我将它的阶段执行设置为none，这意味着这个插件不应该在任何时候启动。

有了这个设置，我就可以运行 maven 构建了，如下所示:

mvn package -DskipTests=true -P no-jooqing

-P标志通过名称no-jooqing激活我的档案，从而抑制代码生成器插件。可以说，有更简单的方法可以达到这种效果，但是概要文件提供了最全面的方法来选择性地执行插件。例如，您可以选择运行不同的 jOOQ 生成器配置，比如说，基于构建环境中安装的 JDK:

<profile>
    <activation>
      <jdk>14</jdk>
    </activation>
    ...
  </profile>

使用前面的代码片段，我已经配置了我的概要文件，只有当构建在 JDK 14 上运行时才生效，这是第一个为 Records API 提供官方支持的 JDK 版本(jOOQ 可以生成 POJOs)。您可以根据操作系统环境变量和其他条件激活配置文件。这确实是最强大的选择。很酷吧？

当您没有活动的数据库连接时

它发生了:您想要生成 jOOQ 代码，但是您没有访问底层数据库服务器的权限来在构建时连接。但是幸运的是，您有描述模式的数据定义语言(DDL)。jOOQ 提供了org.jooq.meta.extensions.ddl.DDLDatabase生成器组件，因此您可以直接从.sql脚本生成代码。看看这个:

<generator>
    <database>
        <name>org.jooq.meta.extensions.ddl.DDLDatabase</name>
        <properties>
            <property>
            <key>scripts</key>
            <value>src/main/resources/db-dump.sql</value>
       </property>
      ...
   <database>
<generator>

scripts属性接受一个 DDL 脚本的路径，该脚本将被加载用于代码生成。这样，您就不会在构建时被束缚在数据库服务器上。我应该提到，这个特性不仅限于打包使用——您可以在任何适合的场景中使用它。

Pro Tip

使用-- [jooq ignore start]和-- [jooq ignore stop]来包装应该在 DDL 脚本中忽略的 SQL。这意味着如果你的 DDL 包含-- [jooq ignore start] CREATE TABLE ignore_me_please ... -- [jooq ignore stop] CREATE TABLE business_as_usual ...，CREATE TABLE ignore_me_please会被DDLDatabase忽略。

当您的模式需要增量进化时

你听说过进化数据库模式吗？它的基本论点是:对你的数据库模式进行增量修改，就像你已经对代码做的那样。无论您是从一个全新的空数据库开始，还是已经有了一个模式，您通常都会有一个工具

  • 能够对数据模型(DDL)或原始数据(DML)应用新的更改

  • 保留已应用更改的历史记录，为回滚不兼容或中断的更改提供空间

  • 支持对应用于数据库的更改进行版本控制

  • 帮助您的代码与它所依赖的数据库保持一致

目前这一领域最大的两家公司是

它们都基于相同的基本前提:

  1. 以合适的文件格式提供您的数据库更改，以及相关的版本信息。

  2. 他们会将您的数据库更改应用到您指定的数据库。

jOOQ 是如何影响这一切的？与 Hibernates 和 JPAs 相比，jOOQ 与数据模式的状态和代码生成有着更紧密的联系。您最不希望生成的代码引用不再存在的触发器或函数。

jOOQ 通过org.jooq.meta.extensions.liquibase.LiquibaseDatabase和jooq-meta-extensions-liquibase Maven 工件为 Liquibase 提供了本地支持。我自己是一个 Flyway 人，很大程度上是因为 Flyway 不需要专门的配置语法或 DSL 而且，我是一个战略上懒惰的人。

对于基本的 Flyway 用法，只需为您的.sql文件提供类似如下的版本格式:

V1__Your_Descriptive_File_Name_Here.SQL

文件名的V1部分是关键。对模式的后续更新应该增加版本号，以支持 Flyway 的增量更改机制。将所有 SQL 文件保存在/src/main/resources/db/migration中，您就可以开始工作了。此时，您应该将 Flyway 依赖项添加到 POM.xml 中:

<dependency>
       <groupId>org.flywaydb</groupId>
       <artifactId>flyway-core</artifactId>
       <version>7.14.0</version>
 </dependency>

准备就绪后，您就可以运行 Flyway 了。您可以选择命令行方法、容器化方法(稍后将详细介绍)或编程方法。让我们来看看编程方法:

Flyway flyway = Flyway.configure().dataSource(jdbcUrl,dbUser , dbPassword).load();
flyway.migrate();

真的就这么简单。Flyway 将在/db/migration中寻找最新版本的 SQL 脚本，并将更改应用到您指向的数据库。它还考虑了您的模式的先前版本，因此当您有一个V10__my_schema_update.sql时，到那时为止的更改都被考虑在内。它支持为您的迁移建立基线，因此您可以选择在V7__new_db_baseline.sql为您的模式建立基线，并且它将从那里开始考虑迁移。

顺便说一下，到目前为止，我所说的关于 Flyway 的一切都是超级可配置的；出于演示的目的，我坚持使用默认值。因为飞行路线不一定是这部分的重点。不，在这里，我想让我们考虑一个进化的数据库模型如何支持 jOOQ 的目标，以帮助生成和打包基于最新但不断进化的模式的最新代码。当考虑在 CI/CD、DevOps 繁重的环境中运行时，情况变得更加棘手。你不能指望总是有一个常设的数据库服务器连接到你的 Jenkins 主机，让 Flyway 或 jOOQ 来运行。

理想的设置是一个自包含的软件项目，它可以在项目生命周期的任何时候在内部运行自己的代码生成。无论是在开发人员的机器上，在代码库中的预合并步骤中，还是作为构建管道的一部分。不需要确保某个数据库服务器已启动。不必担心另一个开发人员对同一个数据库进行并发更改会破坏模式或数据库。是啊，那会很甜蜜，不是吗？

img/500754_1_En_5_Figa_HTML.png

极乐的

在代码中实现自给自足的一种方法是使用一个名为 TestContainers 的工具包。在这一章的后面，我会更详细地讨论测试容器。现在，可以说 TestContainers 是始终拥有一个完整的数据库并与应用捆绑在一起的最佳方式。

自给自足的数据库项目的秘诀

声明:这是一个黑客。比方说，在没有专用的 TestContainers maven 插件的情况下，您需要发挥创造力，以便能够在测试阶段之外运行 TestContainers 支持的项目。

但是在我们看到 TestContainers 如何交付一个真正自我维持的应用之前，我们应该看看我如何打包我的项目来支持我的雄心壮志。考虑下面的类:

public class PreflightOperations {
    final static Logger logger = LoggerFactory.getLogger(PreflightOperations.class);

    public static void main(String[] args){
       logger.info("Running preflight operations");
       GenericContainer container = startDatabaseContainer(); (1)
       runFlywayMigrations(container); (2)
       generateJooqCode(container); (3)
    }
}

这几乎是一个普通的 Java 类，它将做三件事:

  1. 启动一个 TestContainers 数据库(我将在本章后面展示它是如何工作的)。从启动的数据库容器中，我将能够获得一个数据库连接。

  2. 使用 observer 数据库连接，我应该能够立即运行我的 Flyway 迁移。

  3. 一旦我的模式更新被应用到我的数据库容器中，我就可以以编程方式运行 jOOQ 代码生成器。

很简单，对吧？现在的问题是:我如何让这个定制代码作为构建过程的一部分运行？Maven？Maven:

<plugin>
        <artifactId>maven-compiler-plugin</artifactId>
         <version>3.8.1</version>
                <executions>
                    <execution>
                        <id>pre-compile</id>
                        <phase>generate-sources</phase> (1)
                        <goals>
                            <goal>compile</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>3.0.0</version>
                <executions>
                    <execution>
                        <phase>process-sources</phase> (2)
                        <goals>
                            <goal>java</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>

                    <executable>java</executable>
                    <mainClass>com.apress.jooq.generator.PreflightOperations</mainClass> (2a)
                   <cleanupDaemonThreads>false</cleanupDaemonThreads>
                </configuration>
            </plugin>

作为构建过程的一部分，运行 java 代码的能力依赖于两个 Maven 插件:

  • maven 编译器

    这个插件将编译源代码。因为我的PreflightOperations类仍然是原始源代码，所以我需要在能够作为构建过程的一部分运行它之前编译它。

  • 玛文行政长官

    这个插件将运行任何任意可执行文件。为参数executable选择java，准备插件使用main方法执行 Java 类。

这些插件一起唱出美妙的音乐:

  1. maven-compiler 首先在 maven 构建过程的generate-sources阶段编译我的源代码。这将确保我有一个编译好的PreflightOperations类来运行…

  2. maven-exec，允许我运行任意可执行文件的插件。我选择在process-sources阶段运行这个插件，它紧接在generate-sources阶段之后。此时，数据库将启动，我的 Flyway 迁移将执行，然后 jOOQ 将生成任何必要的新源代码。

    1. 我向执行的类提供完全限定的类名(FQCN)。

    2. 因为 TestContainers 在守护线程上做了大量的后台处理，所以当 maven-exec 插件准备好继续运行时，它可能还没有准备好退出。允许构建过程继续进行，同时 TestContainers 做它自己的事情。

很简单。我想重申:这是黑客。黄金标准应该是只包含定制代码和配置。此外，生成的代码/实体通常会受益于更多的模块化。

这个配方中唯一缺少的是实际的动态实例化数据库本身。我们将在讨论…时了解这一点

用 jOOQ 测试

又名:晚上睡个好觉。我是自动化测试的绝对狂热分子，尤其是集成测试。

哦哦。我刚刚用了一个流行词:“集成测试。”集成测试往往与一堆其他不符合标准的东西混为一谈(以我外行的观点来看)。请允许我发表意见。

我相信业界已经确定了单元测试的范围，即验证独立代码单元的行为，例如，函数或方法。您不关心这些功能单元如何相互作用来交付业务场景。你可能会模仿被测方法的每一个依赖项，只关注花括号里的内容。

然后，我们有“端到端”测试，在这种测试中，您跨越了多个系统边界——前端到后端集成层，等等。这就是一些人所说的“QA”测试——确保所有东西一起工作来满足用户的需求。

在单元测试和端到端测试中间的某个地方，你会发现集成测试和有时关于它实际上意味着什么的激烈辩论。

img/500754_1_En_5_Figb_HTML.jpg

如图:集成测试。可能吧。

就本节而言，集成测试是如何确保精心选择的代码片段能够很好地协同工作。在典型的集成测试中，您会希望将一些组件连接在一起，并查看它们是否都按照您预期的方式运行。理想情况下，您的集成测试与业务/用户期望的用例紧密结合。 2

这并不是说 jOOQ 没有工具来支持单元测试——远非如此。我个人更倾向于集成测试，这让我对我要推出的产品更有信心(相对于围绕测试的虚荣度量)。问问肯特·贝克 3 他对为了测试而写测试是什么感觉。

基于 jOOQ 提供的特性，有什么需要测试的呢？

  • you 带给 jOOQ 的纯文本 SQL 语句的语法正确性。jOOQ 自己的 SQL 不太可能是错误的。

  • 你的和 jOOQ 自己的 SQL 的语义正确性。jOOQ 保护你不写语法错误的 SQL。验证 SQL 的语义正确性仍然是一个好主意，不管是生成的还是其他的。

  • 您生成的代码相对于数据库模式的准确反映。不管出于什么原因，如果您生成的代码与底层模式有一点不同步，您都不会有好的结果。

SQL 测试行业的工具

让我们看看测试 SQL 有哪些方法。需要说明的是，这些不仅仅是针对 SQL 测试的，但是你知道，这是一本 SQL-in-Java 的书，所以…

  1. JUnit ( www.junit.org

    Java 中最重要的测试——所有其他的都是装腔作势。 4 JUnit 5(代号 Jupiter)是满足您所有测试需求的一站式商店。最新版本几乎支持你能想象到的所有测试范式:行为驱动开发(BDD)，验收测试驱动开发(ATDD)，单元和集成测试，等等。它附带了一套注释，为在不同粒度级别测试 Java 代码提供了各种便利。但是你可能已经听说过了。

  2. 嘲弄框架

    一个模仿框架(例如，Mockito，PowerMock)将帮助你找出代码的不同部分——我知道这不是一个新概念。测试时删除或嘲笑代码中的选定部分，可以让您将测试重点放在对您来说重要的事情上。事情可能变得棘手的地方是必须使用 jOOQ 的一些静态方法。在我们深入研究这些问题时，请耐心等待。

  3. 嵌入式数据库

    In the course of testing, you’ll eventually need to be able to dynamically

    • 按需将模式加载到数据库中

    • 按需加载/销毁数据库中的数据

    • 作为测试场景的一部分，顺序运行依赖于共享状态的测试方法

      All of these scenarios require that your software project have a database ready quickly and flexibly. That’s where the embedded or “in-memory” databases come in. They’re databases that are designed for dynamic and flexible usage in lightweight scenarios, for example, testing. Examples of these include

    • H2 ( https://h2database.com/html/main.html ”)

    • HSQLDB ( http://hsqldb.org/

    • 德比( https://db.apache.org/derby/ )

    有，都是用 Java 写的。有了这些，您就可以在开发生命周期的任何时候拥有一个可用的数据库“服务器”,而不需要在任何地方部署实际的数据库服务器。

    因为它们很轻，所以它们的能力有限。因此，你通常会错过一些基本的功能。检查约束、触发器，甚至是 SQL 关键字LIMIT都可能不被支持，这取决于您选择的供应商。它们之所以轻量级是有充分理由的:快速、高效的数据库操作，没有“多余的东西”如果您更喜欢使用全功能的和便携式的数据库进行测试，那么您应该转向…

  4. 集装箱化数据库

    您可以以与 Docker、Podman 和其他容器运行时兼容的容器化格式获得最强大的数据库，如 MySQL、PostgreSQL 和 Oracle。什么是容器？当我们谈到这一点时，我会更详细地介绍，但是现在这样说就足够了:容器是你最喜欢的软件的可移植版本，打包在所谓的图像中。这些便携包通常包含完整的操作系统安装以及所有的附件；然后，您需要的软件可以与这些完整的操作系统捆绑在一起，并通过一个集中的注册中心交付。容器化的数据库(大部分)提供了首选数据库服务器的全部功能，同时保持了足够的可移植性，可以通过编程/动态地启动一个实例。这样，您就可以在任何需要的时候拥有完整的数据库，例如，作为管道、构建脚本或 JUnit 集成测试的一部分。不要半斤八两。

  5. 乔克检测试剂盒

    There are a few components in the jOOQ toolkit that support your testing and validation needs. Check it out:

    • org.jooq.tools.jdbc.MockConnection、org.jooq.tools.jdbc.MockDataProvider和几个相关的Mock*组件有助于模拟 jOOQ 中查询操作的不同部分。

    • org.jooq.Parser可用于验证您的 SQL 查询，尝试从您的明文 SQL 中产生 jOOQ 工件。

使用 jOOQ 编程时，上述工具的不同组合会让您安心。更不用说 Spring framework 和 Quarkus 等生态系统提供的各种测试工具了——它们都有许多强大的测试技术。最终，我在我的项目中想要的是一个自包含的、自给自足的工具包，它可以在任何地方运行自己的测试，而不太依赖于它的操作环境。当您在 CI/CD 环境中操作时，这种可移植性变得更加重要。让我们看看所有这些是如何一起玩的。

用 jOOQ 进行单元测试

考虑下面的 jOOQ 查询方法:

public static void selectWithOptionalCondition(boolean hasFilter, Map<String, Object> filterValues) throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/edens_car?user=root&password=admin")) {
            DSLContext context = DSL.using(connection, SQLDialect.MYSQL);
            Condition conditionChainStub = DSL.noCondition();
            if (hasFilter) {
                for(String key: filterValues.keySet()){
                    conditionChainStub = conditionChainStub.and(field(key).eq(filterValues.get(key)));
                }
            }
            List<CompleteVehicleRecord> allVehicles = context.select().from(table("complete_car_listing")).where(conditionChainStub).fetchInto(CompleteVehicleRecord.class);
            logger.info(allVehicles.toString());
        }
    }

我正在为 jOOQ 查询的WHERE子句做一些花哨的构造，动态构造将被翻译成该子句的Condition。除了最终需要发生的数据库查询之外，我如何验证我的条件链将产生我期望的WHERE子句？这就是单元测试的用武之地。

使用 Mockito

就像我之前提到的，Mockito 是一个非常流行的模仿框架，它允许你删除测试中不需要调用的代码部分。它还允许您用其他东西替换部分代码，以便于特定的测试场景。对于我的用例，我想验证我的查询中的条件链是否正常工作——我不需要查询结果。我首先将 Mockito 作为一个依赖项添加到我的项目中:

<dependency>
            <groupId>org.mockito</groupId>
            <artifactId>mockito-inline</artifactId>
            <version>3.12.1</version>
        </dependency>
 <dependency>
           <groupId>org.mockito</groupId>
           <artifactId>mockito-junit-jupiter</artifactId>
           <version>3.12.1</version>
  </dependency>

这些 Maven 依赖项将为我的项目提供使用 Mockito 所需的库。这个Mockito-inline工件尤其重要，因为它提供了对模仿静态方法的支持。对该特性的需求很快就会变得明显。mockito-junit-jupiter工件是为最新版本的 JUnit 规定的；对于旧版本的 JUnit，请使用mockito-core来代替。继续编码！

@ExtendWith(MockitoExtension.class) (1)
@TestInstance(TestInstance.Lifecycle.PER_CLASS) (2)
@DisplayNameGeneration(DisplayNameGenerator.ReplaceUnderscores.class) (3)
class JooqUnitTests {
      static MockedStatic mockedDriver; (4)
      final Logger logger = LoggerFactory.getLogger(JooqUnitTests.class);

      @BeforeAll (5)
      public static void prepare(){
             mockedDriver = mockStatic(DriverManager.class); (5b)
      }
 //more to come
}

前面的代码片段演示了我不久将需要的一些测试装置的设置:

  1. @ExtendWith是一个 JUnit 组件，允许用户使用自定义代码插入运行时。然后，不同的供应商可以提供一个类来完成契约并在这里可用。在本例中，我使用的是 Mockito 的MockitoExtension类。将 Mockito 的特性引入到这个测试单元中。

  2. @TestInstance是一个 JUnit 组件，配置测试类的生命周期。在Lifecycle.PER_CLASS中，我指定了我希望JooqUnitTests的一个实例可以被类中任意数量的测试方法重用。这样，测试方法可以在多次调用中共享状态。

  3. 确定测试用例将如何在报告、IDE 和其他地方显示。使用ReplaceUnderscores，我可以在我的测试方法名称中使用下划线，在显示时它们将被空格替换。这样，方法名可以是用户友好的句子，甚至非工程师(例如，产品所有者)也可以理解和使用。

  4. 是另一个 Mockito 测试夹具，它允许我模仿静态方法和接口。我将使用它来拦截来自 JDBC 的DriverManager.getConnection交互。

  5. @BeforeAll规定在运行任何测试方法之前，带注释的方法prepare要运行一次

    1. 这样我就可以定制DriverManager类的行为来满足我的需要

准备工作完成后，让我们继续进行单元测试。抓紧你的座位，太多了:

@ParameterizedTest (1)
@CsvSource({                (1a)
      "BLUE,2020",
      "SILVER,2020"
})
void test_dynamic_condition_api(String color,String year) throws SQLException {
      MockDataProvider mockJooqProvider = context -> { (2)
                        MockResult[] results = new MockResult[1];
                        String sql = ctx.sql();
                        logger.info(()->"Binding 1: "+ctx.bindings()[0]);
                        assertAll(()->{
                              assertTrue(ctx.bindings().length == 2 ); // validate two parameters are bound;
                              assertEquals(ctx.bindings()[0],color);
                              assertEquals(ctx.bindings()[1],year);
                        });
                        CompleteCarListingRecord completeCarListing = new CompleteCarListingRecord();
                        results[0] = new MockResult(completeCarListing);
                        return new MockResult[0];
                  }
            };
            MockConnection mockConnection = new MockConnection(mockJooqProvider); (3)
            mockedDriver.when(()-> DriverManager.getConnection(anyString())).thenReturn(mockConnection); (4)
      JooqApplication.selectWithOptionalCondition(true,Map.of("color",color,"year",year));
      }

该测试的主要目标是确保过滤器参数得到正确处理。作为第二个目标，我不想也不需要对数据库执行实际的查询。所以我需要用 JDBC 的用法替换其他的。这就是 jOOQ 的MockConnection和MockDataProvider的用武之地:

  1. JUnit 提供了@ParameterizedTest,允许我们从多个来源将数据输入到测试方法中。

    1. 这里，我使用@CsvSource选项来模拟 CSV 数据被传入。对于我提供的每一行，JUnit 将解析列，并将它们作为方法参数提供给测试方法。

  2. 为了从 jOOQ 提供一个MockConnection来替换合法的 JDBC 连接，我需要构建一个MockDataProvider。

    1. 在我的MockDataProvider实现中，我可以访问一些非常好的测试装置，比如MockExecutionContext，将要执行的 SQL，以及关键的:提供给查询的参数绑定。然后，我会对它们进行验证，以确保它们存在并且计数正确。这里有很大的灵活性，允许许多测试用例。

    2. MockDataProvider#execute的契约要求我返回一个MockResult的数组。因为我并不关心这个场景中的结果，我只是从一个生成的类中构造了一个空的Record,然后继续。

  3. 实现了我的MockDataProvider，我可以继续构建一个MockConnection。

  4. 还记得之前我用MockedStatic模仿DriverManager的时候吗？现在是时候闪耀了！去掉了DriverManager，我可以规定当任何字符串被传递给getConnection方法时，我的MockConnection应该被返回，而不是一个实际的 JDBC 连接。

有了所有这些设置，我就可以执行我的业务逻辑，看看事情是如何发展的。将不检索任何数据；这都与那一种方法有关。

使用 SQL 解析

jOOQ 附带了一些不一定与 SQL 执行有任何关系的 SQL 解析功能。您可以使用Parser类从纯文本 SQL 生成 jOOQ 组件；在这个过程中，它会让你知道你的 SQL 是否合法。观察:

@Test
void validate_my_dodgy_sql(){
      assertThrows(ParserException.class, ()->
                  DSL.using(SQLDialect.MYSQL)
                         .parser()
                         .parse("selecet * from table group by 1 where having max (column) > 10"));
}

那个 SQL 不对， 6 我相信你会同意的。通过 JUnit 的assertThrows,我已经指定我期望这个尝试parse明文 SQL 应该失败，并出现一个ParserException。 Via con Dios！

img/500754_1_En_5_Figc_HTML.png

yawwwn

相信我，伙计们:BDD 就是它所在的地方。当您处理数据时，您真的想亲自动手执行 SQL 语句；看到真实的结果。你明白我的意思吗？希望如此。因为我们就要开始有趣的部分了！

用 Docker 和 TestContainers 进行集成测试

像我前几页提到的，Docker 是容器的运行时。如果你不熟悉这个概念，可以把 Docker 想象成一个虚拟机——如果你愿意的话，可以称之为 JVM。就像你可以下载任何一种由第三方打包的 JAR，并在你的 JVM 中运行一样，Docker 也有类似的功能。不同厂商发布图片到 Docker Hub 7 然后你可以拉下这些映像并运行基于映像的容器。从某种意义上说，Docker Hub 是容器世界的专家中心。你可以得到几乎任何一个主要的软件作为一个映像，因此，一个容器。这为您提供了极大的灵活性和可移植性，使您能够以一种可移植的、几乎是轻量级的格式运行以前庞大且开销大的软件，这样您就可以以一种动态和灵活的形式运行整个操作系统、CI/CD 服务器和工具、关键的基础设施软件，当然还有数据库。

TestContainers 是一个 Java 库，帮助您进一步提高容器的可移植性。它给了你从 Java 代码中运行任何容器化软件的能力。

img/500754_1_En_5_Figd_HTML.jpg

www.testcontainers.org

作为 JUnit 测试的一部分，我们现在将使用 TestContainers 启动一个 MySQL 数据库服务器，并加载真实的表和数据。然后，我们可以针对它运行实际的代码——这里没有嘲笑的事情。嗯，也许只是一点点。我们走吧！

首先，为你的操作系统下载/安装 Docker—www.docker.com对于大多数用户来说是一个很好的起点。TestContainers 依赖 Docker 运行时来施展魔法。没有 JVM 就不能运行 JAR 文件，是吗？

和往常一样，Maven 依赖项排在第一位:

<dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>testcontainers</artifactId>
            <version>1.16.0</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>1.16.0</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>mysql</artifactId>
            <version>1.16.0</version>
            <scope>test</scope>
        </dependency>

和莫奇托一样，请注意神器。对于它支持的每个数据库版本(有很多)，TestContainers 都有一个专用的 Maven 依赖项。为了使用 MySQL，我添加了mysql工件；为您选择的数据库容器做出正确选择。

现在，为我的下一个演示，做一点测试准备:

@TestInstance(TestInstance.Lifecycle.PER_CLASS)
@DisplayNameGeneration(DisplayNameGenerator.ReplaceUnderscores.class)
@Testcontainers (1)
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
public class JooqIntegrationTests {

    static MockedStatic mockedDriver;
    final static String DATABASE_NAME = "edens_car";
    final static String USERNAME = "auserhasnoname";
    final static String PW = "anawfulpassword";

    @Container (2)
    static GenericContainer mySqlContainer = new MySQLContainer(DockerImageName.parse("mysql:latest")) (3)
            .withDatabaseName(DATABASE_NAME)
            .withInitScript("schema_with_data.sql")
            .withUsername(USERNAME)
            .withPassword(PW)
            .withEnv("TESTCONTAINERS_CHECKS_DISABLE","true")
            .withExposedPorts(3306);

   @BeforeAll
   public static void setup(){
      mockedDriver = mockStatic(DriverManager.class); (4)
   }
}

请记住，这只是在测试课中的准备工作；实际的测试方法将很快出现:

  1. @TestContainers是 TestContainers 库提供的 JUnit 扩展。它实际上是我们之前在 Mockito 中看到的ExtendWith注释的一个外观。

  2. @Container也由 TestContainers 提供。有了这个注释，TestContainers 可以挂钩到 JUnit 运行时的生命周期中，并提前准备容器实例。

  3. GenericContainer类是一个……通用类，它包装了大多数 TestContainers 的基于容器的功能。

    1. 就像我们对 Maven 和任何其他依赖管理系统所做的一样，我必须为 hub 中适当的 Docker 映像提供命名坐标。格式为“名称”:“标签”。这里，我说我想要带有“最新”标签或版本的“mysql”图像。

    2. 允许我为我尚未创建的数据库设置一个名称。

    3. withInitScript定义 SQL 脚本文件的名称，该文件将在容器完成初始化后立即加载。这允许我在任何实际的测试执行之前用 DDL 和 DML 填充我的数据库。

    4. 我用凭证信息withUsername和withPassword播种数据库容器。

    5. 我还可以用withEnv将随机环境变量传递到我的容器中。这里，我提供了一个 TestContainers 命令行参数，允许它跳过一些启动检查，从而更快地准备好数据库容器。

    6. 最后，我定义了 MySQL 应该监听的端口。请注意，这仍然在容器内部。TestContainers 将为我发布一个单独的随机端口，以便我能够连接到 MySQL 容器。这个过程在容器世界中被称为端口映射。

  4. 最后，和我们之前做的一样，我准备模拟出DriverManager，因为我想提供一个动态生成的Connection——但这次是一个真实的 MySQL 数据库。

现在我已经设置好了所有的测试装置，我可以继续编写实际的测试了:

@Test
 public void test_containerized _connection() throws SQLException {
        JdbcDatabaseContainer container = (JdbcDatabaseContainer) mySqlContainer; (1)
        Connection connection =container.createConnection("");    (2)
        mockedDriver.when(()-> DriverManager.getConnection(anyString())).thenReturn(connection); (3)
        JooqDemoApplication.insertVehicle();
    }

在前面的代码片段中

  1. 我将GenericContainer转换成一种更特殊的形式，即JdbcDatabaseContainer。

  2. 这现在允许我直接从容器中获得一个 JDBC Connection的实例。

  3. 然后我可以把我自己的Connection代入DriverManager。

接下来，我可以直接执行我的测试逻辑代码。

这是一个正确的测试。它包含实际的数据、实际的数据库设备和修整，所有这些都在一个真实的数据库中。令人愉快。

这是 JUnit 允许的另一件漂亮的事情:测试方法排序。通过测试方法排序，您可以拥有相互依赖的测试，或者至少必须以特定的顺序运行。看看吧:

   @Test
   @Order(1)
   public void test_containerized_connection() throws SQLException {
       ...
    }

@Test
@Order(2)
public void test_valid_db_insert() throws SQLException {
      if(!mySqlContainer.isRunning()){
            mySqlContainer.start();
        }
        JdbcDatabaseContainer container = (JdbcDatabaseContainer) mySqlContainer;
        container.getJdbcUrl();
        Connection connection =container.createConnection("");
        DSLContext context = DSL.using(connection, SQLDialect.MYSQL);
        List<CompleteVehicleRecord> allVehicles = context.select(field(name("brand")), field("model"), field("price")).from(table("complete_car_listing")).orderBy(field("year").asc(), two()).fetchInto(CompleteVehicleRecord.class);
        assertTrue(allVehicles.size() == 1);
   }

@Order注释允许我规定test_valid_db_insert应该在test_containerized_connection之后立即执行。这就是事情变得有点不稳定的地方。

看，TestContainers 在测试方法执行完毕后会立即关闭容器。容器实际上没有被销毁，但是它没有运行。这就是为什么在跨测试方法重用容器实例时有必要采取一些预防措施。在这个场景中，我用test_containerized_connection插入了数据；然后我想验证test_valid_db_insert中的插入。我必须检查集装箱是否还在isRunning上；否则，测试失败。如果容器没有运行，我可以用start重启它。这是一种支持容器重用的非常粗糙的机制；有了它，你可以变得更加漂亮和易于维护。

Pro Tip

TestContainers 提供了ScriptUtils.runInitScript实用程序，帮助针对数据库容器执行任意 SQL 脚本。这样，即使在数据库的初始加载之后，您也可以在测试的任何时候执行定制的 SQL。

从“遗产很可爱”的角度来看，这一切都很好。如果你在代码中使用容器，你可能不会处理DriverManager。你可能是一个框架型的人。我们试试这件怎么样…

和 Spring Boot 一起

Spring Boot 是，嗯，Spring Boot。它提供了一整套令人眼花缭乱的测试装置和组件。我们不会深究所有这些。我们来这里只是为了快乐。看看吧:

@SpringBootTest (1)
@Testcontainers
public class JooqSpringBootTests {

    @Autowired
    JooqBean jooqBean; (2)
     ...
    @Container
    static GenericContainer mySqlContainer = new MySQLContainer(DockerImageName.parse("mysql:latest"))
    ...
    @DynamicPropertySource (3)
    static void postgresqlProperties(DynamicPropertyRegistry registry) {
        JdbcDatabaseContainer container = (JdbcDatabaseContainer) mySqlContainer;
        registry.add("spring.datasource.url", container::getJdbcUrl);
        registry.add("spring.datasource.password", container::getPassword);
        registry.add("spring.datasource.username", container::getUsername);
    }

    @Test
    @Sql("/schema_with_data.sql") (3)
    public void test_springboot_loading(){
        List<Vehicle> vehicles = jooqBean.runSql();
        assertTrue(vehicles.size() >= 1);
    }
}

我已经精简了这段代码，排除了你到目前为止看到的旧内容。我们来这里是为了新的，仅仅是新的:

  1. 有了@SpringBootTest，Spring 将会注意到并提供它的设施。

  2. 这就是我现在如何注入包含各种 jOOQ 查询的JooqBean。

  3. Spring Boot 在 2.2.6 版中新增了@DynamicPropertySource注释，它允许我动态地覆盖我选择的任何框架属性。这在动态构建未知端口、用户名和密码的数据库容器时特别有用。

  4. 最后，在测试方法本身上，我部署了同样来自 Spring 的@Sql组件。该注释将执行所提供的脚本文件中的 SQL 语句。默认行为是在测试方法运行之前执行脚本，但是这是可以改变的。此外，我可以为不同的目的提供任意数量的脚本。相当整洁。

现在，您已经对 TestContainers 有所了解，让我们重新审视一下我们的打包困境:我们如何在不需要外部数据库服务器的情况下，将更改应用到我们的模式，生成更新的 jOOQ 类，以及运行我们的测试？我已经演示了为支持这个目标需要做的一些准备工作。现在让我们看看支持它的代码。

public static GenericContainer startDatabaseContainer() throws SQLException {
         mySql = new MySQLContainer(DockerImageName.parse("mysql:latest"))
                .withDatabaseName(DATABASE_NAME)
                .withUsername(USERNAME)
                .withPassword(PW)
                .withEnv("TESTCONTAINERS_CHECKS_DISABLE","true")
                .withExposedPorts(3306);
        mySql.start();
        return container;
    }

前面的代码片段与我在测试业务中展示的代码没有太大的不同。这里的主要区别是我用start方法显式地启动数据库容器。是的，还有一个stop方法可以在你完成时使用。启动容器化的 MySQL 后，我可以用 Flyway 执行我的迁移。

//run the migration with a connection to the database container
public static void runMigrations(GenericContainer container){
        JdbcDatabaseContainer container = (JdbcDatabaseContainer) container;
        Flyway flyway = Flyway.configure().dataSource(container.getJdbcUrl(),container.getUsername(),container.getPassword()).load();
        flyway.migrate();
    }

然后把所有的东西绑在一起:

public static void main(String[] args) throws SQLException {
            logger.info("Running preflight operations");
            GenericContainer mySql = startDatabaseContainer();
            runMigrations(mySql);
            generateJooqCode(mySql);
            connection.close();
            mySql.close();
    }

因此，我们可以拥有一个完全自给自足的项目，至少从数据库的角度来看是这样的。这可以在开发机器或构建服务器上运行。

总的来说，您需要一个自包含、自持续的软件项目工具包，它可以

  1. 可移植性随着数据库模式的变化而发展

  2. 在任何地方运行它的测试——在开发人员的机器上，在构建管道中，在拉请求合并之前，等等。

  3. 在没有相关开销的情况下，针对类似生产的软件和基础架构验证您的假设

  4. 向您保证数据库相关代码的语法正确性

因为毕竟这是现代软件开发的全部内容。

祝你好运，感谢你的阅读！

posted @ 2024-08-19 17:28  绝不原创的飞龙  阅读(5)  评论(0)  编辑  收藏  举报