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

例如：

java

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

或者：

java

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

它的特点是：

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

4. 数据库层 UPSERT

这类语义在 PostgreSQL 中通常是：

sql

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

它的特点是：

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

所以要记住一句话：

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

三、PostgreSQL UPSERT 基础概念

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

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

sql

ALTER TABLE system_admin
ADD CONSTRAINT uk_system_admin_login_name UNIQUE (login_name);

那么就可以写：

sql

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

语义是：

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

这里的：

sql

excluded.password

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

四、jOOQ 中的 UPSERT 写法

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

java

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

4.1 基于唯一键的 UPSERT

java

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 语义就是：

sql

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

它的优点非常明显：

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

4.2 使用 `excluded(...)`

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

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

java

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

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

java

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 插入后返回主键

java

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

java

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

java

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。

java

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()` 回填主键的区别

前一篇里我们讲过：

java

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 统一更新

java

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

java

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 集合，也可以考虑：

java

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 更新不同密码：

java

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

java

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 分批导入

java

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()。

示意写法如下：

java

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 后返回更新结果

java

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 后返回被删除记录

java

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

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

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

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

jOOQ 在这方面非常自然。

例如：

java

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() 再判断非空，更推荐直接让数据库做存在性判断。

java

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

优点

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

十六、高级 SQL：聚合查询

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

例如查询管理员总数：

java

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

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

java

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：表达式字段与别名

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

java

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 示例。

java

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 层事务里。

例如：

java

@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 统一改同一个值”，优先用：

java

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

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

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

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

java

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 中优雅地保留数据库原生能力。

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

一、为什么这一篇很重要 ​

二、先明确几种写入语义 ​

1. insert ​

2. update ​

3. 应用层 saveOrUpdate ​

4. 数据库层 UPSERT ​

三、PostgreSQL UPSERT 基础概念 ​

四、jOOQ 中的 UPSERT 写法 ​

4.1 基于唯一键的 UPSERT ​

说明 ​

4.2 使用 excluded(...) ​

4.3 基于主键的 UPSERT ​

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

5.1 插入后返回主键 ​

说明 ​

5.2 插入后返回整条 Record ​

适用场景 ​

5.3 插入后返回 POJO ​

5.4 UPSERT 后返回结果 ​

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

1. record.insert() 后取主键 ​

2. returning(...) ​

七、批量更新怎么做 ​

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

2. 一条 SQL，更新多行 ​

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

八、同条件批量更新 ​

8.1 一组 id 统一更新 ​

特点 ​

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

9.1 批量提交多条 update ​

说明 ​

9.2 基于 Record 的批量存储 ​

注意 ​

十、复杂差异更新：CASE WHEN ​

特点 ​

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

11.1 用 batchInsert 批量导入 POJO ​

11.2 分批导入 ​

为什么推荐分批 ​

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

1. 小批量逐条 insert + returning ​

2. 构造多 values 插入 + returning ​

适用场景 ​

十三、带返回值的 update / delete ​

13.1 update 后返回更新结果 ​

适合场景 ​

13.2 delete 后返回被删除记录 ​

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

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

优点 ​

十六、高级 SQL：聚合查询 ​

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

十八、推荐的 DAO 示例 ​

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

二十、什么时候优先用 UPSERT ​

1. 存在明确唯一键 ​

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

3. 有并发写入可能 ​

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

1. 业务规则复杂 ​

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

二十二、几个常见坑 ​

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

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

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

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

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

二十三、总结 ​

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

一、为什么这一篇很重要

二、先明确几种写入语义

1. insert

2. update

3. 应用层 `saveOrUpdate`

4. 数据库层 UPSERT

三、PostgreSQL UPSERT 基础概念

四、jOOQ 中的 UPSERT 写法

4.1 基于唯一键的 UPSERT

说明

4.2 使用 `excluded(...)`

4.3 基于主键的 UPSERT

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

5.1 插入后返回主键

说明

5.2 插入后返回整条 Record

适用场景

5.3 插入后返回 POJO

5.4 UPSERT 后返回结果

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

1. `record.insert()` 后取主键

2. `returning(...)`

七、批量更新怎么做

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

2. 一条 SQL，更新多行

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

八、同条件批量更新

8.1 一组 id 统一更新

特点

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

9.1 批量提交多条 update

说明

9.2 基于 Record 的批量存储

注意

十、复杂差异更新：CASE WHEN

特点

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

11.1 用 `batchInsert` 批量导入 POJO

11.2 分批导入

为什么推荐分批

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

1. 小批量逐条 insert + returning

2. 构造多 values 插入 + returning

适用场景

十三、带返回值的 update / delete

13.1 update 后返回更新结果

适合场景

13.2 delete 后返回被删除记录

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

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

优点

十六、高级 SQL：聚合查询

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

十八、推荐的 DAO 示例

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

二十、什么时候优先用 UPSERT

1. 存在明确唯一键

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

3. 有并发写入可能

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

1. 业务规则复杂

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

二十二、几个常见坑

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

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

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

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

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

二十三、总结