Cannot convert row to model

Question

Using JOOQ 3.15, I attempting to map a nested item.

CREATE TABLE a_table (
    id uuid primary key
);


CREATE TABLE b_table (
    id uuid primary key
);

CREATE TABLE c_table (
    a_id uuid REFERENCES a_table(id) NOT NULL,
    b_id uuid REFERENCES b_table(id) NOT NULL,
    started_date timestamp with time zone,
    completed_date timestamp with time zone,
    PRIMARY KEY (a_id, b_id)
);

The classes are just standard POJOs with getters, setters, for above values.

Then I have a query like:

// aId, and bId are passed in as parameters
select(
        A_TABLE.asterisk(),
        row(
                C_TABLE.STARTED_DATE.as("dateStarted"),
                C_TABLE.COMPLETED_DATE.as("dateCompleted")
        ).as("c").convertFrom(r -> r.into(CDomain.class))
).from(A_TABLE)
.leftOuterJoin(C_TABLE)
        .on(C_TABLE.A_ID.eq(A_TABLE.ID).and(C_TABLE.B_ID.eq(bId)))
.where(A_TABLE.ID.eq(aId))).as("a").convertFrom(r -> r.into(ATableRecord.class))

Table C's entry may be non-existant.

The error I encounter when I run this code is:

org.jooq.exception.DataTypeException: Cannot convert from class C {
    dateStarted: null
    dateCompleted: null
} (class com.blah.C_TABLE) to class org.jooq.impl.RecordImpl4

Why is this error occurring? Why is it trying to convert from my class to a record, when I am asking it to convert the record to a class? How could I fix this?

Answer 1

I'm still not sure about the exact root cause of my bug, but I found that with current JOOQ (3.15 at time of writing), it is easiest to use Records, fieldsRow() and the auto-generated POJOs as so:

// Here ATableRecord and CTableRecord are autogenerated by JOOQ based on the table definitions
record ACContainerRecord (ATableRecord a, CTableRecord c);

select(
        A_TABLE.fieldsRow().as("a").convertFrom(r -> r.into(ATableRecord.class)),
        C_TABLE.fieldsRow().as("c").convertFrom(r -> r.into(CTableRecord.class))
).from(ATable)
    .leftOuterJoin(C_TABLE)
        .on(C_TABLE.A_ID.eq(A_TABLE.ID).and(C_TABLE.B_ID.eq(bId)))
    .where(A_TABLE.ID.eq(aId))).as("a")

    // Fetch into the custom holding record
    .fetch(Records.mapping(ACContainerRecord::new))

    // Map it onto our desired domain classes
    .stream()
    .map(r -> {
        // Imagine that the domain classes know how to construct themselves from JOOQ autogenerated records
        CDomain c = new CDomain(r.c());
        ADomain a = new ADomain(r.a(), c);
        return a;
    });