jooq_使用jOOQ DSL

深藏阁楼爱情的钟 2023-02-25 03:45 5阅读 0赞

jooq

*本文是我们学院课程“ jOOQ –类型安全数据库查询”的一部分 。*

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

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

#### **目录** ####

1. jOOQ DSL的思想 2.运行第一个查询 3.其他语句类型

3.1。 插 3.2。 更新 3.4。 删除

4.谓词

4.1。 日常谓词 4.2。 更高级的谓词 4.3。 比较谓词

5.列表达式

5.1。 在SQL中： 5.2。 使用jOOQ ::

6.有关jOOQ DSL的更多信息

也可以从[org.jooq.academy.section1][]包中获得本节中显示的[示例][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()
    );

以上将产生：

## 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知道各种谓词，例如：

### 日常谓词 ###

*  [比较谓词][Link 1]
 *  [NULL谓词][NULL]
 *  [像谓词][Link 2]
 *  [IN谓词][IN]
 *  [存在谓词][Link 3]

### 更高级的谓词 ###

*  [量化比较谓词][Link 4]
 *  [DISTINCT谓词][DISTINCT]
 *  [谓词之间][Link 5]
 *  [重叠谓词][Link 6]

同样，在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构建的部分，][SQL]以了解有关jOOQ DSL的更多信息。

> 翻译自: [https://www.javacodegeeks.com/2015/09/work-with-the-jooq-dsl.html][https_www.javacodegeeks.com_2015_09_work-with-the-jooq-dsl.html]

jooq

[org.jooq.academy.section1]: https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-academy/src/test/java/org/jooq/academy/section1
[Link 1]: http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/comparison-predicate/
[NULL]: http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/null-predicate/
[Link 2]: http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/like-predicate/
[IN]: http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/in-predicate/
[Link 3]: http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/exists-predicate/
[Link 4]: http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/quantified-comparison-predicate/
[DISTINCT]: http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/distinct-predicate/
[Link 5]: http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/between-predicate/
[Link 6]: http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/overlaps-predicate/
[SQL]: http://www.jooq.org/doc/latest/manual/sql-building/
[https_www.javacodegeeks.com_2015_09_work-with-the-jooq-dsl.html]: https://www.javacodegeeks.com/2015/09/work-with-the-jooq-dsl.html