ORM的小清新(2)——JOOQ查询功能-随遇而安

JOOQ目前支持5种SQL语句类型，这些语句类型均通过DSLContext的选择搭配生成。语句的执行依赖于数据库连接和数据源。

我们首先来看下一个涉及面比较广的SQL语句和JOOQ语句的对比：

SQL语句：

SELECT * 
FROM role as r
JOIN `function` as f
ON r.id = f.role_id
WHERE r.id = 1
AND r.name = 'Sunshine'
ORDER BY r.id
LIMIT 100;

JOOQ语句：

Role r = ROLE.as("r");
Function f = FUNCTION.as("f");
Result<Record> recordResult =
        dslContext.select()
                .from(ROLE.as("r"))
                .join(FUNCTION.as("f"))
                .on(r.ID.eq(f.ROLE_ID))
                .where(r.ID.eq(1))
                .and(r.NAME.eq("Sunshine"))
                .orderBy(f.NAME)
                .offset(0)
                .limit(100)
                .fetch();

仿真！

这就是JOOQ的特点，你可以在Java程序中像写SQL语句那样写执行语句。

你会说很多著名的ORM框架也拥有类似的API使用，比如说这样？

@Select("SELECT * FROM role r JOIN function f WHERE r.id = 1 AND r.name = 'Sunshine' ORDER BY r.id LIMIT 100")

亦或是这样？

public String findUser() {
    return new SQL() {
        {
            SELECT();
            FROM("role r");
            JOIN("funtion f");
            WHERE("r.id = 1");
            WHERE("r.name = 'Sunshine'");
            ORDER_BY("r.id");
        }
    }.toString();
}

看似没什么问题，编译通过了，部分数据库运行起来没有问题（比如MySQL），但是当你在分析结果集时，会发现，数据怎么不对了，爬过坑的人可能瞬间定位到问题的根本。

没有JOIN…ON连接。

作为身经百战，上到熟知各种中间件源码，下到生产各种高大上的轮子的你，小case啦，这种问题不会出现在你的身上啦。

为什么不避免这个问题。

JOOQ结合先进的IDE给出了较优的解决方案，如下情况会进行提示，在编译期时就将错误按死在萌芽之中。

Result<Record> recordResult =
        dslContext.select()
                .from(ROLE.as("r"))
                .join(FUNCTION.as("f"))
                // join后不能接where
                .where(r.ID.eq(1))
                .fetch();
Result<Record> recordResult =
        dslContext.select()
                // select后不能接join
                .join(FUNCTION.as("f"))
                .on(r.ID.eq(f.ROLE_ID))
                .from(ROLE.as("r"))
                .where(r.ID.eq(1))
                .and(r.NAME.eq("Sunshine"))
                .fetch();

除了这种方式，JOOQ还支持和其他框架一样的面向对象SQL构建，这也就意味着所有的句法结构都会封装成一个对象然后放入到查询中，这个查询是一个SQL翻译和参数绑定的代理，这是一种向过去妥协的传统API，示例如下：

boolean isJoin = true;
SelectQuery query = dslContext.selectQuery();
query.addFrom(ROLE);
if (isJoin) {
    query.addJoin(FUNCTION);
}
query.fetch();

WITH

为了可以使用通用表表达式，在SQL-1999标准中，指定了WITH子句可以作为SELECT语句的可选择子句。比如在PostgreSQL或是SQL Server等数据库来说，它们也允许在其他DML语句（INSERT、UPDATE、DELETE或者是MERGE）中使用WITH子句，通用表表达式也可以简称为CTE。

如果在JOOQ中使用CTE，那么本质上有两种方法：

在SELECT语句之前声明一个明确到名称的CTE。
接将CTE插入到SELECT子句中。

WITH一般用法WITH AS，你可以将其视为一个语句片段、临时表或是子查询部分，我们可以使用WITH AS子句使我们的SQL更可读。

明确的CTE

我们来看一下易于理解的、相对标准的WITH子句写法，因为这样不会影响普通SELECT语句的正常语序：

# SQL语句
WITH "t1" ("f1", "f2") AS (SELECT 1, 'a'),
     "t2" ("f3", "f4") AS (SELECT 2, 'b')
SELECT
    "t1"."f1" + "t2"."f3" AS "add",
    "t1"."f2" || "t2"."f4" AS "concat"
FROM "t1", "t2";

那么在JOOQ的世界里，它们是这样的：

// JOOQ代码
CommonTableExpression<Record2<Integer, String>> t1 =
        name("t1").fields("f1", "f2").as(select(val(1), val("a")));
CommonTableExpression<Record2<Integer, String>> t2 =
        name("t2").fields("f3", "f4").as(select(val(2), val("b")));
dslContext.with(t1)
        .with(t2)
        .select(
                t1.field("f1").add(t2.field("f3")).as("add"),
                t1.field("f2").concat(t2.field("f4")).as("concat"))
        .from(t1, t2)
        .fetch();

由于org.jooq.CommonTableExpression类型是继承自org.jooq.Tables，所以CTE可以用于任何一个table可以使用的地方。

嵌入的CTE

我们也可以直接在我们的SQL中嵌入CTE：

# SQL语句
WITH a AS(SELECT * FROM "user")
SELECT * from a WHERE "id" = 1;

// JOOQ代码
dslContext.with("test").as(select().from(USER))
        .select()
        .from(table(name("test")))
        .where(USER.ID.eq(1))
        .fetch();

