(For the record, this same question was posted on SO:
https://stackoverflow.com/questions/64027819/batching-stores-transparently.
I will update the question there to reflect any information gleaned on this
user group conversation.)
We are using the following frameworks and versions:
- jOOQ 3.11.1
- Spring Boot 2.3.1.RELEASE
- Spring 5.2.7.RELEASE
I have an issue where some of our business logic is divided into logical
units that look as follows:
- Request containing a user transaction is received
- This request contains various information, such as the type of
transaction, which products are part of this transaction, what kind of
payments were done, etc.
- These attributes are then stored individually in the database.
In code, this looks approximately as follows:
TransactionRecord transaction = transactionRepository.create();
transaction.create(creationCommand);`
In Transaction#create (which runs transactionally), something like the
following occurs:
storeTransaction();
storePayments();
storeProducts();// ... other relevant information
A given transaction can have many different types of products and
attributes, all of which are stored. Many of these attributes result in
UPDATE statements, while some may result in INSERT statements - it is
difficult to fully know in advance.
For example, the storeProducts method looks approximately as follows:
products.forEach(product -> {
ProductRecord record = productRepository.findProductByX(...);
if (record == null) {
record = productRepository.create();
record.setX(...);
record.store();
} else {
// do something else
}
});
If the products are new, they are INSERTed. Otherwise, other calculations
may take place. Depending on the size of the transaction, this single user
transaction could obviously result in up to O(n) database calls/roundtrips,
and even more depending on what other attributes are present. In
transactions where a large number of attributes are present, this may
result in upwards of hundreds of database calls for a single request (!). I
would like to bring this down as close as possible to O(1) so as to have
more predictable load on our database.
Naturally, batch and bulk inserts/updates come to mind here. What I would
like to do is to batch all of these statements into a single batch using
jOOQ, and execute after successful method invocation prior to commit. I
have found several (SO Post
<https://stackoverflow.com/questions/45274242/jooq-batch-record-insert>, jOOQ
API
<https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/DSLContext.html#batchStore%28java.util.Collection%29>,
jOOQ GitHub Feature Request <https://github.com/jOOQ/jOOQ/issues/3419>)
posts where this topic is implicitly mentioned, and one user groups post
<https://groups.google.com/forum/#!topic/jooq-user/RRAYf2OUO8U> that seemed
explicitly related to my issue.
Since I am using Spring together with jOOQ, I believe my ideal solution
(preferably declarative) would look something like the following:
@Batched(100) // batch size as parameter, potentially@Transactionalpublic void
createTransaction(CreationCommand creationCommand) {
// all inserts/updates above are added to a batch and executed on
successful invocation
}
Or perhaps set batching as the default behaviour with the option for
non-batched stores. For this to work, I imagine I’d need to manage a scoped
(ThreadLocal/Transactional/Session scope) resource which can keep track of
the current batch such that:
1. Prior to entering the method, an empty batch is created if the method
is @Batched,
2. A custom DSLContext (perhaps extending DefaultDSLContext) that is
made available via DI has a ThreadLocal flag which keeps track of
whether any current statements should be batched or not, and if so
3. Intercept the calls and add them to the current batch instead of
executing them immediatelly.
However, step 3 would currently necessitate having to rewrite a large
portion of our code from the (IMO) relatively readable:
records.forEach(record -> {
record.setX(...);
// ...
record.store();
}
to:
userObjects.forEach(userObject -> {
dslContext.insertInto(...).values(userObject.getX(), ...).execute();
}
which would defeat the purpose of having this abstraction in the first
place, since the second form can also be rewritten using
DSLContext#batchStore or DSLContext#batchInsert. IMO however, batching and
bulk insertion should not be up to the individual developer and should be
able to be handled transparently at a higher level (e.g. by the framework).
I find the readability of the jOOQ API to be an amazing benefit of using
it, however it seems that it does not lend itself (as far as I can tell) to
interception/extension very well for cases such as these. Is it possible,
with the jOOQ 3.11.1 (or even current) API, to get behaviour similar to the
former with transparent batch/bulk handling? What would this entail?
------------------------------
One possible but extremely hacky solution that comes to mind for enabling
transparent batching of stores would be something like the following:
1. Create a RecordListener and add it as a default to the Configuration
whenever batching is enabled.
2. In RecordListener#storeStart, add the query to the current
Transaction’s batch (e.g. in a ThreadLocal<List>)
3. The AbstractRecord has a changed flag which is checked (
org.jooq.impl.UpdatableRecordImpl#store0,
org.jooq.impl.TableRecordImpl#addChangedValues) prior to storing.
Resetting this (and saving it for later use) makes the store operation a
no-op.
4. Lastly, upon successful method invocation but prior to commit:
- Reset the changes flags of the respective records to the correct
values
- Invoke org.jooq.UpdatableRecord#store, this time without the
RecordListener or while skipping the storeStart method (perhaps using
another ThreadLocal flag to check whether batching has already been
performed).
As far as I can tell, this approach *should* work, in theory. Obviously,
it’s extremely hacky and prone to breaking as the library internals may
change at any time if the code depends on Reflection to work.
Is there currently a better way, using only the public jOOQ API?
Thanks!
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/03d3fa96-be8f-442f-bb43-6bd949a38bban%40googlegroups.com.
