JOOQ: How to resolve foreign keys as objects?

Say I have a table that references another table, in this case “TestScenarios” references “TestSchemas”. So each TestScenario HAS-A TestSchema. I autogenerated DAOs, however, when fetching TestScenario instance via the DAO the TestSchema field is an integer, not a TestSchema-object. How can I get JOOQ to resolve foreign keys directly as objects up to a certain depth?

CREATE TABLE "TestScenarios"
(
   id              integer   DEFAULT nextval('"TestScenarios_id_seq"'::regclass) NOT NULL,
   name            varchar,
   version         bigint,
   "testSchema"    integer,
);

ALTER TABLE "TestScenarios"
   ADD CONSTRAINT "TestScenarios_pkey"
   PRIMARY KEY (id);

ALTER TABLE "TestScenarios"
  ADD CONSTRAINT "testSchemaFk" FOREIGN KEY ("testSchema")
  REFERENCES "TestSchemas" (id)
  ON UPDATE NO ACTION
  ON DELETE NO ACTION;

COMMIT;

Answer

DAOs don’t have such a feature, but with jOOQ’s DSL API, you could use implicit joins to quickly fetch also parent tables for any given child table, e.g.

ctx.select(TestScenarios.asterisk(), TestScenarios.TestSchemas().asterisk())
   .from(TestScenarios)
   .fetch();

There are other approaches, but there’s never going to be anything automatic about “object graph persistence” in the way JPA would offer it, for example. The jOOQ philosophy is to always express every query explicitly – maybe profit from some mapping sugar, but to never implicitly and automatically fetch entire object graphs.