目前支持WITH AS子句的数据库：

PostgreSQL

FireBird

HSQLDB

H2

SQL Server

SELECT

SELECT是一个使用频繁的短语，除非是简单的CRUD，否则SELECT后面会定制出很多不同特性的语句。

复杂查询

在本篇文章开头部分举例，其实就是一个复杂查询，我们重新看一下。

SQL语句：

SELECT * 
FROM role as r
JOIN `function` as f
ON r.id = f.role_id
WHERE r.id = 1
AND r.name = 'Sunshine'
ORDER BY r.id
LIMIT 100;

JOOQ代码：

Role r = ROLE.as("r");
Function f = FUNCTION.as("f");
Result<Record> recordResult =
        dslContext.select()
                .from(ROLE.as("r"))
                .join(FUNCTION.as("f"))
                .on(r.ID.eq(f.ROLE_ID))
                .where(r.ID.eq(1))
                .and(r.NAME.eq("Sunshine"))
                .orderBy(f.NAME)
                .offset(0)
                .limit(100)
                .fetch();

单表查询

如果你只是想单纯的检索单表的行记录或者是更新记录，在使用DSL的前提下，你也可以这样写：

dslContext.selectFrom(ROLE)
          .where(ROLE.NAME.eq("Sunshine"))
          .orderBy(ROLE.ID.desc())
          .fetch();

使用的DSL方法是：

@Support
<R extends Record> SelectWhereStep<R> selectFrom(Table<R> table);

查询

接下来我们详细介绍下SELECT短语的诸多用法。

基本用法

SQL语句：

SELECT id, name FROM role;

JOOQ代码：

Select selectOne = dslContext.select(ROLE.ID, ROLE.NAME);

一些常用的查询我们可以使用JOOQ便捷的方法：

# SQL语句
SELECT COUNT(*) FROM role;
SELECT 1 FROM role;

// JOOQ代码
dslContext.selectCount().from(ROLE).fetch();
dslContext.selectOne().from(ROLE).fetch();

DISTINCT

# SQL语句
SELECT DISTINCT NAME FROM role

// JOOQ代码
dslContext.selectDistinct(ROLE.NAME).from(ROLE).fetch();

SELECT *

JOOQ并没有明确的支持在SQL构建中使用*分隔符代替查询所有字段，但是我们可以直接使用方法进行映射：

# SQL代码
SELECT *
FROM
role;

// JOOQ代码
dslContext.select().from(ROLE).fetch();
dslContext.select().from(table(name("role"))).fetch();

字段安全类型容量已经上升至22个

从JOOQ3.0开始，record和row value expression已将类型安全容量已经上升到22个。

字段类型安全绑定主要用防止溢出查询，22这个数字比较诡异，笔者查阅资料发现可能与scala的tuple有关。

那么什么是类型安全的查询？我们参照源码进行一下对比：

// 非类型安全的查询
public static SelectSelectStep<Record> select(Collection<? extends Field<?>> fields);
public static SelectSelectStep<Record> select(Field<?>... fields);

// 类型安全的查询
public static <T1>         SelectSelectStep<Record1<T1>>         select(Field<T1> field1);
public static <T1, T2>     SelectSelectStep<Record2<T1, T2>>     select(Field<T1> field1, Field<T2> field2);
public static <T1, T2, T3> SelectSelectStep<Record3<T1, T2, T3>> select(Field<T1> field1, Field<T2> field2, Field<T3> field3);

其实也就是我们常说的参数绑定，可以根据需求，对泛型进行绑定：

Select<Record1<Integer>> select = dslContext.select(ROLE.ID).from(ROLE);

Select<Record2<Integer, String>> select = dslContext.select(ROLE.ID, ROLE.NAME).from(ROLE);

FROM

FROM短语，顾名思义，指定了查询数据的来源Table表达式：

# SQL语句
SELECT * FROM role;
SELECT * FROM role, `function`;
SELECT * FROM role as r, `function` as f;

// JOOQ代码
Role r = ROLE.as("r");
Function f = FUNCTION.as("f");
dslContext.select().from(ROLE);
dslContext.select().from(ROLE, FUNCTION);
dslContext.select().from(ROLE.as("r"), FUNCTION.as("f"));

更高级的Table表达式

除了简单表，你也可以使用FROM构建任意的Table表达式，比如我们可以在Oracle中查询一个SQL语句的真实执行计时：

# SQL语句
SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS')
);

