JOOQ: batch CRUD operations using dynamic table definitions, how to?

Question

I'm looking for a way to dynamically create CRUD queries using JOOQ.

I have a heavily partitioned table and I'd like to perform batch inserts/updates/deletes directly into the right partition. Reason is significant performance gain in my scenario.

1. I have JOOQ-generated templates for root table.
2. Partitions for this table are generated dynamically.
3. I can create a table with required schema and partition name using DSL.table()
4. There is no way I can define Record type for newly created table.
5. Otherwise, if I create a table definition from RootRecord, I cannot alter table name.

Is there a way to achieve desired behaviour with JOOQ?

Answer 1

The simplest way I can think of is to use the logical table (e.g. from code generation) and apply table mapping at runtime to translate the table directly to the underlying partition.

E.g. you write:

List<MyTableRecord> records = ...
ctx.batchUpdate(records);

But jOOQ doesn't update the MY_TABLE table, but the MY_TABLE_1341 partition instead:

UPDATE my_table_1341 SET ...

This only works if you're writing to a single partition at a time.