ORM的小清新(3)——JOOQ写操作-随遇而安

笔者在上一篇文章中，花了巨幅笔向各位读者展示了如何使用JOOQ进行读操作，那么本篇文章将集中讲解，如何使用JOOQ进行写操作。

INSERT语句

INSERT语句用于向数据库中插入新记录，接下来的内容将展示JOOQ的花式插入语句。

INSERT …VALUES

简单插入

和其他ORM框架类似，尤其是Hibernate，我们可以直接使用VALUES()方法进行链式插入：

# SQL语句
INSERT INTO `user`(login_name, user_name, password, authority) 
VALUES("My", "Sunshine", "654321", 3);

// JOOQ代码
dslContext.insertInto(USER, USER.LOGIN_NAME, USER.USER_NAME, USER.PASSWORD, USER.AUTHORITY)
        .values("My", "Sunshine", "654321", 2)
        .execute();

和SELECT中类似，VALUES()目前也支持最多22个的类型安全属性，除了链式写法，我们还可以面向对象：

// JOOQ代码
InsertValuesStep4<UserRecord, String, String, String, Integer> step = dslContext.insertInto(USER, USER.LOGIN_NAME, USER.USER_NAME, USER.PASSWORD, USER.AUTHORITY);
step.values("My", "Sunshine", "654321", 2);
step.execute();

多行插入

插入一行总是很简单，那么插入多行呢？

其实在SQL标准中已经指明，我们可以在一个INSERT语句中使用VALUES()方法声明多个行数据，那么在JOOQ中呢？

# MySQL语句
INSERT INTO `user`(login_name, user_name, password, authority) 
VALUES("Allen", "Iverson", "123456", 3), 
("Tracy", "McGrady", "123456", 4);

# PostgreSQL语句
INSERT INTO "user" (login_name, user_name, password, authority) VALUES
('Allen', 'Iverson', '123456', 3),
('Tracy', 'McGrady', '123456', 4);

// JOOQ代码
dslContext.insertInto(USER, USER.LOGIN_NAME, USER.USER_NAME, USER.PASSWORD, USER.AUTHORITY)
        .values("Allen", "Iverson", "123456", 3)
        .values("Tracy", "McGrady", "123456", 4)
        .execute();

JOOQ试图保持和SQL的近距离接触，详细来说呢，受限于Java的表现力，所以只能如此的重复使用VALUES()方法。

不同数据库多行插入的SQL语句是有差异的。

但是有些关系型数据库是不支持单个语句插入多行记录的。这些情况下，JOOQ将会按照下面的操作来模仿多行插入：

# SQL语句
INSERT INTO `user`(login_name, user_name, password, authority) 
SELECT "Allen", "Iverson", "123456", 3 FROM DUAL UNION ALL
SELECT "Tracy", "McGrady", "123456", 4 FROM DUAL;

// JOOQ代码
dslContext.insertInto(USER, USER.LOGIN_NAME, USER.USER_NAME, USER.PASSWORD, USER.AUTHORITY)
        .values("Allen", "Iverson", "123456", 3)
        .values("Tracy", "McGrady", "123456", 4)
        .execute();

插入默认值

INSERT… DEFAULT VALUES是一个鲜为人知的SQL语法特点。当使用这种语法时，一个仅包含默认值的行记录插入到了数据库中：

# MySQL语句
INSERT INTO `user`() 
VALUES();

# PostgreSQL语句
INSERT INTO "user"
DEFAULT VALUES;

// JOOQ代码
dslContext.insertInto(USER)
        .defaultValues()
        .execute();

如果你想使用相同的事务来为后续的UPDATE语句来储备一个行，或者你只是想发送一个包含触发生成、类似于ID或者时间戳的默认值事件，这看起来说的过去。

并不是所有的数据库都支持DEFAULT VALUES，如果不支持，JOOQ将会使用等价代码进行隐式替换：