// JOOQ语句
dslContext.select()
      .from(table(
          DbmsXplan.displayCursor(null, null, "ALLSTATS")
      ).fetch();

如果想要使用DbmsXplan包，那么可以使用JOOQ的代码生成功能生成Oracle的SYS schema。

从DUAL表中查询数据

DUAL表是什么？

在MySQL中，如果你的语句没有表需要引用，那么可以指定DUAL为一个傀儡表。

Oracle中，DUAL是一个虚拟表，Oracle保证dual里面永远只有一条数据，其实也是用于没有表引用时，指定一个表。

JOOQ允许忽略FROM短语，然后只返回一条记录：

# SQL语句
SELECT 1 FROM DUAL
SELECT 1

// JOOQ代码
DSL.using(SQLDialect.ORACLE).selectOne().fetch();
DSL.using(SQLDialect.POSTGRES).selectOne().fetch();

JOIN

JOOQ支持多种不同的标准或非标准的JOIN操作：

[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL OUTER JOIN
LEFT SEMI JOIN
LEFT ANTI JOIN
CROSS JOIN
NATURAL JOIN
NATURAL LEFT [OUTER] JOIN
NATURAL RIGHT [OUTER] JOIN

除此之外，JOOQ还支持：

CROSS APPLY (T-SQL and Oracle 12c specific)
OUTER APPLY (T-SQL and Oracle 12c specific)
LATERAL derived tables (PostgreSQL and Oracle 12c)
partitioned outer join

这些JOIN方法均可以被org.jooq.Tables类型调用或者直接在FROM短语之后进行链式调用：

// JOOQ代码
dslContext.select()
        .from(ROLE.join(FUNCTION).on(ROLE.ID.eq(FUNCTION.ROLE_ID)))
        .fetch();

dslContext.select()
        .from(ROLE)
        .join(FUNCTION)
        .on(ROLE.ID.eq(FUNCTION.ROLE_ID))
        .fetch();

以上两种JOIN写法，殊途同归，均会解析成同样的语句。然而，如果你决意使用org.jooq.Tables的方式调用JOIN，恭喜你，你需要挑战一下非常多的、嵌套的JOIN表达式和眼花缭乱的圆括号。

JOOQ捷径：ON KEY

令人惊讶的是，目前SQL标准还没有正式支持直接使用我们的熟悉的外键进行关联信息查询。

日常，如果我们想要连接两个表，一般是基于两个表的外键进行关联。如果不这么做，那么我们会多写一些罗里吧嗦的代码，尤其是你的外键有很多列时。但是，如果你使用JOOQ，你可以直接在你的JOIN表达式中使用你的外键约束信息：

# SQL语句
SELECT *
FROM
role 
JOIN `function` 
ON role.id = `function`.role_id;

// JOOQ代码
dslContext.select()
        .from(ROLE)
        .join(FUNCTION)
        .onKey()
        .fetch();

为了防止模棱两可，你可以为你的外键提供引用，或者直接使用onKey()生成一个外键引用。

Sybase正式支持JOIN ON KEY已经在规划中。

奇淫技巧：USING

日常，我们需要经常进行JOIN…ON操作，而可能两个表中需要关联的字段是相同的，我们可能会写这样的代码：

SELECT * 
FROM `user`
JOIN `function`
ON `user`.role_id = `function`.role_id;

。。。

到了面向对象编程，JOOQ对这一冗余代码进行了优化，我们仅需使用using()方法，即可省略重复列的ON操作。如果你的数据库schema具有高度一致的关系标准化，那么using()将会非常好用：

// JOOQ代码
dslContext.select()
        .from(USER)
        .join(FUNCTION)
        .using(FUNCTION.ROLE_ID)
        .fetch();

我们刚刚提到列的高度一致性，你可能会使用NATURAL JOIN子句，NATURAL JOIN子句会使用两个表中字段名相同的部分进行判断：

# SQL语句
SELECT * 
FROM `user`
NATURAL JOIN `function`;

// JOOQ代码
dslContext.select()
        .from(USER)
        .naturalJoin(FUNCTION)
        .fetch();

Oracle的分段OUTER JOIN

Oracle有个特殊的OUTER JOIN语法，就是在LEFT OUTER JOIN或者RIGHT OUTER JOIN时，使用PARTITION BY对指定字段所在行数据进行分区，分区后再在每个分区中进行相应的OUTER JOIN，最后整合为一个数据集。JOOQ也支持这种语法，但是只能将这个字段放在OUTER JOIN的右侧：

# SQL语句
SELECT *
FROM `user`
LEFT OUTER JOIN `function`
PARTITION BY (role_id)
ON `user`.role_id = `function`.role_id;

// JOOQ代码
dslContext.select()
		  .from(USER)
		  .leftOuterJoin(FUNCTION)
          .partitionBy(FUNCTION.ROLE_ID)
          .on(USER.ROLE_ID.eq(FUNCTION.ROLE_ID))
          .fetch();

SEMI JOIN和ANTI JOIN

寥寥无几的数据库支持[LEFT | RIGHT] SEMI JOIN或者[LEFT] | RIGHT] ANTI JOIN，这两种类型的子句用来优化SQL标准中IN / EXISTS和NOT IN / NOT EXISTS判断。

通常，我们会将IN / EXISTS / NOT IN / NOT EXISTS写在WHERE子句中，但是[LEFT | RIGHT] SEMI JOIN或者[LEFT] | RIGHT] ANTI JOIN中的JOIN指明，这两个子句将运用在FROM子句中。

[LEFT | RIGHT] SEMI JOIN解释为EXISTS：

# SQL语句
SELECT * 
FROM `user`
WHERE EXISTS
(SELECT 1 FROM `function` WHERE `user`.role_id = `function`.role_id);

// JOOQ代码
dslContext.select()
        .from(USER)
        .leftSemiJoin(FUNCTION)
        .on(USER.ROLE_ID.eq(FUNCTION.ROLE_ID))
        .fetch();

# SQL语句
SELECT * 
FROM `user`
WHERE EXISTS
(SELECT 1 FROM `function` WHERE `user`.role_id = `function`.role_id);

