JOOQ: Logically group columns from different tables in common interface

Question

We have a table design where a lot of tables share some columns, e.g. in one case some of our tables have the column markedForDeletion. In another case, multiple of our tables have the columns approvedAt and approvedBy. These tables don't share anything in terms of to be JOINED data and thus, I don't like to introduce a common JOIN table for those (It's also not an option due to performance issues).

But from application perspective, I do have quite similar tasks to be performed, here. For instance, if I create a new row, it doesn't matter in which of the tables I insert the entry, I need to extract from the request the approver and the request time to fill that into my table, along with the other line data later on in my code.

In JOOQ, I can now do something like

private void insertApprovalInformation(Record record, RequestContext ctx) {
   record.set(DSL.field("approver"), ctx.getRequestUser());
   record.set(DSL.field("approvedAt"), ctx.getRequestTime());
}

However, I'll loose all my beloved type safety with that approach. Ideally, I'd like to write something like

private void insertApprovalInformation(Approvablerecord record, RequestContext ctx) {
   record.set(ApprovableTable.APPROVER, ctx.getRequestUser());
   record.set(ApprovableTable.APPROVED_AT, ctx.getRequestTime());
}

I am aware that this is similar to the Postgres inheritance feature, but I'd like to have this more database independent and would like to use it in an Oracle database. I imagine something like configuring the JOOQ generator and tell it "These four tables belong to a logical group, which I name "approvable" and they all have the columns APPROVER and APPROVED_AT." which will let JOOQ generate the classes for instance implementing a marker interface for that group.

I think this could be a rather frequently used feature when thinking about historization, common tasks like approval, mark rows for deletion and so on.

My questions:

• Is there already a way in JOOQ to achieve a type safe result as I desire?
• Any other suggestions on how to approach this scenarion in a type safe way?
• Or should I just forget about the type-safetyness here?

Answer 1

Using embeddables from jOOQ 3.14

jOOQ 3.14 introduced "embeddable types", which thoroughly solve this problem on a code generator basis. You can define an embeddable like this:

<configuration>
  <generator>
    <database>
      <embeddables>
        <embeddable>
          <name>APPROVAL_INFORMATION</name>
          <fields>
            <field><expression>APPROVER</expression></field>
            <field><expression>APPROVED_AT</expression></field>
          </fields>
        </embeddable>
      </embeddables>
    </database>
  </generator>
</configuration>

(More specific configuration is possible, the manual for details). This will now generate an auxiliary EmbeddableRecord for you, of the form:

public class ApprovalInformationRecord
extends EmbeddableRecordImpl<ApprovalInformationRecord> {
    public ApprovalInformationRecord(
        String approver, LocalDateTime approvedAt
    ) { /* ... */ }
    
    // Getters, setters
}

You can use this embeddable instead of the underlying columns in queries, projections, etc. e.g.

Result<Record2<Long, ApprovalInformationRecord>> result =
ctx.select(T.ID, T.APPROVAL_INFORMATION)
   .from(T)
   .where(T.APPROVAL_INFORMATION.eq(new ApprovalInformationRecord(...))
   .fetch();

A generic solution to this using custom interfaces

You can easily configure and extend the jOOQ code generator to add the type information for you. Since you want to work on generated records, just add a new interface like this to your code base:

public interface Approvable {
    void setApprover(String approver);
    void setApprovedAt(Timestamp approvedAt);
}

And then configure the code generator to let all the relevant generated records implement the above interface using a generator strategy:

• Programmatic
• Configurative

A configurative example:

..
<generator>
  <strategy>
    <matchers>
      <tables>
        <table>
          <expression>MY_TABLE</expression>
          <recordImplements>com.example.Approvable</recordImplements>
        </table>
      </tables>
    </matchers>
  </strategy>
</generator>