# SQL语句
INSERT INTO `user`(login_name, user_name, password, authority) 
VALUES(
    DEFAULT,
    DEFAULT,
    DEFAULT,
    DEFAULT);

// JOOQ代码
dslContext.insertInto(USER,
                      USER.LOGIN_NAME,
                      USER.USER_NAME,
                      USER.PASSWORD,
                      USER.AUTHORITY)
        .values(defaultValue(USER.LOGIN_NAME),
                defaultValue(USER.USER_NAME),
                defaultValue(USER.PASSWORD),
                defaultValue(USER.AUTHORITY))
        .execute();

DEFAULT或者说是Dsl#defaultValue()方法可以作用于单个列，虽然看起来这和不设置这列值（除非这个列没有默认值）没什么两样。

INSERT … SET

MySQL或者其他的关系型数据库，允许使用一种非SQL标准的，形似UPDATE的INSERT语句，目前JOOQ支持这种语法，未来将会支持所有的数据库，使用哪种就看你的个人爱好了：

# SQL语句
INSERT INTO `user`
SET login_name = 'My', 
    user_name = 'Sunshine', 
    password = '654321', 
    authority = 2;

// JOOQ代码
dslContext.insertInto(USER)
        .set(USER.LOGIN_NAME, "My")
        .set(USER.USER_NAME, "Sunshine")
        .set(USER.PASSWORD, "654321")
        .set(USER.AUTHORITY, 2)
        .execute();

从示例中可以看出，语法有点冗余，但是可读性更强，因为每个字段紧接着对应字段值，很方便我们查看插入的列和值的对应关系。

本质上来说，这两种语法是完全相同的。

INSERT … SELECT

有些场合，比如在表数据拷贝时，你可能会使用INSERT SELECT语法：

dslContext.insertInto(USER)
        .select(selectFrom(USER))
        .execute();

INSERT … ON DUPLICATE KEY

ON DUPLICATE KEY UPDATE

MySQL数据库支持一种便捷的方法来新增或者更新一条记录，JOOQ支持这种非标准的语法扩展，如果你的数据库支持MERGE语句，那么完全可以移植到你所使用的关系型数据库中：

dslContext.insertInto(USER, USER.LOGIN_NAME, USER.USER_NAME, USER.PASSWORD, USER.AUTHORITY)
        .values("My", "Sunshine", "654321", 2)
        .onDuplicateKeyUpdate()
        .set(USER.PASSWORD, "123456")
        .execute();

ON DUPLICATE KEY IGNORE

MySQL既然支持ON DUPLICATE KEY UPDATE那么也可以支持ON DUPLICATE KEY IGNORE，JOOQ也提供了便捷的使用方式：

dslContext.insertInto(USER, USER.LOGIN_NAME, USER.USER_NAME)
        .values("My", "Sunshine")
        .onDuplicateKeyIgnore()
        .execute();

如果我们使用的数据库并没有支持IGNORE，JOOQ会使用MERGE或者INSERT … SELECT语句等价转化ON DUPLICATE KEY IGNORE语句。接下来的两小节，我们会介绍是如何转换的。

用MERGE模拟IGNORE

上面对IGNORE的JOOQ示例代码也可以进行如下示例的改装：

MERGE INTO "user"
USING (SELECT login_name, user_name FROM DUAL)
WHEN NOT MATCHED THEN INSERT (login_name, user_name)
VALUES("My", "Sunshine")

INSERT … SELECT WHERE NOT EXISTS模拟IGNORE

上面对IGNORE的示例也可以进行如下示例的改装：

INSERT INTO `user` (login_name, user_name)
SELECT 'My', 'Sunshine'
WHERE NOT EXISTS (
  SELECT 1
  FROM `user`
  WHERE id = 9
)

如果数据库中已存在id=9的行记录，并且行中login_name的列值为My，user_name列的值为Sunshine，那么将不会执行插入数据。

INSERT … RETURNING

