Search code examples
jooq

How to configure jOOQ Embeddable Types from SQL Query

We’ve got a database where there are quite a lot of embedded types. Often the same embedded type occurs multiple times in the same table. The respective columns follow a naming pattern, so it’s easy to identify the different occurrences using an SQL query for example.

What’s the best way to configure jOOQ so that these embedded types gets mapped by the code generator? Note that our real db contains hundreds of tables, so manually configuring this is a no-go.

Fictional example:

create table t(amount int, unit varchar2(4), amount_pend int, unit_pend varchar(4));
Solution

  • Re-using the same embeddable multiple times per table

    As of jOOQ 3.15, it is not yet possible to reference the same embeddable type more than once per table. You can only reference it from several tables, once each.

    This should obviously be fixed. It seems to be merely a code generation limitation. I've created a feature request for this: https://github.com/jOOQ/jOOQ/issues/12608

    Starting from jOOQ 3.17, you can declare:

    <embeddables>
    <embeddable>
        <name>MONETARY_AMOUNT</name>
        <referencingName>AMOUNT_WITH_UNIT</referencingName>
        <tables>T/tables>
        <fields>
            <field><expression>AMOUNT</expression></field>
            <field><expression>UNIT</expression></field>
        </fields>
    </embeddable>
    <embeddable>
        <name>MONETARY_AMOUNT</name>
        <referencingName>AMOUNT_WITH_UNIT_PEND</referencingName>
        <tables>T/tables>
        <fields>
            <field><name>AMOUNT</name><expression>AMOUNT_PEND</expression></field>
            <field><name>UNIT</name><expression>UNIT_PEND</expression></field>
        </fields>
    </embeddable>
</embeddables>

    For the time being, you'll have to produce 2 distinct embeddable types, which aren't compatible by name or type, only by structure.

    Generating code generation configuration dynamically

    The code generation configuration is a JAXB-annotated API, which happens to conveniently map to:

    But you can also use the code generation API programmatically yourself and thus generate the configuration elements dynamically: