9D1.Jooq Better Practive

trydoforOriginalPracticeManualDatabaseAbout 3 min

9D1.Jooq Better Practive

Wings simplified Jooq with the following conventions and syntax sugar in practice,

9D1.1.Code Generator

Recommend using Warlock3JooqGenerator, which further encapsulates WingsCodeGenerator.

databaseIncludes - specify the table to generate code based on RegExp
setGlobalSuffix - add suffix to global objects to distinguish different projects from the same package
forcedStrCodeEnum - bind varchar and enum types
forcedIntConsEnum - bind int and enum types
forcedLocale - bind varchar and locale types
forcedZoneId - bind int and ZoneId types
forcedZoneStr - bind varchar and ZoneId types

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

The default configuration maps the following types,

Boolean - TINYINT(\(1\))? match all fields
Integer - TINYINT[2-9()]* match all fields
Locale - field .*\.locale match all types
ZoneId - field *\.zoneid match INT.* and (VAR)?CHAR.*

9D1.2.Dao and Dsl

Jooq official examples are in style of importing static Table, injected Dsl, but Wings recommend to start from Dao.

// Complete Setter injection with lombok
@Setter(onMethod_ = {@Autowired})
private WinConfRuntimeDao dao;

// Check table exists
dao.notTableExist()
// obtain Table and Dsl from Dao
WinConfRuntimeTable a = dao.getAlias();
WinConfRuntimeTable t = dao.getTable();
DSLContext dsl = dao.ctx();

There are many methods in WingsJooqDaoAliasImpl and WingsJooqDaoJournalImpl

batchXX - batch insert, merge, update, etc.
fetchXX - select data by type or automatically
countXX - count the number of records
insertXX - insert related
updateXX - update related
deleteXX - logical or physical delete

Dsl and Dao are equivalent and both are used below the service layer.

Dao is used for simple operations, Dsl is used for complex ones
Dao is more like java, Dsl is more like Sql
Both explicitly table or alias when there are conditions and fields
Dsl can getSql, also use Any2Dto turn Sql into Dsl

9D1.3.Accurate Select

Get what you need and receive it with the typesafe pojo or record to ensure strong type.

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.Accurate Update

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.Paginate Select

Use PageJooqHelper or PageJdbcHelper for paginating queries, Both are optimized for queries and can use the cached total number of records.

total < 0 - DB count and select
total = 0 - DB does not count, does not select
total > 0 - DB does not count, but 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.Logical Delete

Use WingsJooqDaoJournal for logical delete, but wings recommends physical deletion.

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

Using JournalJooqHelp or JournalJdbcHelp can fire the jooq listener and 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)

Wings can use Trigger to track data changes, but its purpose is to allow fine-grained investigation or recovery of data during periods of business instability. Some business scenarios require external display of data change history, or even code diff-like display of data timelines.

In short, normal and stable business functions should not rely on Trigger and should have independent data structures. The following implementation, depending on the CUD, queries the database sequentially and compares the data differently in the default order.

JournalDiff - data structure for one or more data diffs
dao.diffXXX - insert, update, delete data and get the differential data after insertion
JournalDiffHelper.helpXXX - help diff operation
JournalDiffHelper.diffXXX - direct diff database operations
JournalDiffHelper.tidyXXX - tidy up differential data
wings.faceless.jooq.cud.diff - default ignore for configuration tables

The following is an updated example, for more examples see JooqDslAndDaoSample#test5Diff

// use 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));

// use 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();
});