Postgres数据库已经本地支持INSERT … RETURNING子句，这是一个很强大的概念，媲美其他方言中JDBC所使用的getGeneratedKeys()方法：

// 返回单数据结果集
dslContext.insertInto(USER, USER.LOGIN_NAME, USER.USER_NAME, USER.PASSWORD, USER.AUTHORITY)
        .values("My", "Sunshine", "123456", 1)
        .returning(USER.ID)
        .fetchOne()
        .intoStream()
        .forEach(System.out::println);

// 返回多数据结果集
dslContext.insertInto(USER, USER.LOGIN_NAME, USER.USER_NAME, USER.PASSWORD, USER.AUTHORITY)
        .values("My", "Sunshine", "123456", 1)
        .values("Sundial", "Dreams", "654321", 2)
        .returning(USER.ID, USER.USER_NAME)
        .fetch()
        .intoMap(USER.ID, USER.USER_NAME)
        .forEach((k, v) -> System.out.println(k + "---" + v)
        );

UPDATE

众所周知，UPDATE用于更新一个或多个已存在的数据库表记录，UPDATE只能作用于单表，未来会实现同时更新多个表的操作：

# SQL语句
UPDATE `user`
SET login_name = 'My', user_name = 'Sunshine'
WHERE id = 10;

// JOOQ代码
dslContext.update(USER)
        .set(USER.LOGIN_NAME, "My")
        .set(USER.USER_NAME, "Sunshine")
        .where(USER.ID.eq(10))
        .execute();

绝大部分数据库允许在UPDATE语句中通过不同途径使用一定数量的子查询，JOOQ将它们映射为一个集合方法：

# SQL语句（PostgreSQL）
UPDATE "user"
SET user_name = 
(SELECT user_name FROM "user" WHERE "id" = 1)
WHERE "id" = 3;

// JOOQ代码
dslContext.update(USER)
        .set(USER.USER_NAME, 
                select(USER.USER_NAME).from(USER).where(USER.ID.eq(1)))
        .where(USER.ID.eq(3))
        .execute();

使用行表达式

JOOQ支持在各种各样的UPDATE子句上下文中使用标准的row表达式，但是在一个时间点，只有一行记录会被更新：

# SQL语句（PostgreSQL）
UPDATE "user"
SET(login_name, user_name) = ('My', 'Sunshine')
WHERE "id" = 1;

// JOOQ代码
dslContext.update(USER)
        .set(row(USER.LOGIN_NAME, USER.USER_NAME), row("My", "Sunshine"))
        .where(USER.ID.eq(1))
        .execute();

UPDATE … RETURNING

FireBird和Postgres数据库支持UPDATE … RETURNING结构子句。UPDATE … RETURNING子句和INSERT … RETURNING子句类似，方便在一个语句中获取更新行记录后的字段信息：

# SQL语句（PostgreSQL）
UPDATE "user"
SET login_name = 'My'
WHERE "id" = 1
RETURNING user_name;

// JOOQ代码
dslContext.update(USER)
        .set(USER.LOGIN_NAME, "My")
        .where(USER.ID.eq(1))
        .returning(USER.USER_NAME)
        .fetchOne()
        .getValue(USER.USER_NAME);

在DB2中，我们可以使用SQL标准中提到的SELECT … FROM FINAL TABLE(UPDATE …)结构来模拟UPDATE … RETURNING子句。

在Oracle中，我们可以使用PL/SQL的UPDATE … RETURNING语句。

DELETE

删除是DML语句中比较简单的部分，这个子句完成了删除表中数据的功能

DELETE只能作用于单表之上，未来将会支持多表删除记录操作。

# SQL语句（PostgreSQL）
DELETE "user"
WHERE "id" = 1;

// JOOQ代码
dslContext.delete(USER)
        .where(USER.ID.eq(1))
        .execute();

MERGE

MERGE是比较超前的SQL构造标准化实现语句之一。

目前支持MERGE语句的数据库有：

