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?
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.
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
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With