如何使用jooq进行批量更新

Question

使用以下方法使用jOOQ进行更新。

for (Balance balance : balances) {
       dslContext.update(BALANCE)
                 .set(BALANCE.AMOUNT, balance.getAmount())
                 .where(BALANCE.ID.eq(balance.getId))
                 .execute();
}

这个代码遍历所有余额并插入每个余额。我知道可以使用UpdatableRecord完成此操作。但是我想避免从数据库中提取余额。余额是一个具有8个以上字段的表，但我只关心更新一个字段。是否有其他方法可以在不使用UpdatableRecord的情况下完成此操作？

- nanpakal

---

你的(id, amount)对是从哪里来的？ - Lukas Eder

我从外部服务收到余额列表。因此，对于这个方法，它是通过方法参数实现的。 - nanpakal

Answer 1

使用UpdatableRecord的批量语句

如果你想要使用DSLContext.batchUpdate()的便利性，而不必先从数据库中获取所有记录，则仍然可以使用UpdatableRecord。假设你正在使用代码生成器，并且正在生成记录，则将拥有BalanceRecord：

ctx.batchUpdate(balances
   .stream()
   .map(b -> { 
       var r = new BalanceRecord();
       r.setAmount(b.getAmount());
       r.setId(b.getId());
       r.changed(BALANCE.ID, false); // Prevent setting the ID to itself
       return r;
   })
   .collect(toList()))
   .execute();

这将为您在幕后创建一个批处理语句。

使用BatchedConnection

从 jOOQ 3.14 开始，您可以使用BatchedConnection透明地批处理所有逻辑，它是一个特殊的 JDBC 连接代理，延迟执行所有 JDBC 语句（无论是 jOOQ 创建还是其他），并将它们缓冲直到需要执行：

dslContext.batched(c -> {
    for (Balance balance : balances) {
        c.dsl().update(BALANCE)
               .set(BALANCE.AMOUNT, balance.getAmount())
               .where(BALANCE.ID.eq(balance.getId))
               .execute(); // This doesn't execute the query yet
    }
} // Now, the buffered queries are being batch-executed

运行单个批量语句

从您的评论中，似乎希望将此作为单个批量语句运行，而不是在单个批量语句（单次往返）中批处理多个语句。

我并不确定这是否是正确的方法 - 语句可能会变得非常庞大，并不一定比批处理更快，但当然，您可以使用CASE表达式来实现这一点。

ctx.update(BALANCE)
   .set(BALANCE.AMOUNT, 
     case_(BALANCE.ID).mapValues(
       balances.stream().collect(toMap(balance::getId, balance::getValue))
     )
   )
   .where(BALANCE.ID.in(balances.stream().map(balance::getId).collect(toList())))
   .execute();

这将产生类似下面的内容:

UPDATE balance
SET amount = 
  CASE id
    WHEN 1 THEN 2.50
    WHEN 2 THEN 3.50
    WHEN 13 THEN 8.30
  END
WHERE id IN (1, 2, 13)

根据你使用的方言，这可能更适合使用MERGE来完成：

MERGE INTO balance
USING (
  VALUES (1, 2.50), (2, 3.50), (13, 8.30)
) AS s (i, b)
ON balance.id = s.i 
AND balance.balance = s.b
WHEN MATCHED THEN UPDATE SET balance = b

- Lukas Eder

---

DSLContext.batchMerge() 函数仅在 jOOQ 3.14 中添加（目前还未发布）。您只需要将 batchUpdate() 调用更改为 batchMerge() 即可。 - Lukas Eder

上面的查询不会生成批量更新，而是生成许多更新查询。批量更新是否与报告中提到的批量插入类似？https://github.com/jOOQ/jOOQ/issues/4533 - nanpakal

@pppavan：这取决于您对“batch”的理解。jOOQ与JDBC使用相同的方式来使用“batch”，即“将一批语句发送到服务器”。您是指“批量”更新吗？ - Lukas Eder

谢谢。我使用绑定变量和UpdatableRecord进行了更新测试，其中绑定变量比UpdatableRecord表现更好，快了10倍。这是预期行为吗？ - nanpakal

@MaximBezmen：听起来像是一个新问题，不是我能够在 Stack Overflow 的评论功能限制内合理解释的东西... - Lukas Eder

Answer 2

你的代码没问题，但你可以像这样使用批量更新：

List<UpdateConditionStep<BalanceRecord> updates = new ArrayList<>();
for (Balance balance : balances) {
   updates.add(dslContext.update(BALANCE)
             .set(BALANCE.AMOUNT, balance.getAmount())
             .where(BALANCE.ID.eq(balance.getId)));
}

dslContext.batch(updates).execute();

文档：https://www.jooq.org/doc/3.14/manual-single-page/#batch-execution

提示：注意在没有锁定的情况下更新余额时避免丢失更新。

- Simon Martinelli

---

看起来它没有使用绑定的值？ - nanpakal

不是吗？执行的 SQL 是什么样子的？ - Simon Martinelli

这对应于批处理几个不一定相关的语句。使用JDBC，无法使用绑定变量来完成此操作。您要寻找的是批处理单个查询并使用bind（...）提供绑定变量集，位于同一页上：https://www.jooq.org/doc/3.14/manual/sql-execution/batch-execution/ - Lukas Eder