jOOQ 是基于 JDBC 之上的一个抽象层，提供了多种多样的模型来与关系型数据库进行互操作；其使用与 mybatis 和 Hibernate ORM 不同的思路来实现 对象关系映射 ORM 。

JOOQ 的使用 - 代码生成配置 (PostgreSQL & DDL Driven) 介绍了使用 jOOQ 为数据库表生成实体类代码；JOOQ 的使用 - 拼接 SQL 语句 (SQL Builder) 介绍了基于 jOOQ 的 SQL 语句拼接；作为对比，本篇主要介绍基于生成代码的 SQL 语句拼接 (SQL Builder) 。

🔗Generated Classes

本文使用了 代码生成 里面的基于 PostgreSQL 实例驱动生成的代码，代码结构如下：

├── tables
│   ├── daos
│   │   ├── AuthorDao.java
│   │   └── BookDao.java
│   ├── interfaces
│   │   ├── IAuthor.java
│   │   └── IBook.java
│   ├── pojos
│   │   ├── Author.java
│   │   └── Book.java
│   ├── records
│   │   ├── AuthorRecord.java
│   │   └── BookRecord.java
│   ├── Author.java
│   └── Book.java
├── DefaultCatalog.java
├── Indexes.java
├── Keys.java
├── Public.java
├── Sequences.java
└── Tables.java

🔗SQL Building

🔗Common

所有的 SQL 生成用例都使用了 JUnit 5 的参数化测试能力，参数化的数据为 6 种不同的关系型数据库方言。

公共代码：

// junit 5 parameterized test data source
static Stream<Arguments> dslContexts() {
    return Stream.of(
        Arguments.of(SQLDialect.DEFAULT),
        Arguments.of(SQLDialect.H2),
        Arguments.of(SQLDialect.HSQLDB),
        Arguments.of(SQLDialect.POSTGRES),
        Arguments.of(SQLDialect.MYSQL)
    );
}

🔗select

使用生成类来生成 select 的 SQL：

@ParameterizedTest
@MethodSource("dslContexts")
void test_buildSelect(final SQLDialect dialect) {
    final DSLContext dsl = DSL.using(dialect);

    final String sql = dsl
        .select(Tables.AUTHOR.ID, Tables.AUTHOR.FIRST_NAME,
                Tables.AUTHOR.LAST_NAME, Tables.BOOK.TITLE)
        .from(Tables.AUTHOR.as("u"))
        .join(Tables.BOOK.as("b"))
        .on(Tables.AUTHOR.ID.eq(Tables.BOOK.AUTHOR_ID))
        .where(Tables.AUTHOR.FIRST_NAME.eq("Elvis"))
        .and(Tables.AUTHOR.LAST_NAME.eq("Wang"))
        .getSQL(ParamType.INLINED);
    System.out.println(dialect.getName() + " => " + sql);
}

生成的 select 语句简单对比如下：

  • SQL:1999

    select "PUBLIC"."AUTHOR"."ID",
    "PUBLIC"."AUTHOR"."FIRST_NAME",
    "PUBLIC"."AUTHOR"."LAST_NAME",
    "PUBLIC"."BOOK"."TITLE"
from "PUBLIC"."AUTHOR" "u"
join "PUBLIC"."BOOK" "b"
on "PUBLIC"."AUTHOR"."ID" = "PUBLIC"."BOOK"."AUTHOR_ID"
where ("PUBLIC"."AUTHOR"."FIRST_NAME" = 'Elvis'
and "PUBLIC"."AUTHOR"."LAST_NAME" = 'Wang')

  • H2

    select "PUBLIC"."AUTHOR"."ID",
    "PUBLIC"."AUTHOR"."FIRST_NAME",
    "PUBLIC"."AUTHOR"."LAST_NAME",
    "PUBLIC"."BOOK"."TITLE" from "PUBLIC"."AUTHOR" "u"
join "PUBLIC"."BOOK" "b"
on "PUBLIC"."AUTHOR"."ID" = "PUBLIC"."BOOK"."AUTHOR_ID"
where ("PUBLIC"."AUTHOR"."FIRST_NAME" = 'Elvis'
and "PUBLIC"."AUTHOR"."LAST_NAME" = 'Wang')

  • HSQLDB

    select "PUBLIC"."AUTHOR"."ID",
    "PUBLIC"."AUTHOR"."FIRST_NAME",
    "PUBLIC"."AUTHOR"."LAST_NAME",
    "PUBLIC"."BOOK"."TITLE" from "PUBLIC"."AUTHOR" as "u"
join "PUBLIC"."BOOK" as "b"
on "PUBLIC"."AUTHOR"."ID" = "PUBLIC"."BOOK"."AUTHOR_ID"
where ("PUBLIC"."AUTHOR"."FIRST_NAME" = 'Elvis'
and "PUBLIC"."AUTHOR"."LAST_NAME" = 'Wang')

  • MySQL

    select `PUBLIC`.`AUTHOR`.`ID`,
    `PUBLIC`.`AUTHOR`.`FIRST_NAME`,
    `PUBLIC`.`AUTHOR`.`LAST_NAME`,
    `PUBLIC`.`BOOK`.`TITLE`
from `PUBLIC`.`AUTHOR` as `u`
join `PUBLIC`.`BOOK` as `b`
on `PUBLIC`.`AUTHOR`.`ID` = `PUBLIC`.`BOOK`.`AUTHOR_ID`
where (`PUBLIC`.`AUTHOR`.`FIRST_NAME` = 'Elvis'
and `PUBLIC`.`AUTHOR`.`LAST_NAME` = 'Wang')

  • Postgres

    select "PUBLIC"."AUTHOR"."ID",
    "PUBLIC"."AUTHOR"."FIRST_NAME",
    "PUBLIC"."AUTHOR"."LAST_NAME",
    "PUBLIC"."BOOK"."TITLE"
from "PUBLIC"."AUTHOR" as "u"
join "PUBLIC"."BOOK" as "b"
on "PUBLIC"."AUTHOR"."ID" = "PUBLIC"."BOOK"."AUTHOR_ID"
where ("PUBLIC"."AUTHOR"."FIRST_NAME" = 'Elvis'
and "PUBLIC"."AUTHOR"."LAST_NAME" = 'Wang')

🔗insert

使用生成类来生成 insert 的 SQL：

@ParameterizedTest
@MethodSource("dslContexts")
void test_buildInsert(final SQLDialect dialect) {
    final DSLContext dsl = DSL.using(dialect);

    final String sql = dsl
        .insertInto(Tables.AUTHOR)
        .columns(Tables.AUTHOR.FIRST_NAME, Tables.AUTHOR.LAST_NAME)
        .values("Elvis", "Wang")
        .getSQL(ParamType.INLINED);
    System.out.println(dialect.getName() + " => " + sql);
}

生成的 insert 语句简单对比如下：

  • SQL:1999

    insert into "PUBLIC"."AUTHOR"
    ("FIRST_NAME", "LAST_NAME")
values ('Elvis', 'Wang')

  • H2

    insert into "PUBLIC"."AUTHOR"
    ("FIRST_NAME", "LAST_NAME")
values ('Elvis', 'Wang')

  • HSQLDB

    insert into "PUBLIC"."AUTHOR"
    ("FIRST_NAME", "LAST_NAME")
values ('Elvis', 'Wang')

  • MySQL

    insert into `PUBLIC`.`AUTHOR`
    (`FIRST_NAME`, `LAST_NAME`)
values ('Elvis', 'Wang')

  • Postgres

    insert into "PUBLIC"."AUTHOR"
    ("FIRST_NAME", "LAST_NAME")
values ('Elvis', 'Wang')

🔗update

使用生成类来生成 update 的 SQL：

@ParameterizedTest
@MethodSource("dslContexts")
void test_buildUpdate(final SQLDialect dialect) {
    final DSLContext dsl = DSL.using(dialect);

    final String sql = dsl
        .update(Tables.AUTHOR)
        .set(Tables.AUTHOR.FIRST_NAME, "Elvis")
        .set(Tables.AUTHOR.LAST_NAME, "Wang")
        .where(Tables.AUTHOR.ID.eq(1993L))
        .getSQL(ParamType.INLINED);
    System.out.println(dialect.getName() + " => " + sql);
}

生成的 update 语句简单对比如下：

  • SQL:1999

    update "PUBLIC"."AUTHOR"
set "PUBLIC"."AUTHOR"."FIRST_NAME" = 'Elvis',
    "PUBLIC"."AUTHOR"."LAST_NAME" = 'Wang'
where "PUBLIC"."AUTHOR"."ID" = 1993

  • H2

    update "PUBLIC"."AUTHOR"
set "PUBLIC"."AUTHOR"."FIRST_NAME" = 'Elvis',
    "PUBLIC"."AUTHOR"."LAST_NAME" = 'Wang'
where "PUBLIC"."AUTHOR"."ID" = 1993

  • HSQLDB

    update "PUBLIC"."AUTHOR"
set "PUBLIC"."AUTHOR"."FIRST_NAME" = 'Elvis',
    "PUBLIC"."AUTHOR"."LAST_NAME" = 'Wang'
where "PUBLIC"."AUTHOR"."ID" = 1993

  • MySQL

    update `PUBLIC`.`AUTHOR`
set `PUBLIC`.`AUTHOR`.`FIRST_NAME` = 'Elvis',
    `PUBLIC`.`AUTHOR`.`LAST_NAME` = 'Wang'
where `PUBLIC`.`AUTHOR`.`ID` = 1993

  • Postgres

    update "PUBLIC"."AUTHOR"
set "FIRST_NAME" = 'Elvis',
    "LAST_NAME" = 'Wang'
where "PUBLIC"."AUTHOR"."ID" = 1993

🔗delete

使用生成类来生成 delete 的 SQL：

@ParameterizedTest
@MethodSource("dslContexts")
void test_buildDelete(final SQLDialect dialect) {
    final DSLContext dsl = DSL.using(dialect);

    final String sql = dsl
        .delete(Tables.AUTHOR)
        .where(Tables.AUTHOR.ID.eq(1993L))
        .getSQL(ParamType.INLINED);
    System.out.println(dialect.getName() + " => " + sql);
}

生成的 delete 语句简单对比如下：

  • SQL:1999

    delete from "PUBLIC"."AUTHOR"
where "PUBLIC"."AUTHOR"."ID" = 1993

  • H2

    delete from "PUBLIC"."AUTHOR"
where "PUBLIC"."AUTHOR"."ID" = 1993

  • HSQLDB

    delete from "PUBLIC"."AUTHOR"
where "PUBLIC"."AUTHOR"."ID" = 1993

  • MySQL

    delete from `PUBLIC`.`AUTHOR`
where `PUBLIC`.`AUTHOR`.`ID` = 1993

  • Postgres

    delete from "PUBLIC"."AUTHOR"
where "PUBLIC"."AUTHOR"."ID" = 1993

完整的示例代码可以参见 jOOQ Usecases 。

想了解更多的 jOOQ 用法，可以阅读 官方文档 。

可以明显感觉到，使用 jOOQ 生成的代码为 SQL 拼接提供了更强的类型安全保证。

以上。