// JOOQ代码
dslContext.select()
        .from(USER)
        .leftSemiJoin(FUNCTION)
        .on(USER.ROLE_ID.eq(FUNCTION.ROLE_ID))
        .fetch();

[LEFT | RIGHT] ANTI JOIN解释为NOT EXISTS：

# SQL语句
SELECT * 
FROM `user`
WHERE NOT EXISTS
(SELECT 1 FROM `function` WHERE `user`.role_id = `function`.role_id);

// JOOQ代码
dslContext.select()
        .from(USER)
        .leftAntiJoin(FUNCTION)
        .on(USER.ROLE_ID.eq(FUNCTION.ROLE_ID))
        .fetch();

T-SQL的CROSS APPLY和OUTER APPLY

T-SQL使用APPLY关键完成了SQL标准中对lateral对派生表的连接，从左侧表表达式返回的每一行结果集，我们也可以直接使用右侧的表表达式。JOOQ也支持这种用法：

// JOOQ代码
dslContext.select(USER.USER_NAME, FUNCTION.NAME)
        .from(USER, 
                lateral(select()
                        .from(FUNCTION)
                        .where(USER.ROLE_ID.eq(FUNCTION.ROLE_ID))))
        .fetch();

上面的例子示意了LATERVAL关键字连接派生表和前表的用法，如果你的数据库支持T-SQL，你也可以这样写：

// JOOQ代码
dslContext.select(USER.USER_NAME, FUNCTION.NAME)
        .from(USER)
        .crossApply(
                select()
                .from(FUNCTION)
                .where(USER.ROLE_ID.eq(FUNCTION.ROLE_ID))
        )
        .fetch();

APPLY等价于先计算并得出左边表达式的数据集，然后再将左边的数据集一行一行的并入到右边的数据集，集中计算，返回最后结果。

CROSS和OUTER的区别在于，右边数据行如果没有对应左边数据集的行，那么：CROSS将不会显示该行。

OUTER将会继续显示该行，对应的右侧数据行显示为null。

不是所有的LATERAL JOIN都和APPLY语法都是等价的。

LATERAL JOIN和CROSS APPLY对于JOOQ的行表达式来说，也特别的好用。

WHERE

WHERE语句用于JOIN或者过滤判断，是为了限制之前指定的FROM短语或者JOIN短语返回的数据。

针对这样的SQL语句：

SELECT *
FROM role
WHERE id = 2
AND name = 'Sunshine'

我们的JOOQ的代码可以是这样：

dslContext.select()
        .from(ROLE)
        .where(ROLE.ID.eq(2))
        .and(ROLE.NAME.eq("Sunshine"))
        .fetch();

也可以是这样：

dslContext.select()
        .from(ROLE)
        .where(ROLE.ID.eq(2).and(ROLE.NAME.eq("Sunshine")))
        .fetch();

CONNECT BY

CONNECT BY一般用于Oracle中的树状查询。

Oracle数据由一种非常简洁明了的层级查询语法——CONNECT BY。当然，JOOQ必然会支持这种语法，并且支持的比较完全，包括了所有关联的函数和伪列，这个子句的定义如下：

--   SELECT ..
--     FROM ..
--    WHERE ..
CONNECT BY [ NOCYCLE ] condition [ AND condition, ... ] [ START WITH condition ]
-- GROUP BY ..
-- ORDER [ SIBLINGS ] BY ..

关于CONNECT BY的使用，有一个经常在某个区间能进行的重复查询：

# SQL语句
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 5

// JOOQ代码
dslContext.select(level())
        .connectBy(level().le(5))
        .fetch();

太简单？上难度：

# SQL语句
SELECT
  SUBSTR(SYS_CONNECT_BY_PATH(DIRECTORY.NAME, '/'), 2)
FROM DIRECTORY
CONNECT BY
  PRIOR DIRECTORY.ID = DIRECTORY.PARENT_ID
START WITH DIRECTORY.PARENT_ID IS NULL
ORDER BY 1

// JOOQ代码
dslContext.select(
   sysConnectByPath(DIRECTORY.NAME, "/").substring(2))
        .from(DIRECTORY)
        .connectBy(
               prior(DIRECTORY.ID).eq(DIRECTORY.PARENT_ID))
        .startWith(DIRECTORY.PARENT_ID.isNull())
        .orderBy(1)
        .fetch();

目前CONNECT BY支持的函数和伪列如下所示：

LEVEL
CONNECT_BY_IS_CYCLE
CONNECT_BY_IS_LEAF
CONNECT_BY_ROOT
SYS_CONNECT_BY_PATH
PRIOR

目前CONNECT BY语法还支持CUBRID数据库，期待未来还会支持更多的方言。

MySQL目前可以通过Procedure来实现CONNECT BY。

GROUP BY

GROUP BY短语可用形成聚合，去重或者是其他操作。对于每一种类集合，只会返回一个结果，比如：

# SQL语句
SELECT count(*), role_id
FROM `function`
GROUP BY role_id;

// JOOQ代码
dslContext.selectCount()
        .select(FUNCTION.ROLE_ID)
        .from(FUNCTION)
        .groupBy(FUNCTION.ROLE_ID)
        .fetch();

MySQL与众不同之处

MySQL在进行GROUP BY操作时，和标准语句表现比较，有些怪异的地方。简单来说，在进行GROUP BY时，你可以选择一些不是GROUP BY操作的字段，那么这个值将会随机的从GROUP中选取，你不能指定任意的顺序：