DB2
HSQLDB
Oracle
SQL Server
Sybase
MySQL有近似语句：INSERT … ON DUPLICATE KEY UPDATE

MERGE语句的意思是选定一个目标表，将源表的数据合并到目标表中，DB2、Oracle、SQL Server和Sybase还允许删除一些数据或者是添加一些额外的子句。在JOOQ 3.9.5版本中，只支持Oracle的扩展：

# SQL语句
MERGE INTO USER 
USING (SELECT 1 FROM DUAL) ON (USER_NAME = 'Sunshine') 
WHEN MATCHED THEN UPDATE SET LOGIN_NAME = 'My' 
WHEN NOT MATCHED THEN INSERT (USER_NAME) VALUES ('Sunshine')

解释一下上面的SQL语句：即将向USER表中合并一条数据，如果表中已有一条数据的USER_NAME字段对应的值为Sunshine，那么将这条记录的LOGIN_NAME更新为My，如果没有找到符合的数据，则插入一条USER_NAME字段值为Sunshine。

那么在JOOQ中呢？

// JOOQ代码
dslContext.mergeInto(USER)
        .using(dslContext.selectOne())
        .on(USER.USER_NAME.eq("Sunshine"))
        .whenMatchedThenUpdate()
        .set(USER.LOGIN_NAME, "My")
        .whenNotMatchedThenInsert(USER.USER_NAME)
        .values("Sunshine")
        .execute();

H2的MERGE，不一样的MERGE

H2数据库的MERGE稍显与众不同，它不虽然不具备上节所述的那么强大的功能，但是更直接的语句，和前一小节的代码示例进行对比，我们就会发现或多或少的不同之处：

# SQL语句
MERGE INTO USER (LOGIN_NAME, USER_NAME) 
KEY (USER_NAME) 
VALUES ('My', 'Sunshine')

// JOOQ代码
dslContext.mergeInto(USER, USER.LOGIN_NAME, USER.USER_NAME)
        .key(USER.USER_NAME)
        .values("My", "Sunshine")
        .execute();

解释一下上面的SQL语句：检查USER表中是否已经有一位USER_NAME为Sunshine的记录，如果有，将这条记录的LOGIN_NAME更新为My，如果没有，添加一个LOGING_NAME为My，USER_NAME为Sunshine的记录。

VALUES()的类型安全升级到22个

和前面提到的绝大多数类型安全个数一样，目前MERGE语句提供了最多22个的参数类型安全保证。

MERGE的类型安全保证包括H2数据库。

ORM的小清新(3)——JOOQ写操作

INSERT语句

INSERT …VALUES

简单插入

多行插入

插入默认值

INSERT … SET

INSERT … SELECT

INSERT … ON DUPLICATE KEY

ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY IGNORE

用MERGE模拟IGNORE

INSERT … SELECT WHERE NOT EXISTS模拟IGNORE

INSERT … RETURNING

UPDATE

使用行表达式

UPDATE … RETURNING

DELETE

MERGE

H2的MERGE，不一样的MERGE

VALUES()的类型安全升级到22个

相关推荐

评论抢沙发

近期文章

我的标签

友情链接

码字不容易，路过请投币

支付宝扫一扫

微信扫一扫

INSERT语句

INSERT …VALUES

简单插入

多行插入

插入默认值

INSERT … SET

INSERT … SELECT

INSERT … ON DUPLICATE KEY

ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY IGNORE

用MERGE模拟IGNORE

INSERT … SELECT WHERE NOT EXISTS模拟IGNORE

INSERT … RETURNING

UPDATE

使用行表达式

UPDATE … RETURNING

DELETE

MERGE

H2的MERGE，不一样的MERGE

VALUES()的类型安全升级到22个

相关推荐

评论 抢沙发

近期文章

我的标签

友情链接

码字不容易，路过请投币

支付宝扫一扫

微信扫一扫

评论抢沙发