JOOQ: Logically Group Columns From Different Tables In Common Interface

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

Solution 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>