The jOOQ User Manual Code generation Embeddable types Embedded keys

previous : next

Embedded keys

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

A very useful application of embeddable types are PRIMARY KEYS, UNIQUE constraints, and FOREIGN KEYS. There is are only few good use-case of joining two tables by two columns that are not the FOREIGN KEY and its referenced PRIMARY / UNIQUE key. If two such columns are chosen, this is mostly because of a typo, or even because of misunderstanding the underlying schema.

You can turn on the feature like this:

<configuration>
  <generator>
    <database>

      <!-- Use regular expressions to match the keys that should be replaced by embeddables. -->
      <embeddablePrimaryKeys>.*</embeddablePrimaryKeys>
      <embeddableUniqueKeys>.*</embeddableUniqueKeys>
    </database>
  </generator>
</configuration>

new org.jooq.meta.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()

      // Use regular expressions to match the keys that should be replaced by embeddables.
      .withEmbeddablePrimaryKeys(".*")
      .withEmbeddableUniqueKeys(".*")
    )
  )

import org.jooq.meta.jaxb.*


configuration {
  generator {
    database {

      // Use regular expressions to match the keys that should be replaced by embeddables.
      embeddablePrimaryKeys = ".*"
      embeddableUniqueKeys = ".*"
    }
  }
}

configuration {
  generator {
    database {

      // Use regular expressions to match the keys that should be replaced by embeddables.
      embeddablePrimaryKeys = ".*"
      embeddableUniqueKeys = ".*"
    }
  }
}

generationTool {
  generator {
    database {

      // Use regular expressions to match the keys that should be replaced by embeddables.
      embeddablePrimaryKeys = ".*"
      embeddableUniqueKeys = ".*"
    }
  }
}

As always, when regular expressions are used, they are regular expressions with default flags.

This will automatically produce an embeddable type configuration for each PRIMARY KEY and/or UNIQUE key, as well as for each FOREIGN KEY referencing the PRIMARY KEY or UNIQUE key. The generated configuration could be written manually, but the configuration generation algorithm is not trivial when keys overlap, or when tables reference a "remote" primary key transitively, through a relationship table.

Applying this to our sample database, along with either well-designed constraint names (generated embeddables use constraint names), or a programmatic generator strategy, or a configurative matcher strategy, we might be getting org.jooq.EmbeddableRecord types like these:

// Both primary and foreign key produce the respective primary key record
Result<Record5<PkBookRecord, String, PkAuthorRecord, String, String>> result =
create.select(
            BOOK.PK_BOOK,
            BOOK.TITLE,
            BOOK.FK_BOOK_AUTHOR,
            AUTHOR.FIRST_NAME,
            AUTHOR.LAST_NAME)
      .from(BOOK)
      .join(AUTHOR)
      // This join compiles
      .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      // This wrong join wouldn't compile
      // .on(BOOK.LANGUAGE_ID.eq(AUTHOR.ID))
      .fetch();

for (Record5<PkBookRecord, String, PkAuthorRecord, String, String> record : result) {
    System.out.println("ID        : " + record.value1().getId());
    System.out.println("TITLE     : " + record.value2());
    System.out.println("AUTHOR_ID : " + record.value3().getId());
    System.out.println("FIRST_NAME: " + record.value4());
    System.out.println("LAST_NAME : " + record.value5());
}

Notice how:

• Each primary key produces an embeddable record type.
• Both primary key and foreign key columns reference the primary key record type.
• This means that only matching primary / foreign key columns can be compared in joins. It is not sufficient for them to be both of type java.lang.Integer

Composite keys

This feature is even more powerful when keys are composite! You no longer have to list each pair of join columns in your join predicates, just join by primary / foreign key embeddable type. Advantages are:

• You'll never forget a column again when joining by composite keys
• You'll never forget to refactor all your queries, in case you add / remove a column from a key. Just re-generate the jOOQ code, and the queries are automatically updated

The second bullet can also be achieved using implicit joins, or the synthetic ON KEY clause.

The jOOQ User Manual Code generation Embeddable types Embedded keys

previous : next