# SQL语句
SELECT id, `name`
FROM `function`
GROUP BY role_id;

// JOOQ代码
dslContext.select(FUNCTION.ID, FUNCTION.NAME)
        .from(FUNCTION)
        .groupBy(FUNCTION.ROLE_ID)
        .fetch();

空GROUP BY条件

JOOQ支持空GROUP BY条件，其实最后九九归一，只会返回一条记录。

ROLLUP()、CUBE()和GROUPING SETS()

一些数据库还支持group一些函数或者是扩展等，我们会在以后的内容进行讲解。

HAVING

HAVING短语一般搭配GROUP BY短语进行使用，是对GROUP BY后的数据再进行一次过滤：

# SQL语句
SELECT role_id
FROM `function` 
GROUP BY role_id 
HAVING role_id >= 1

// JOOQ代码
dslContext.select(FUNCTION.ROLE_ID)
        .from(FUNCTION)
        .groupBy(FUNCTION.ROLE_ID)
        .having(FUNCTION.ROLE_ID.greaterOrEqual(1))
        .fetch();

当然，你可以在HAVING时省略掉GROUP BY，前提是你的SELECT语句要含蓄地表现出聚合的意思。

# SQL语句
SELECT count(*), role_name
FROM `function`  
HAVING role_id >= 1

// JOOQ代码
dslContext.selectCount()
        .select(FUNCTION.ROLE_NAME)
        .from(FUNCTION)
        .having(FUNCTION.ID.greaterOrEqual(1))
        .fetch();

WINDOW

在SQL:2003标准中，出现了一个新的子句WINDOW，这个短语支持复用SELECT短语和ORDER BY短语，目前为止，还不能使用其他过滤子句。WINDOW子句用于计算一些行的集合的数据，非常类似于聚集函数，但和聚集函数不同，WINDOW不会将计算的行合并成一行输出，而是保留为它们原来的样子。

WINDOW子句目前支持Sybase和PostgreSQL，但是官方文档指明CUBRID、FIREBIRD 3.0同时具有window函数。

SELECT
  LAG(login_name, 1) OVER w "prev",
  user_name,
  LEAD(user_name, 1) OVER w "next"
FROM "user"
WINDOW w AS (PARTITION BY user_name)
ORDER BY user_name DESC

// JOOQ代码
WindowDefinition w = name("w").as(partitionBy(USER.USER_NAME));

dslContext.select(lag(USER.LOGIN_NAME, 1).over(w).as("prev"), 
                  USER.USER_NAME, 
                  lead(USER.USER_NAME).over(w).as("next"))
        .from(USER)
        .window(w)
        .orderBy(USER.USER_NAME)
        .fetch();

如果你的数据库支持window函数，那么可以在JOOQ中使用org.jooq.WindowDefinition和org.jooq.WindowSpecification来扩展你的window方法。

在创建一个明确的window之前，请首先使用DSL.name()创建一个name引用。

ORDER BY

除非你通过ORDER BY短语指明你想要的顺序，数据库将随机返回你需要的数据。在JOOQ，这将非常直接：

# SQL语句
SELECT *
FROM `function`
ORDER BY role_id asc, `name` desc;

// JOOQ代码
dslContext.select()
        .from(FUNCTION)
        .orderBy(FUNCTION.ID.asc(), FUNCTION.NAME.desc())
        .fetch();

通过调用asc()和desc()方法，JOOQ中任何的列或者字段都可以转换成org.jooq.SortField形式。

通过字段索引进行排序

SQL标准允许使用数字字面量（字面量，非绑定的值）来指定SELECT短语中列的索引，你可以不必在ORDER BY短语中重复写一遍冗长的字段名称，即使是绝大部分数据库都支持对列进行别名处理，这样的索引写法仍能为我们节省不少时间：

# SQL语句
SELECT *
FROM `function`
ORDER BY 2 asc, 3 desc;

// JOOQ代码
dslContext.select()
        .from(FUNCTION)
        .orderBy(two().asc(), inline(3).desc())
        .fetch();

one()、two()都是对inline(1)、inline(2)的便捷写法。

NULL值排序

一些数据库支持对含有null值的行进行指定排序，看看它们是先出场还是后出场：

# SQL语句
SELECT *
FROM `function`
ORDER BY id asc, `name` desc NULLS LAST;

// JOOQ代码
dslContext.select()
        .from(FUNCTION)
        .orderBy(FUNCTION.ID.asc(), FUNCTION.NAME.desc().nullsLast())
        .fetch();

这样，如果你的name字段中，包含null值，那么它们将会在所有数据的最后面输出。

别试了，CUBIRD、MariaDB、MySQL、SQLLite均不支持这种语法。

但是，Derby、H2、HSQLDB、Oracle和PostgreSQL支持这种语法。

但是，并不是数据库不支持这个SQL语法，我们就不能实现这个功能，我们可以从不同层面搞定这个问题，请继续阅读接下来的一小节。

使用CASE进行排序

CASE表达式是一个通用的模式，如果你想在你的查询中采用一些想你所想的排序，你可以将任何的列放入到ORDER BY中，然后使用CASE表达式：

# SQL语句
SELECT *
FROM `function`
ORDER BY CASE `name`
     WHEN 'Sunshine' THEN 1
     WHEN 'Sundial丶Dreams' THEN 2
     ELSE THEN 0 DESC

// JOOQ代码
dslContext.select()
        .from(FUNCTION)
        .orderBy(choose(FUNCTION.NAME)
                .when("Sunshine", 1)
                .when("Sundial丶Dreams", 2)
                .otherwise(0)
                .desc())
        .fetch();

这样写太冗长了，有没有更优雅的写法？有！

dslContext.select()
        .from(FUNCTION)
        .orderBy(FUNCTION.NAME.sortDesc("Sunshine", "Sundial丶Dreams"))
        .fetch();

如果你是跟着我的步伐，那么在写出上面的代码时，你会发现下一处代码示例：

dslContext.select()
        .from(FUNCTION)
        .orderBy(FUNCTION.NAME.sort(new HashedMap(){{
            put("Sunshine", 1);
            put("Sundial丶Dreams", 2);
        }}))
        .fetch();

你可以和之前提到的ORDER BY短语中的表达式结合使用：

dslContext.select()
        .from(FUNCTION)
        .orderBy(FUNCTION.NAME.sortAsc("Sunshine", "Sundial丶Dreams").nullsLast())
        .fetch();

JOOQ对于SELECT…ORDER BY套路的理解

SQL标准指明，查询表达式是可以进行排序的，并且查询表达式可以包含UNION、INTERSECT和EXCEPT（这些子句的子查询是不能排序的）。但是，许多数据库允许一种或者多种途径使用LIMIT子句，比没有依赖SQL标准。因此，JOOQ允许对所有的SELECT语句进行排序，不管是否是UNION的一部分。JOOQ使用组合子查询来依赖它们所需的正确语法。

Oracle的ORDER SIBLINGS BY

JOOQ也支持Oracle的SIBLINGS关键字，这个关键字配合ORDER BY一起使用在以CONNECT BY进行的分层查询中。

LIMIT…OFFSET子句

LIMIT…OFFSET目前还不是SQL标准中的子句（截止到SQL:2008），但是对于每个应用的分页或者是限制返回的数据大小在一个合理的范围之内，LIMIT…OFFSET是一个十分好用的子句。因此，LIMIT…OFFSET在不同的SQL方言中，有各种各样的实现。

凭借着我们一路以来对Hibernate、MyBatis的使用，我们对下面的代码将会感到非常亲切：

dslContext.select()
        .from(USER)
        .limit(10)
        .offset(100)
        .fetch();

这段代码的意思是：从第100条数据开始查（offset从0开始）10条数据。

SQL SERVER的ORDER BY、TOP和子查询

从上面的例子可以观测出，根据SQL方言来写出正确的SQL是比较棘手的。比如说，在SQL SERVER中，除非你使用了TOP子句，你不能在子查询中使用ORDER BY子句。

同样，在使用ROW_NUMERBER()、OVER()时需要使用ORDER BY子句，如果你没有使用，那么ORDER BY将会默认使用你的查询中的第一个字段。

SEEK

在上一小节中我们描述了使用OFFSET分页的情形，但是如果一味的使用此种分页方式，在分页页数比较的时候会带来极大地性能瓶颈。因为它会跳跃所有数据库中不需要的记录。

最快速，最稳定的分页工具是seek，JOOQ也支持seek语法：

dslContext.select()
      .from(FUNCTION)
      .orderBy(FUNCTION.NAME, FUNCTION.ROLE_ID)
      .seek("sunshine", 10)
      .limit(10)
      .fetch();

JOOQ的seek子句是SQL中不存在的语法子句，然而，更加直观：

它代替了你想获取的OFFSET
它不会将其他判断和seek判断混合起来
它是类型安全的
对于那些不支持行值表达式的数据库来说，它模仿了行值表达式

你不能在使用SEEK的同时，继续使用OFFSET。

FOR UPDATE

在同步或者其他原因下，我们一般可以通过事务来对一些行或者字段进行上锁，这时，我们可以采用SELECT…FOR UPDATE来指使数据库的行或者字段进行加锁。

不同数据库FOR UPDATE表现形式有所不同，请充分了解您所使用的数据库FOR UPDATE子句后，再进行使用。

# SQL语句
SELECT *
FROM `function`
WHERE id = 1
FOR UPDATE;

// JOOQ代码
dslContext.select()
        .from(FUNCTION)
        .where(FUNCTION.ID.eq(1))
        .forUpdate()
        .fetch();

上面的列子是使用FOR UPDATE上的是一个行锁，但是一些数据库同时也支持字段锁：

# SQL语句
SELECT *
FROM `function`
WHERE id = 1
FOR UPDATE OF `name`;

// JOOQ代码
dslContext.select()
        .from(FUNCTION)
        .where(FUNCTION.ID.eq(1))
        .forUpdate().of(FUNCTION.NAME)
        .fetch();

截止JOOQ 3.9.5版本，仅有DERBY、FIREBIRD、H2和HSQLDB支持字段锁。

Oracle这个庞大的机器，拥有更多的视野，它允许开发者指定实际上锁的状态，JOOQ也支持了Oracle额外特点的子句：

FOR UPDATE NOWAIT。这是默认的行为，如果没有没有获取到锁，那么查询将会立即失败。
FOR UPDATE WAIT n。在获取锁时会等待n秒，n秒时候查询失败。
FOR UPDATE SKIP LOCKED。这个特殊的语法将会跳过所有锁住的行记录，这对拥有多个消费者的队列表特别好用。

在JOOQ，你完全可以使用这些Oracle的扩展。

dslContext.select()
        .from(FUNCTION)
        .where(FUNCTION.ID.eq(1))
        .forUpdate().noWait()
        .fetch();
dslContext.select()
        .from(FUNCTION)
        .where(FUNCTION.ID.eq(1))
        .forUpdate().fetch().wait(10);
dslContext.select()
        .from(FUNCTION)
        .where(FUNCTION.ID.eq(1))
        .forUpdate().skipLocked()
        .fetch();

SQL Server和CUBRID中的FOR UPDATE

SQL标准将FOR UPDATE指定为适合游标的子句，而绝大部分数据库也将其解释成适合的SELECT语句。万事总有例外，在CUBRID和SQL Server数据库中却不允许使用任何的FOR UPDATE子句，但是JOOQ在JDBC层面模拟了FOR UPDATE的行为。

JDBC允许在任何语句中指定TYPE_SCROLL_SENSITIVE和CONCUR_UPDATABLE，然后使用Result.updateXXX()中设置字段锁或者行锁：

try (
    PreparedStatement stmt = connection.prepareStatement(
        "SELECT * FROM author WHERE id IN (3, 4, 5)",
        ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery()
) {
    while (rs.next()) {
        // 在更新第一个字段的时候，同时进行上锁
        rs.updateObject(1, rs.getObject(1));
        rs.updateRow();

        // 做事
    }
}

但是这个方法的弊端是我们必须使用一个滚动游标，这个游标中的数据将会排排上锁，这就有意思了，因为这样会带来死锁或者条件竞争的风险，JDBC可能永远都无法从失败上锁中恢复，想一想，如下语句执行时会带来什么后果：

# 线程A
SELECT *
FROM `function`
ORDER BY role_id ASC;

# 线程B
SELECT *
FROM `function`
ORDER BY role_id DESC;

所以，先进的解决方案带来便利的同时，我们还需要谨慎。

使用FOR SHARE实现悲观可共享锁

比如MySQL和PostgreSQL都支持使用FOR SHARE子句来明确地给出一个非独占锁，当然，JOOQ也支持。

乐观锁

伏笔，笔者会在后续的内容详细的介绍乐观锁。

UNION、INTERSECTION和EXCEPT

SQL允许使用对数据集使用标准的集合操作，这些操作包括聚合、相交和差异。对两个子查询的数据集使用集合操作符的前提是每个结果集返回的字段列应完全相同。

UNION和UNION ALL

UNION和UNION ALL将两个结果集合并为一个，但是两者之间并非完全类似。

UNION在结合后会删除重复的数据。
UNION ALL则会保持子查询的结果集不变。

所以在实践中，如果你不在意是否需要删除重复数据，二选一取决于你的使用环境，但是原装的总是最好的：

# SQL语句
(SELECT * 
FROM FUNCTION
WHERE role_id = 1)
UNION All
(SELECT * 
FROM FUNCTION
WHERE role_id = 2)

// JOOQ代码
dslContext.select()
        .from(FUNCTION)
        .where(FUNCTION.ROLE_ID.eq(1))
        .unionAll(dslContext.select()
                .from(FUNCTION)
                .where(FUNCTION.ROLE_ID.eq(2)));

INTERSECT [ALL]和EXCEPT [ALL]

讲到这里，其实用高数中集合的概念来示范一下更加的醍醐灌顶：

UNION ALL相当于两个数据集的还没有去重的并集部分。
UNION相当于两个数据集的并集。
INTERSECT相当于两个数据集的没有去重的交集部分。
EXCEPT相当于数据集A相对于数据集B的没有去重的差集部分。

Oracle中MINUS同EXCEPT。

JOOQ的SET操作符和SQL标准之间的差异

根据前面的学习，不难发现，JOOQ轻微的改变了这些SET操作符的语义。因为在一个子查询中，可能不允许出现任何ORDER BY子句或者是LIMIT子句（如果你把子查询包装成嵌套查询，当我没说），但是JOOQ支持你这样做（在MySQL方言下）：

# SQL语句
(SELECT * 
FROM FUNCTION
WHERE role_id = 1 ORDER BY id ASC)
UNION ALL
(SELECT * 
FROM FUNCTION
WHERE role_id = 2 ORDER BY id DESC)
ORDER BY role_id DESC

// JOOQ代码
dslContext.select()
        .from(FUNCTION)
        .where(FUNCTION.ROLE_ID.eq(1))
        .orderBy(FUNCTION.ID.asc())
        .unionAll(dslContext.select()
        .from(FUNCTION)
        .where(FUNCTION.ROLE_ID.eq(2))
        .orderBy(FUNCTION.ID.desc()))
        .orderBy(FUNCTION.ROLE_ID.desc())
        .fetch();

1~22的类型安全

如果两个子查询需要结合在一起，那么这两个子查询最起码需要具备同样的字段位，JOOQ在3.0版本隆重推出了RECORD的类型安全模式，方便在编译器检查这些约束。

Select<Record2<Integer, String>> selectOne = dslContext.select(FUNCTION.ID, FUNCTION.NAME)
        .from(FUNCTION)
        .where(FUNCTION.ROLE_ID.eq(1));

Select<Record1<Integer>> selectTwo = dslContext.select(FUNCTION.ID)
        .from(FUNCTION)
        .where(FUNCTION.ROLE_ID.eq(2));

// 编译异常，union需要的是Record<Integer, String>，但提供的是Record1<Integer>
selectOne.union(selectTwo);

Select<Record2<Integer, String>> selectThree = dslContext.select(FUNCTION.ID, inline("name"))
        .from(FUNCTION)
        .where(FUNCTION.ROLE_ID.eq(3));

// 编译正常
selectOne.unionAll(selectThree);

Oracle

如果你在coding中和Oracle或者CUBRID走的很近，

SELECT /*+ALL_ROWS*/ `name`
FROM FUNCTION

对于这种SQL，写过MyBatis的人仰天长啸，太简单了，是的，比较简单：

dslContext.select(FUNCTION.NAME)
        .hint("/*+ALL_ROWS*/")
        .from(FUNCTION)
        .fetch();

你理解上面SQL语句的含义吗？

hint子句支持各种字符串，通常出现在SELECT [DISTINCT]关键字和映射字段的中间，MySQL等其他数据库也同样适用：

# SQL语句
SELECT SQL_CALC_FOUND_ROWS, count(*)
FROM `function`
LIMIT 10;

// JOOQ代码
dslContext.select()
        .hint("SQL_CALC_FOUND_ROWS")
        .from(FUNCTION)
        .limit(10);
        .fetch();

SELECT的词汇和逻辑顺序

对于SQL来说，它同时具备词汇和逻辑顺序。词汇顺序是受英语语言本身而来。作为数据库的命令语句，SQL会很自然的表达出必要的语态。

SELECT的逻辑顺序

SELECT子句的逻辑顺序，和语法并不是相一致的。事实上，逻辑顺序如下所示：

FROM。处于第一位，所有的数据源将跟在后面声明或者连接。
WHERE。FROM的随后位置，数据尽可能早的进行过滤。
CONNECT BY。WHERE的随后位置，数据会被迭代或者递归遍历，或者是生成新的元组。
GROUP BY。CONNECT BY的随后位置，数据会被聚合为多个分组，如果使用ROLLUP()、CUBE()或者是GROUPING SETS()方法，可能会生成新的元组。
HAVING。GROUP BY的随后位置，数据将再一次进行过滤。
SELECT。到现在为止，映射基本完成。如果你使用了SELECT DISTINCT子句，那么数据集还会进行滤重。
UNION [ALL]。我们会重复如上的几个步骤来完成用UNION [ALL]连接的子查询。
ORDER BY。现在，我们将对剩余的数据进行排序。
LIMIT。然后，对排序后的数据进行分页。
FOR UPDATE。最后，才会使用悲观锁。

巨硬的SQL Server总是与众不同，它做了一些微调：

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

如你所见，数据库不得不对SQL语句进行重排序，来制定最优的执行计划。

不寻常的语法：LINQ、SLICK

比如像LINQ、SLICK等更高级别的抽象，通常试图反转SELECT子句的词汇顺序来更贴近逻辑顺序。这种把SELECT子句放到语句最后面的好处是可以进行更好的复用。

比如说：

// as           // from
From p          In db.Products

// where
Where p.UnitsInStock <= p.ReorderLevel AndAlso Not p.Discontinued

// select
Select p

// for
val q = for {

    // from          // where
    c <- Coffees     if c.supID === 101

// select
} yield (c.name, c.price)

最初，这样的写法看起来是一个很好的创意，但是这增加了过去常常写SQL的coder的翻译难度。JOOQ从设计上来看，非常贴近于SQL，尤其是相对于SLICK来说，因为SLICK不仅仅改变了SELECT语句的顺序，而且还将SQL子句和Scala语言重重地耦合在一起。

以此，JOOQ的DSL API，是SQL逻辑顺序的标准模范。

WITH

明确的CTE

嵌入的CTE

SELECT

复杂查询

单表查询

查询

基本用法

DISTINCT

SELECT *

字段安全类型容量已经上升至22个

FROM

更高级的Table表达式

从DUAL表中查询数据

JOIN

JOOQ捷径：ON KEY

奇淫技巧：USING

Oracle的分段OUTER JOIN

SEMI JOIN和ANTI JOIN

T-SQL的CROSS APPLY和OUTER APPLY

WHERE

CONNECT BY

GROUP BY

MySQL与众不同之处

空GROUP BY条件

ROLLUP()、CUBE()和GROUPING SETS()

HAVING

WINDOW

ORDER BY

通过字段索引进行排序

NULL值排序

使用CASE进行排序

JOOQ对于SELECT…ORDER BY套路的理解

Oracle的ORDER SIBLINGS BY

LIMIT…OFFSET子句

SQL SERVER的ORDER BY、TOP和子查询

SEEK

FOR UPDATE

SQL Server和CUBRID中的FOR UPDATE

使用FOR SHARE实现悲观可共享锁

乐观锁

UNION、INTERSECTION和EXCEPT

UNION和UNION ALL

INTERSECT [ALL]和EXCEPT [ALL]

JOOQ的SET操作符和SQL标准之间的差异

1~22的类型安全

Oracle

SELECT的词汇和逻辑顺序

SELECT的逻辑顺序

不寻常的语法：LINQ、SLICK

相关推荐

评论 抢沙发

近期文章

我的标签

友情链接

码字不容易，路过请投币

支付宝扫一扫

微信扫一扫

评论抢沙发