9D1.Jooq最佳实操

trydofor原创实战手册数据库大约 3 分钟

9D1.Jooq最佳实操

WingsBoot体系内对Jooq进行了简化，在实践中有以下约定和语法糖

9D1.1.代码生成

推荐使用Warlock3JooqGenerator，其进一步封装了WingsCodeGenerator，

databaseIncludes - 基于正则，指定生成代码的表
setGlobalSuffix - 可对全局对象增加后缀，以区分同包不同工程
forcedStrCodeEnum - 绑定varchar和enum类型
forcedIntConsEnum - 绑定int和emum类型
forcedLocale - 绑定varchar和Locale类型
forcedZoneId - 绑定int和ZoneId类型
forcedZoneStr - 绑定varchar和ZoneId类型

Warlock3JooqGenerator generator = new Warlock3JooqGenerator();
generator.setTargetDir(JAVA);
generator.gen(JDBC, USER, PASS,
        Warlock3JooqGenerator.includeWarlock(),
        bd -> bd.setGlobalSuffix("Warlock"));

默认配置中，对以下类型做了映射

Boolean - TINYINT(\(1\))?，所有字段
Integer - TINYINT[2-9()]*，所有字段
Locale - 字段.*\.locale，所有类型
ZoneId - 字段*\.zoneid，类型INT.*和(VAR)?CHAR.*

9D1.2.Dao和Dsl

Jooq官方示例中，都采用了静态引入Table，注入Dsl的形式，但在wings中推荐从Dao入手。

// 以lombok完成Setter注入，必须setter注入
@Setter(onMethod_ = {@Autowired})
private WinConfRuntimeDao dao;

// 检查表是否存在
dao.notTableExist()
// 通过Dao获取Table和Dsl
WinConfRuntimeTable a = dao.getAlias();
WinConfRuntimeTable t = dao.getTable();
DSLContext dsl = dao.ctx();

WingsJooqDaoAliasImpl和WingsJooqDaoJournalImpl中存在大量方法

batchXX - 批量插入，合并，更新等操作有关
fetchXX - 按类型或自动读取数据
countXX - count记录数
insertXX - 插入有关
updateXX - 更新有关
deleteXX - 逻辑或物理删除

Dsl和Dao等价，都在service层以下使用。

简单操作用Dao，复杂的用Dsl
Dao更像java，Dsl更像Sql
当有条件和字段时，两者都要明确table或alias
Dsl可以getSql，也可用Any2Dto把Sql变为Dsl

9D1.3.精确查找

用什么就获取什么，用pojo或record接收，确保强类型。

final WinUserLoginTable t = winUserLoginDao.getTable();
// Dao
dao.fetch(t, 0, 2, t.Id.gt(1L).and(t.CommitId.lt(200L)),
    t.Id, t.CommitId,
    t.Id.desc());
// Dao lambda
dao.fetch(0, 2, (t, w) -> w
    .where(t.Id.gt(1L).and(t.CommitId.lt(200L)))
    .order(t.Id, t.CommitId, t.Id.desc()));
// Dsl
winUserLoginDao
    .ctx()
    .select(t.AuthType, t.LoginIp, t.LoginDt, t.Terminal, t.Failed)
    .from(t)
    .orderBy(t.LoginDt.desc())
    .limit(query.toOffset(), query.getSize())
    .fetch()
    .into(Item.class);

9D1.4.精确更新

final WinUserBasisTable tu = winUserBasisDao.getTable();
// Dao map
Map<Field<?>, Object> setter = new HashMap<>();
setter.put(tu.Nickname, user.getNickname());
setter.put(tu.CommitId, commit.getCommitId());
winUserBasisDao.update(tu, setter, tu.Id.eq(userId), true);
// Dao pojo
WinUserBasisTable upo = new WinUserBasisTable();
upo.setNickname(user.getNickname());
upo.setCommitId(commit.getCommitId());
winUserBasisDao.update(tu, upo, tu.Id.eq(userId), true);

// Dsl
winPermEntryDao
    .ctx()
    .update(tu)
    .set(tu.CommitId, commit.getCommitId())
    .set(tu.ModifyDt, commit.getCommitDt())
    .set(tu.Remark, remark)
    .where(tu.Id.eq(permId))
    .execute();

9D1.5.分页查询

分页查询使用PageJooqHelper或PageJdbcHelper，两者都进行了查询优化，可使用缓存的总记录数total。

total < 0 - DB执行count和select
total = 0 - DB不count，不select
total > 0 - DB不count，但select

final WinUserBasisTable t1 = winUserBasisDao.getTable();
// dao dsl
PageJooqHelper.use(dao, page)
    .count()
    .from(t1)
    .where(t1.Id.ge(1L))
    .order(order)
    .fetch(t1.Id, t1.CommitId)
    .into(WinUserBasisTable.class);
// wrap query
val qry4 = dsl.select(t1.asterisk()).from(t1).where(t1.Id.ge(1L));
PageJooqHelper.use(dao, page)
    .wrap(qry4, order)
    .fetch()
    .into(WinUserBasisTable.class);

9D1.6.逻辑删除

逻辑删除使用WingsJooqDaoJournal即可，不过wings推荐物理删除。

dao.delete - by condition
dao.deleteById - by id

使用JournalJooqHelp或JournalJdbcHelp，可以触发jooq监听和trigger。

JournalJooqHelp.deleteByIds(dsl, TstShardingTable.TstSharding, 12L, 1L, 2L);
JournalJooqHelp.deleteByIds(tmpl, "`tst_sharding`", 34L, 3L, 4L);

// UPDATE `tst_sharding` SET commit_id=34, delete_dt=NOW(3)  WHERE id IN (3,4)
// DELETE FROM `tst_sharding`  WHERE id IN (3,4)

9D1.7.数据差分

Wings可用Trigger跟踪数据变化，但其目的是在业务不稳定期，可细粒度的调查或恢复数据。有些业务场景需要对外展示数据变化历史，甚至类似代码diff一样，显示数据时间线。

一句话，正常的稳定的业务功能，不应该依赖Trigger，应该有独立的数据结构。以下实现，根据CUD的不同，先后查询数据库，按默认顺序对数据进行差分比较。

JournalDiff - 一条或多条数据差分的数据结构
dao.diffXXX - 插入，更新，删除数据，获得插入后的差分数据
JournalDiffHelper.helpXXX - 辅助差分操作
JournalDiffHelper.diffXXX - 直接差分数据库操作
JournalDiffHelper.tidyXXX - 简化差分数据
wings.faceless.jooq.cud.diff - 配置表的默认忽略项

以下为更新示例，更多示例参考 JooqDslAndDaoSample#test5Diff

// 使用Dao
Map<Field<?>, Object> setter = new LinkedHashMap<>();
setter.put(t.CommitId, t.CommitId.add(1));
setter.put(t.LoginInfo, "login by diff update");
final JournalDiff d2 = dao.diffUpdate(t, setter, t.Id.ge(id));

// 使用Dsl
val query = dsl.selectFrom(t).where(t.Id.eq(id));
final JournalDiff d2 = JournalDiffHelper.diffUpdate(t, query, () -> {
    dsl.update(t)
        .set(t.CommitId, t.CommitId.add(1))
        .set(t.LoginInfo, "login by diff update")
        .set(t.ModifyDt, now)
        .where(t.Id.eq(id))
        .execute();
});