UPSERT、批量更新、返回主键与高级 SQL

在前几篇中，我们已经完成了：

  • tio-boot + jOOQ 纯配置类整合
  • 事务管理
  • Agroal / Druid 数据源整合
  • jOOQ Codegen 强类型升级
  • 基于 Record / POJO 的增删改查、分页、动态条件、批量插入

到这里，已经可以覆盖大多数常规 CRUD 场景。

但在真实项目中，还会频繁遇到几类更“生产化”的问题：

  • 插入时如果已存在，如何自动改成更新
  • 批量更新怎么写才清晰
  • 插入后如何可靠拿到主键或完整返回值
  • PostgreSQL 的 returning、on conflict 如何用 jOOQ 表达
  • 如何把复杂 SQL 继续保持在“强类型 + 可维护”的轨道上

本文就围绕这些问题，系统讲透：

  • PostgreSQL UPSERT
  • 批量更新
  • 返回主键与 returning
  • 批量写入的几种方式
  • 常见高级 SQL 写法
  • 在 tio-boot + jOOQ 中的推荐工程实践

一、为什么这一篇很重要

前几篇主要解决的是“能不能优雅地做 CRUD”。

这一篇解决的是：

当项目进入真实业务阶段后，如何把数据库写操作做得更稳、更高效、更贴近 PostgreSQL 能力。

尤其是 PostgreSQL，有几个非常重要的能力：

  • insert ... returning
  • insert ... on conflict do update
  • 批量插入
  • 公共表达式与子查询
  • 强大的函数和表达式系统

而 jOOQ 的价值就在于：

把这些数据库原生能力，继续以 Java 强类型 DSL 的方式表达出来。

也就是说，jOOQ 并不会“抹平”数据库特性，反而会尽量保留数据库原生表达力。

二、先明确几种写入语义

在进入代码之前，先把几个容易混淆的概念区分开。

1. insert

语义很明确：

就是新增，如果冲突了就报错

适合场景：

  • 业务明确要求新增
  • 重复数据必须视为异常
  • 主键或唯一键冲突应立即暴露

2. update

语义也很明确：

只更新已有数据，不存在就更新 0 行

适合场景：

  • 明确按 id / 唯一键更新
  • 业务上“不存在就不更新”是合理行为

3. 应用层 saveOrUpdate

它的逻辑通常是：

  • 先查
  • 再决定 insert 或 update

例如：

if (id == null) {
  insert();
} else {
  update();
}

或者：

if (exist == null) {
  insert();
} else {
  update();
}

它的特点是：

  • 逻辑清晰
  • 容易理解
  • 但通常至少两步数据库交互
  • 并发下未必原子

4. 数据库层 UPSERT

这类语义在 PostgreSQL 中通常是：

insert into ...
on conflict (...) do update ...

它的特点是：

  • 一条 SQL 完成
  • 原子性更好
  • 更适合并发写入
  • 更贴近数据库原生能力

所以要记住一句话：

应用层 saveOrUpdate 和 PostgreSQL UPSERT，不是一回事。

三、PostgreSQL UPSERT 基础概念

PostgreSQL 的 UPSERT 依赖唯一约束或主键冲突检测。

例如，假设 system_admin.login_name 上有唯一约束：

ALTER TABLE system_admin
ADD CONSTRAINT uk_system_admin_login_name UNIQUE (login_name);

那么就可以写：

insert into system_admin(login_name, password)
values ('litong', '123456')
on conflict (login_name)
do update set password = excluded.password;

语义是：

  • 如果 login_name 不存在，执行插入
  • 如果 login_name 已存在，执行更新

这里的：

excluded.password

表示本次尝试插入的那一行里的值。

四、jOOQ 中的 UPSERT 写法

先假设我们已经有静态导入：

import static demo.jooq.gen.tables.SystemAdmin.SYSTEM_ADMIN;

4.1 基于唯一键的 UPSERT

