How to batch update using jooq

user_3627233 0
Viewed 6183

Using the following way to update using jOOQ.

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

This iterates over all balances and insert each. I know this can be done using UpdatableRecord. But i want to avoid fetching balance from database. Balance is table with more than 8 fields, but i am interested in updating only one field.Is there any other way to do this without UpdatableRecord?

jooq java
2 Answers

A batch statement using UpdatableRecord

You can still use UpdatableRecord if you want the convenience of DSLContext.batchUpdate(), without fetching all the records from the database first. Assuming you're using the code generator and you're generating records, you'll have 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();

This will create a batch statement behind the scenes for you.

Using a BatchedConnection

Starting with jOOQ 3.14, you can transparently batch all of your logic using a BatchedConnection, which is a special JDBC connection proxy that delays the execution of all your JDBC statements (jOOQ created or not), buffering them until an execution is required:

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

Running a single bulk statement

From your comments, there seems to be a desire to run this as a single bulk statement, instead of batching multiple statements in a single batch statement (single round trip).

I'm not convinced this is the right approach - the statement can get huge and is not necessarily faster than the batch, but of course, you can do this using a CASE expression

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

This will produce something like:

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)

Depending on the dialect you're using, this might be better done with 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
user_5217990

Your code is fine but you could use the batch updates like this:

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

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

Just a hint: Be careful of lost updates if you update a balance without locking.

user_10451420
Related