首页 > 其他分享 >JOOQ-入门手册-全-

JOOQ-入门手册-全-

时间：2024-10-01 20:45:18浏览次数：6  
标签：JOOQ 入门 jOOQ 数据库 vehicle 手册 jooq SQL VEHICLE

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

橡皮鸭调试:真人电影！

相关文章

  • JavaScript-正则表达式入门指南-全-
    JavaScript正则表达式入门指南（全）原文：IntroducingRegularExpressions协议：CCBY-NC-SA4.0一、正则表达式简介为了开始介绍正则表达式，我将从一个例子开始。这是一个你已经经历了几百次的问题。当您在线输入客户数据时，许多web表单会要求您提供电子邮件地址。为了避免输入......
  • python - 合理的入门编程语言
    盗版资源我就一个人独享了，分享的大部分为“开源”，不小心则为侵权。当两国战争后，谁在乎“侵权”？编程语言心法参考：http://www.yinwang.org/blog-cn/2017/07/06/master-pl英语阅读速成：http://www.yinwang.org/blog-cn/2018/11/23/grammar文档部分：教程https://docs.python.org/3......
  • 《Java 基础篇》一：入门
    Author:ACatSmilingSince:2024-09-30bit和byte计算机本质是一系列的电路开关。每个开关存在两种状态：开（on）和关（off）。如果电路是开的，它的值是1，如果电路是关的，它的值是0。一个0或者一个1存储为一个比特（bit），是计算机中最小的存储单位。计算机中最基本的存储单元是......
  • Linux 万字入门教程
    0.前言文章已经收录到GitHub个人博客项目，欢迎Star：https://github.com/chenyl8848/chenyl8848.github.io或者访问网站，进行在线浏览：https://chenyl8848.github.io/1.Linux介绍1.1引言Linux是一套免费使用和自由传播的类Unix操作系统，是一个基于POSIX和Unix......
  • C++入门基础知识91（实例）——实例16【求两数最小公倍数】
    成长路上不孤单......
  • 微积分快速入门5部分：基本算术、规律及花式算术
    12微积分的基本算术12.1加法12.2乘法12.3简单除法（倒数）你们原来的份额是1/x(当x=2时，你有1/2）。有人进来你的新份额变成1/(x+1)你的蛋糕数量是如何变化的？在求出总变化（及其恼人的代数）后，我们除以dx，就得到了“每dx”的变化：现在，我们去掉剩余的dx，......
  • Web入门 ——生成验证码
    <!DOCTYPEhtml><htmllang="zh-CN"><head><metacharset="UTF-8"><metaname="viewport"content="width=device-width,initial-scale=1.0"><title>验证码</title>......
  • SQLite 入门教程
    SQLite入门教程 不是MySQL用不起，而是SQLite更有性价比，绝大多数的Web应用SQLite都可以满足。SQLite 是一个用C语言编写的开源、轻量级、快速、独立且高可靠性的SQL数据库引擎，它提供了功能齐全的数据库解决方案。SQLite几乎可以在所有的手机和计算机上运行，......
  • Qt 入门
    1、概述1.1、什么是QtQt是一个跨平台的C++图形用户界面应用程序框架，它为应用程序开发者提供建立艺术级图形界面的所有功能，它是完全面向对象的，很容易扩展，并且允许真正的组件编程。1.2、Qt的发展史1991年Qt最早由奇趣科技开发；1996年进入商业领域，它也是目前流行的Linux桌面环境K......
  • Docker入门实践（五）
    什么是容器？定义容器是每个应用组件的独立进程，它完全和你机器的其他东西隔离。独立性：每个容器包含所有它需要的功能，不依赖于主机上预安装的依赖项。隔离行：由于容器是隔离运行的，它们对主机和其他容器的影响最小，增加了应用的安全性。独立性：每个容器独立管理，删除一个容器不会......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99