public int upsertByLoginName(String loginName, String password) {
  return useDsl()
      .insertInto(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .onConflict(SYSTEM_ADMIN.LOGIN_NAME)
      .doUpdate()
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .execute();
}

说明

这一段对应的 PostgreSQL 语义就是：

insert into system_admin(login_name, password)
values (?, ?)
on conflict (login_name)
do update set password = ?

它的优点非常明显：

  • 一条 SQL 完成
  • 无需先查再写
  • 并发下更稳妥
  • 意图清晰

4.2 使用 excluded(...)

在 UPSERT 中，更新值往往直接来自“插入尝试值”。

这时可以使用 excluded(...) 风格表达。

public int upsertByLoginName2(String loginName, String password) {
  return useDsl()
      .insertInto(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .onConflict(SYSTEM_ADMIN.LOGIN_NAME)
      .doUpdate()
      .set(SYSTEM_ADMIN.PASSWORD, DSL.excluded(SYSTEM_ADMIN.PASSWORD))
      .execute();
}

这个写法更贴近 PostgreSQL 原生 SQL：

  • 插入什么值
  • 冲突后就用这次插入值覆盖

这样做的好处是：后续字段多时更统一。

4.3 基于主键的 UPSERT

如果业务语义是“按主键冲突处理”，也可以这样写：

public int upsertById(Integer id, String loginName, String password) {
  return useDsl()
      .insertInto(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.ID, id)
      .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .onConflict(SYSTEM_ADMIN.ID)
      .doUpdate()
      .set(SYSTEM_ADMIN.LOGIN_NAME, DSL.excluded(SYSTEM_ADMIN.LOGIN_NAME))
      .set(SYSTEM_ADMIN.PASSWORD, DSL.excluded(SYSTEM_ADMIN.PASSWORD))
      .execute();
}

不过在自增主键场景里，业务上更常见的 UPSERT 键通常不是 id，而是某种业务唯一键。

例如：

  • login_name
  • email
  • tenant_id + code
  • biz_type + biz_id

五、UPSERT 返回主键或完整对象

在 PostgreSQL 中，returning 是非常强大的能力。

它允许你在写入后直接拿回：

  • 主键
  • 若干字段
  • 甚至整行

jOOQ 对此支持很好。

5.1 插入后返回主键

public Integer insertAndReturnId(String loginName, String password) {
  return useDsl()
      .insertInto(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .returning(SYSTEM_ADMIN.ID)
      .fetchOne(SYSTEM_ADMIN.ID);
}

说明

这里不是先插入、再额外查询，而是借助 PostgreSQL 的 returning 一次完成。

这种写法比单纯依赖 JDBC 自动回填主键更显式。

5.2 插入后返回整条 Record

public SystemAdminRecord insertAndReturnRecord(String loginName, String password) {
  return useDsl()
      .insertInto(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .returning()
      .fetchOne();
}

适用场景

  • 想拿到完整插入结果
  • 数据库有默认值、触发器、更新时间字段等
  • 想看到最终落库后的真实记录

5.3 插入后返回 POJO

public demo.jooq.gen.tables.pojos.SystemAdmin insertAndReturnPojo(String loginName, String password) {
  return useDsl()
      .insertInto(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .returning()
      .fetchOneInto(demo.jooq.gen.tables.pojos.SystemAdmin.class);
}

这种写法很适合 DAO 对外返回纯数据对象。

5.4 UPSERT 后返回结果

PostgreSQL 的 on conflict ... do update 同样可以搭配 returning。

public SystemAdminRecord upsertAndReturnRecord(String loginName, String password) {
  return useDsl()
      .insertInto(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .onConflict(SYSTEM_ADMIN.LOGIN_NAME)
      .doUpdate()
      .set(SYSTEM_ADMIN.PASSWORD, DSL.excluded(SYSTEM_ADMIN.PASSWORD))
      .returning()
      .fetchOne();
}

这非常适合“写入后立刻拿最终状态”的业务。

六、returning 和 Record.insert() 回填主键的区别

前一篇里我们讲过：

record.insert();
record.getId();

很多情况下也能拿到主键。

但两者还是有区别的。

1. record.insert() 后取主键

优点：

  • 写法自然
  • 对单行 Record 插入很方便

局限：

  • 更依赖底层驱动 / 方言 / jOOQ 配置的默认处理
  • 返回范围通常主要是回填 identity 字段

2. returning(...)

优点：

  • 语义更明确
  • 可返回任意字段
  • 可返回整条记录
  • 很适合 PostgreSQL 场景

所以在 PostgreSQL 中，如果你明确需要返回值，通常更推荐：

优先使用 returning(...)。

七、批量更新怎么做

批量更新的“批量”，其实有几种不同含义，必须区分清楚。

1. 相同 SQL 模板，多组参数

例如批量把多个人的密码更新成不同值：

  • 每条记录条件不同
  • 每条记录更新值也不同

这种适合 JDBC batch 风格。

2. 一条 SQL，更新多行

例如：

  • 把某一组用户状态统一改成禁用
  • 把一批 id 对应的数据统一标记删除

这种适合单条 update ... where in (...)

3. 更复杂的批量差异更新

例如：

  • 每行更新不同列
  • 需要 case when
  • 或借助临时表 / CTE

这就属于高级批量更新场景。

八、同条件批量更新

先看最简单、也最常见的一种。

8.1 一组 id 统一更新

public int batchUpdatePassword(List<Integer> ids, String newPassword) {
  if (ids == null || ids.isEmpty()) {
    return 0;
  }

  return useDsl()
      .update(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.PASSWORD, newPassword)
      .where(SYSTEM_ADMIN.ID.in(ids))
      .execute();
}

特点

  • 一条 SQL
  • 性能通常很好
  • 适合统一改同一个值

这是最推荐优先考虑的批量更新方式。

因为它最简单，也最容易让数据库优化。

九、差异化批量更新：JDBC Batch 风格

如果每条记录要更新成不同值，例如：

  • id=1 改成 a
  • id=2 改成 b
  • id=3 改成 c

这时通常可以用 jOOQ 的 batch。

9.1 批量提交多条 update

public int[] batchUpdateDifferentPassword(List<demo.jooq.gen.tables.pojos.SystemAdmin> list) {
  if (list == null || list.isEmpty()) {
    return new int[0];
  }

  List<org.jooq.Query> queries = new ArrayList<>();

  for (demo.jooq.gen.tables.pojos.SystemAdmin pojo : list) {
    org.jooq.Query query = useDsl()
        .update(SYSTEM_ADMIN)
        .set(SYSTEM_ADMIN.PASSWORD, pojo.getPassword())
        .where(SYSTEM_ADMIN.ID.eq(pojo.getId()));
    queries.add(query);
  }

  return useDsl().batch(queries).execute();
}

说明

这里的语义是：

  • 每条更新 SQL 都独立
  • 但通过 JDBC batch 一次批处理提交

适合：

  • 每行更新值不同
  • 逻辑仍然相对简单

9.2 基于 Record 的批量存储

如果你拿到的是 Record 集合，也可以考虑：

public int[] batchStoreRecords(List<SystemAdminRecord> records) {
  if (records == null || records.isEmpty()) {
    return new int[0];
  }

  return useDsl().batchStore(records).execute();
}

注意

batchStore 的语义依赖每个 Record 的状态：

  • 有些会 insert
  • 有些会 update

所以它方便，但不一定是最清晰的方式。

团队协作时，如果希望 SQL 意图更明确，通常还是：

  • 批量 insert 用 batchInsert
  • 批量 update 用 batch(query...)

更容易读懂。

十、复杂差异更新：CASE WHEN

如果想把多行更新压缩成一条 SQL，可以考虑 case when。

例如按 id 更新不同密码：

public int updatePasswordByCase(List<demo.jooq.gen.tables.pojos.SystemAdmin> list) {
  if (list == null || list.isEmpty()) {
    return 0;
  }

  org.jooq.CaseConditionStep<String> caseStep = null;
  List<Integer> ids = new ArrayList<>();

  for (demo.jooq.gen.tables.pojos.SystemAdmin pojo : list) {
    ids.add(pojo.getId());
    if (caseStep == null) {
      caseStep = DSL.when(SYSTEM_ADMIN.ID.eq(pojo.getId()), pojo.getPassword());
    } else {
      caseStep = caseStep.when(SYSTEM_ADMIN.ID.eq(pojo.getId()), pojo.getPassword());
    }
  }

  return useDsl()
      .update(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.PASSWORD, caseStep.otherwise(SYSTEM_ADMIN.PASSWORD))
      .where(SYSTEM_ADMIN.ID.in(ids))
      .execute();
}

特点

优点：

  • 一条 SQL 完成
  • 减少数据库往返

缺点：

  • SQL 会变长
  • 可读性会下降
  • 不适合非常大的批次

通常只有在明确有性能需求时才建议这样做。

十一、批量插入再深入一点

前一篇已经讲了 batchInsert，这里再补充两类实际工程建议。

11.1 用 batchInsert 批量导入 POJO

public int[] batchInsert(List<demo.jooq.gen.tables.pojos.SystemAdmin> pojos) {
  if (pojos == null || pojos.isEmpty()) {
    return new int[0];
  }

  List<SystemAdminRecord> records = new ArrayList<>();

  for (demo.jooq.gen.tables.pojos.SystemAdmin pojo : pojos) {
    SystemAdminRecord record = useDsl().newRecord(SYSTEM_ADMIN, pojo);
    records.add(record);
  }

  return useDsl().batchInsert(records).execute();
}

11.2 分批导入

public void batchInsertByChunk(List<demo.jooq.gen.tables.pojos.SystemAdmin> pojos, int chunkSize) {
  if (pojos == null || pojos.isEmpty()) {
    return;
  }

  int size = pojos.size();
  for (int i = 0; i < size; i += chunkSize) {
    int end = Math.min(i + chunkSize, size);
    List<demo.jooq.gen.tables.pojos.SystemAdmin> sub = pojos.subList(i, end);
    batchInsert(sub);
  }
}

为什么推荐分批

原因很简单：

  • 避免单批过大占用内存
  • 避免 JDBC batch 过长
  • 避免数据库压力瞬时过高
  • 出错时更容易定位

一般实践里，常见起步值是：

  • 200
  • 500
  • 1000

具体还是要压测。

十二、插入多行并返回结果

这里要注意一个现实问题：

批量 insert 后“逐行返回所有主键”这件事，并不是所有数据库 / 驱动 / 批处理方式都天然适合。

所以在 PostgreSQL 中，如果业务强依赖“插入后必须立刻拿到所有返回值”，通常有两种思路：

1. 小批量逐条 insert + returning

优点：

  • 返回值最明确
  • 编码简单

缺点：

  • 往返次数更多

2. 构造多 values 插入 + returning

jOOQ 可以构造单条多 values 的 insert，再 returning()。

示意写法如下：

public List<SystemAdminRecord> insertManyAndReturn(List<demo.jooq.gen.tables.pojos.SystemAdmin> pojos) {
  if (pojos == null || pojos.isEmpty()) {
    return List.of();
  }

  var step = useDsl()
      .insertInto(SYSTEM_ADMIN, SYSTEM_ADMIN.LOGIN_NAME, SYSTEM_ADMIN.PASSWORD);

  var valuesStep = step.values(pojos.get(0).getLoginName(), pojos.get(0).getPassword());

  for (int i = 1; i < pojos.size(); i++) {
    valuesStep = valuesStep.values(pojos.get(i).getLoginName(), pojos.get(i).getPassword());
  }

  return valuesStep
      .returning()
      .fetch();
}

适用场景

  • 一次插入数量不大
  • 明确需要返回每行结果
  • PostgreSQL 场景

不过这类写法在数据量很大时就不一定适合了。

十三、带返回值的 update / delete

PostgreSQL 不只是 insert 支持 returning，update / delete 也支持。

这在很多业务场景里非常有用。

13.1 update 后返回更新结果

public SystemAdminRecord updatePasswordAndReturn(Integer id, String newPassword) {
  return useDsl()
      .update(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.PASSWORD, newPassword)
      .where(SYSTEM_ADMIN.ID.eq(id))
      .returning()
      .fetchOne();
}

适合场景

  • 更新后要拿最新记录
  • 省去再查一次
  • 想确认最终值

13.2 delete 后返回被删除记录

public SystemAdminRecord deleteAndReturn(Integer id) {
  return useDsl()
      .deleteFrom(SYSTEM_ADMIN)
      .where(SYSTEM_ADMIN.ID.eq(id))
      .returning()
      .fetchOne();
}

这在做审计日志、回收站、删除前备份时非常方便。

十四、高级 SQL：子查询更新

复杂业务里，经常会有“根据子查询结果来更新”的需求。

jOOQ 在这方面非常自然。

例如：

public int updatePasswordByLoginNameSubquery(String loginName, String newPassword) {
  return useDsl()
      .update(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.PASSWORD, newPassword)
      .where(SYSTEM_ADMIN.ID.in(
          useDsl()
              .select(SYSTEM_ADMIN.ID)
              .from(SYSTEM_ADMIN)
              .where(SYSTEM_ADMIN.LOGIN_NAME.eq(loginName))
      ))
      .execute();
}

虽然这个例子比较简单，但它说明了一个要点：

jOOQ 的子查询和主查询可以自然拼接，且全部保持强类型。

十五、高级 SQL：存在性判断

有些业务只关心“是否存在”，不需要真正查出整行。

这时不要先 fetchOne() 再判断非空，更推荐直接让数据库做存在性判断。

public boolean existsByLoginName(String loginName) {
  return useDsl()
      .fetchExists(
          useDsl()
              .selectOne()
              .from(SYSTEM_ADMIN)
              .where(SYSTEM_ADMIN.LOGIN_NAME.eq(loginName))
      );
}

优点

  • 语义更明确
  • 数据库优化更直接
  • 不需要真的把整行拉回应用层

十六、高级 SQL：聚合查询

jOOQ 不只是 CRUD，也很适合统计查询。

例如查询管理员总数：

public int countAll() {
  return useDsl()
      .selectCount()
      .from(SYSTEM_ADMIN)
      .fetchOne(0, int.class);
}

例如按密码分组统计数量：

public List<org.jooq.Record2<String, Integer>> countGroupByPassword() {
  return useDsl()
      .select(
          SYSTEM_ADMIN.PASSWORD,
          DSL.count().cast(Integer.class)
      )
      .from(SYSTEM_ADMIN)
      .groupBy(SYSTEM_ADMIN.PASSWORD)
      .fetch();
}

如果不想直接返回 Record2，也可以映射到自定义 DTO。

十七、高级 SQL：表达式字段与别名

有时需要查询一个计算字段，例如拼接文本：

public List<org.jooq.Record2<Integer, String>> selectDisplayName() {
  return useDsl()
      .select(
          SYSTEM_ADMIN.ID,
          DSL.concat(DSL.inline("admin:"), SYSTEM_ADMIN.LOGIN_NAME).as("display_name")
      )
      .from(SYSTEM_ADMIN)
      .fetch();
}

这说明 jOOQ 并不是只能查“表原字段”，也非常适合构建表达式列。

十八、推荐的 DAO 示例

下面给出一个把本文重点串起来的 DAO 示例。

package demo.jooq.dao;

import static demo.jooq.gen.tables.SystemAdmin.SYSTEM_ADMIN;

import java.util.ArrayList;
import java.util.List;

import org.jooq.DSLContext;
import org.jooq.Query;
import org.jooq.impl.DSL;

import nexus.io.annotation.Inject;

import demo.jooq.gen.tables.pojos.SystemAdmin;
import demo.jooq.gen.tables.records.SystemAdminRecord;
import demo.jooq.tx.TransactionContext;

public class SystemAdminAdvancedDao {

  @Inject
  private DSLContext dsl;

  private DSLContext useDsl() {
    DSLContext txDsl = TransactionContext.get();
    return txDsl != null ? txDsl : dsl;
  }

  public Integer insertAndReturnId(String loginName, String password) {
    return useDsl()
        .insertInto(SYSTEM_ADMIN)
        .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
        .set(SYSTEM_ADMIN.PASSWORD, password)
        .returning(SYSTEM_ADMIN.ID)
        .fetchOne(SYSTEM_ADMIN.ID);
  }

  public SystemAdminRecord insertAndReturnRecord(String loginName, String password) {
    return useDsl()
        .insertInto(SYSTEM_ADMIN)
        .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
        .set(SYSTEM_ADMIN.PASSWORD, password)
        .returning()
        .fetchOne();
  }

  public SystemAdminRecord upsertAndReturn(String loginName, String password) {
    return useDsl()
        .insertInto(SYSTEM_ADMIN)
        .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
        .set(SYSTEM_ADMIN.PASSWORD, password)
        .onConflict(SYSTEM_ADMIN.LOGIN_NAME)
        .doUpdate()
        .set(SYSTEM_ADMIN.PASSWORD, DSL.excluded(SYSTEM_ADMIN.PASSWORD))
        .returning()
        .fetchOne();
  }

  public int batchUpdatePassword(List<Integer> ids, String password) {
    if (ids == null || ids.isEmpty()) {
      return 0;
    }

    return useDsl()
        .update(SYSTEM_ADMIN)
        .set(SYSTEM_ADMIN.PASSWORD, password)
        .where(SYSTEM_ADMIN.ID.in(ids))
        .execute();
  }

  public int[] batchUpdateDifferentPassword(List<SystemAdmin> list) {
    if (list == null || list.isEmpty()) {
      return new int[0];
    }

    List<Query> queries = new ArrayList<>();
    for (SystemAdmin pojo : list) {
      Query query = useDsl()
          .update(SYSTEM_ADMIN)
          .set(SYSTEM_ADMIN.PASSWORD, pojo.getPassword())
          .where(SYSTEM_ADMIN.ID.eq(pojo.getId()));
      queries.add(query);
    }

    return useDsl().batch(queries).execute();
  }

  public int[] batchInsert(List<SystemAdmin> pojos) {
    if (pojos == null || pojos.isEmpty()) {
      return new int[0];
    }

    List<SystemAdminRecord> records = new ArrayList<>();
    for (SystemAdmin pojo : pojos) {
      SystemAdminRecord record = useDsl().newRecord(SYSTEM_ADMIN, pojo);
      records.add(record);
    }

    return useDsl().batchInsert(records).execute();
  }

  public boolean existsByLoginName(String loginName) {
    return useDsl().fetchExists(
        useDsl()
            .selectOne()
            .from(SYSTEM_ADMIN)
            .where(SYSTEM_ADMIN.LOGIN_NAME.eq(loginName))
    );
  }

  public SystemAdminRecord updateAndReturn(Integer id, String password) {
    return useDsl()
        .update(SYSTEM_ADMIN)
        .set(SYSTEM_ADMIN.PASSWORD, password)
        .where(SYSTEM_ADMIN.ID.eq(id))
        .returning()
        .fetchOne();
  }

  public SystemAdminRecord deleteAndReturn(Integer id) {
    return useDsl()
        .deleteFrom(SYSTEM_ADMIN)
        .where(SYSTEM_ADMIN.ID.eq(id))
        .returning()
        .fetchOne();
  }
}

十九、UPSERT 与事务边界的关系

虽然 PostgreSQL UPSERT 本身是一条原子 SQL，但它不意味着整个业务方法就不需要事务。

例如下面这种业务：

  • UPSERT 管理员
  • 再插入审计日志
  • 再更新别的表

如果这三步要求“一起成功或一起失败”，那仍然应该放在 service 层事务里。

例如：

@ATransactional
public void createOrUpdateAdmin(String loginName, String password) {
  systemAdminAdvancedDao.upsertAndReturn(loginName, password);
  auditLogDao.insert("system_admin upsert");
}

要记住：

单条 SQL 的原子性，不等于整个业务流程的事务一致性。

二十、什么时候优先用 UPSERT

推荐优先考虑 UPSERT 的场景：

1. 存在明确唯一键

例如：

  • login_name
  • email
  • biz_code

2. 业务允许“有则改，无则增”

例如：

  • 同步外部系统主数据
  • 账号初始化
  • 配置项覆盖写入

3. 有并发写入可能

UPSERT 比“先查再写”更稳。

二十一、什么时候不建议滥用 UPSERT

以下场景要谨慎：

1. 业务规则复杂

例如：

  • 已存在时不是简单更新
  • 需要比较旧值
  • 需要检查状态流转是否合法

这时通常应该：

  • 先查
  • 再做业务判断
  • 再决定更新方式

2. 需要非常明确地区分“新增成功”和“更新成功”

UPSERT 虽然方便，但会把两种语义融合在一起。

如果业务上两者是不同事件，那最好不要直接混写。

二十二、几个常见坑

1. UPSERT 必须依赖唯一键或主键

没有冲突约束，onConflict(...) 就没有意义。

所以用之前一定要确认数据库表结构已经具备：

  • 主键
  • 唯一约束
  • 或唯一索引

2. 批量更新不一定要用 batch

如果是“一批 id 统一改同一个值”，优先用：

update ... where id in (...)

这通常比 batch 多条 update 更自然。

3. returning() 很强，但不要无脑全返回

如果你只需要主键，就写：

returning(SYSTEM_ADMIN.ID)

而不是默认整行返回。

这样更清晰，也更节省数据传输。

4. batchStore() 虽方便，但意图不如显式 batch 清楚

批量 insert、批量 update、混合 save，最好分开表达。

可读性会更好。

5. PostgreSQL 特性很强，但要承认它是方言能力

像：

  • returning
  • on conflict

都带有 PostgreSQL 方言色彩。

这不是缺点，但要意识到：

如果项目目标是深度利用 PostgreSQL，那就应该主动拥抱方言能力，而不是假装自己在写“数据库无关 SQL”。

这恰好也是 jOOQ 非常适合的地方。

二十三、总结

这一篇的核心，其实可以压缩成一句话：

在 tio-boot + jOOQ + PostgreSQL 体系中，不要只把 jOOQ 当成 CRUD 工具，而要把它当成“强类型的 PostgreSQL SQL 表达层”。

通过本文，我们完成了：

  • 使用 onConflict(...).doUpdate() 实现 PostgreSQL UPSERT
  • 使用 returning(...) 获取主键、Record、POJO
  • 区分应用层 saveOrUpdate 和数据库层 UPSERT
  • 掌握同值批量更新与差异化批量更新
  • 理解 batchInsert、batch(query...)、batchStore 的差异
  • 使用 fetchExists、聚合查询、表达式字段扩展高级 SQL 能力

到这里，整个 tio-boot + jOOQ 体系已经具备了相当完整的数据库访问能力：

  • 强类型表字段引用
  • Record / POJO 分层
  • Service 层事务边界
  • PostgreSQL 原生 UPSERT
  • 批量写入与更新
  • 返回主键与返回整行
  • 高级 SQL 表达能力

一句话总结：

jOOQ 的真正价值，不只是替代 XML，而是让你在 Java 中优雅地保留数据库原生能力。