使用jOOQ DSL

本文是我们学院课程的一部分，标题为jOOQ –类型安全的数据库查询 。

在SQL和特定关系数据库很重要的Java应用程序中，jOOQ是一个不错的选择。 当JPA / Hibernate抽象过多，JDBC过多时，这是一种替代方法。 它显示了一种现代的领域特定语言如何可以极大地提高开发人员的生产率，从而将SQL内部化为Java。

在本课程中，我们将看到如何使用jOOQ有效地查询数据库。 在这里查看 ！

也可以从org.jooq.academy.section1包中获得本节中显示的示例 。

1. jOOQ DSL的思想

jOOQ是一种DSL（领域特定语言），它模仿Java API中的标准和特定于供应商的SQL语法。 该API背后的思想很容易理解：

• 作为内部 DSL，Java编译器可以验证您的SQL查询的语法正确性（例如，SQL关键字的正确顺序）
• 将表和列作为生成的Java对象，编译器还可以验证元数据的正确性（例如，正确的列名和类型）

换句话说，当您要表达这样的SQL查询时：

SELECT   author.first_name, author.last_name
FROM     author
ORDER BY author.id

…然后您可以立即使用jOOQ编写相同的查询

select  (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.from   (AUTHOR)
.orderBy(AUTHOR.ID);

它是如何工作的，jOOQ和您的Java编译器如何知道“ select ”的含义或“ AUTHOR.FIRST_NAME ”的含义？

2.运行第一个查询

在上面的示例中，进行了两个简单的假设，并将在整个过程中进行以下假设：

• 每当您看到独立的SQL关键字时，都可能是从org.jooq.impl.DSL静态导入的。
• 每当您看到独立的表引用时，它就可能是从生成的Tables类中静态导入的

换句话说，理想情况下，在每个使用jOOQ的类中，只需添加以下两个import语句：

import static org.jooq.example.db.h2.Tables.*;
import static org.jooq.impl.DSL.*;

这将使上面的代码编译。 但是，这样的Select语句并不能做很多事情，它只是放在那儿，可以打印到控制台上：

System.out.println(
    select  (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
    .from   (AUTHOR)
    .orderBy(AUTHOR.ID)
);

上面将打印：

select "PUBLIC"."AUTHOR"."FIRST_NAME", "PUBLIC"."AUTHOR"."LAST_NAME" from "PUBLIC"."AUTHOR" order by "PUBLIC"."AUTHOR"."ID" asc

执行这样的查询非常简单。 我们需要做的就是为其提供JDBC Connection ，然后在其上调用fetch() ：

DSL.using(connection)
   .select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch();

注意，当然，您也可以将对象分配给局部变量，或者使用Spring或您喜欢的配置框架来配置它，而不是一直重复DSL.using(...) ：

DSLContext dsl = DSL.using(connection);

dsl.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch();

jOOQ将在内部创建一个新的JDBC PreparedStatement ，执行它，使用JDBC ResultSet ，并急切地关闭它创建的所有资源。 结果对象是Result ，它也实现了一个非常有用的toString()方法：

System.out.println(
    dsl.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
       .from(AUTHOR)
       .orderBy(AUTHOR.ID)
       .fetch()
);

以上将产生：

+----------+---------+
|FIRST_NAME|LAST_NAME|
+----------+---------+
|George    |Orwell   |
|Paulo     |Coelho   |
+----------+---------+

3.其他语句类型

jOOQ本机支持每个DML SQL语句（以及一些DDL SQL语句），包括SELECT ， UPDATE ， INSERT ， DELETE ， MERGE 。 如果我们要创建，更新，删除新的AUTHOR记录，则可以编写以下SQL语句：

插

dsl.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .values(3, "Alfred", "Hitchcock")
   .execute();

请注意，我们现在将调用execute()而不是fetch() execute() ，它返回受影响的行数。

上面查询的一个有趣的方面是jOOQ使用很多Java泛型来确保查询中的类型安全。 例如，以下两个查询将产生编译错误：

dsl.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .values(3, "Alfred")
// ^^^^^^^ values() expects three arguments (for ID, FIRST_NAME, LAST_NAME),
//                  but only two were provided!
   .execute()

这非常强大，因为您将永远不会忘记根据INTO子句的要求向VALUES子句添加相等数量的值。 这也扩展到类型不匹配。 如果您决定对INTO子句中的列进行重新排序，但忘了改写VALUES子句，则您的Java编译器可能会再次抱怨。 以下内容无法编译：

dsl.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, AUTHOR.ID)
   .values(4, "Alfred", "Hitchcock")
// ^^^^^^^ values() expects arguments of type (String, String, Integer),
                    but (Integer, String, String) was provided!
   .execute()

更新

UPDATE语句与前面的语句一样简单：

dsl.update(AUTHOR)
   .set(AUTHOR.DATE_OF_BIRTH, Date.valueOf("1899-08-13"))
   .where(AUTHOR.ID.eq(3))
   .execute()

到目前为止，选择ID = 3的作者将产生完整的AUTHOR记录：

dsl.select()
   .from(AUTHOR)
   .where(AUTHOR.ID.eq(3))
   .fetch()

……屈服

+----+----------+---------+-------------+
|  ID|FIRST_NAME|LAST_NAME|DATE_OF_BIRTH|
+----+----------+---------+-------------+
|   3|Alfred    |Hitchcock|1899-08-13   |
+----+----------+---------+-------------+

删除

最后但并非最不重要的一点是， DELETE语句如下：

dsl.delete(AUTHOR)
   .where(AUTHOR.ID.eq(3))
   .execute()

4.谓词

谓词是SQL（尤其是动态SQL）的重要方面。 SQL知道各种谓词，例如：

日常谓词

• 比较谓词
• NULL谓词
• 像谓词
• IN谓词
• 存在谓词

更高级的谓词

• 量化比较谓词
• DISTINCT谓词
• 谓词之间
• 重叠谓词

同样，在SQL中，可以使用AND和OR轻松组合谓词。 jOOQ以一种流利的方式直接在列类型上反映了所有这些谓词。 最好通过示例解释：

比较谓词

// A filtering predicate
AUTHOR.ID.eq(3);

// A join predicate
AUTHOR.ID.eq(BOOK.AUTHOR_ID);

这些谓词中的大多数也都是类型安全的，例如，您不能将数字与字符串进行比较。

AUTHOR.ID.eq("abc");
//        ^^ Compilation error. An expression of type Integer is expected
//           (or Field<Integer>, or Select<? extends Record1<Integer>>)

IN谓词是类型安全的一个有趣案例，它需要一个值列表或一个在IN关键字右侧仅包含一列的子查询：

// IN list
AUTHOR.ID.in(1, 2, 3);

// IN with subquery
AUTHOR.ID.in(select(BOOK.AUTHOR_ID).from(BOOK))

第二个示例在AUTHOR表和BOOK表之间执行半AUTHOR ，仅返回至少写过一本书的作者。 由于使用Java泛型，因此以下查询将无法编译：

// IN list with wrong types
AUTHOR.ID.in("a", "b", "c");
//           ^^^^^^^^^^^^^ This in() method expects an Integer... argument

// IN with subquery returning wrong type
AUTHOR.ID.in(select(BOOK.TITLE).from(BOOK))
//           ^^^^^^^^^^^^^^^^^^ This in() method expects a   Select<? extends Record1<Integer>>
//

另外，以下（无效）语句将被Java编译器拒绝：

AUTHOR.ID.in(select(BOOK.AUTHOR_ID, BOOK.TITLE).from(BOOK))
//        ^^ This in() method expects a   Select<? extends Record1<Integer>>,
//           but instead, an incompatible Select<Record2<Integer, String>> was provided

5.列表达式

在SQL中，您可以通过对列表达式应用函数或操作来创建新型的列表达式。 例如，您可以串联作者的名字和姓氏：

在SQL中：

SELECT   author.first_name || ' ' || author.last_name
FROM     author
ORDER BY author.id

使用jOOQ ::

dsl.select(concat(AUTHOR.FIRST_NAME, val(" "), AUTHOR.LAST_NAME))
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch()

请记住，我们是从DSL静态导入所有内容，包括DSL.concat()和DSL.val() 。

dsl.select(AUTHOR.FIRST_NAME.concat(" ").concat(AUTHOR.LAST_NAME))
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch()

当然，由于Java不允许运算符重载（或符号方法名称），因此我们必须处理常规方法名称，在这种情况下，应使用concat 。 通常是由您决定要对函数使用前缀符号，还是对运算符使用前缀符号。

6.有关jOOQ DSL的更多信息

jOOQ DSL具有非常丰富的功能。 列出本课程中的所有功能将重复参考手册。 请考虑手册中有关SQL构建的部分，以了解有关jOOQ DSL的更多信息。

翻译自: https://www.javacodegeeks.com/2015/09/work-with-the-jooq-dsl.html