JOOQ Howto fetch a result of join into POJO without flattening properties?

Question

I have a following query where I join tables A,B, and C:

• C is related to B via C.B_ID
• B is related to A via B.A_ID

I want to retrieve a report, where for each C, I want to retrieve also fields from corresponding B and A. If only a subset of fields is required, a projection and fetching to a POJO (with required properties from C, B, A) is an obvious approach.

class CReportDTO {
    Long c_id;
    Long c_field1;
    Long c_bid;
    Long b_field1;
    // ...
    CReportDTO(Long c_id, Long c_field1, Long c_bid, Long b_field1) {
        // ...
    }

    // ..
}

public List<CReportDTO> getPendingScheduledDeployments() {
        return dslContext.select(
                C.ID,
                C.FIELD1,
                C.B_ID,
                B.FIELD1,
                B.A_ID
                A.FIELD1,
                A.FIELD2
                )
                .from(C)
                .join(B)
                .on(C.B_ID.eq(B.ID))
                .join(A)
                .on(B.A_ID.eq(A.ID))
                .fetchInto(CReportDTO.class);
    };
}

My question

In case where all fields are needed I would prefer to have my report DTO contain A, B, C POJOs, without flattening them out:

class CReportDTO2 {
    C c;
    B b;
    A a;

    CReportDTO2(C c, B b, A a) {
        // ...
    }

    // ..
}

Is it possible to modify my query to:

• include all fields from each table
• massage it into CReportDTO2 without too much verbosity

Answer 1

You can use a lesser known feature of jOOQ's DefaultRecordMapper by aliasing your fields using a dot notation that denotes the nesting structure of your DTO:

public List<CReportDTO> getPendingScheduledDeployments() {
    return dslContext.select(
    // Add these       vvvvvvvvvvvvvvvvvvvv
        C.ID       .as("c.c_id"),
        C.FIELD1   .as("c.c_field1"),
        C.B_ID     .as("c.b_id"),
        B.FIELD1   .as("b.b_field1"),
        B.A_ID     .as("b.a_id")
        A.FIELD1   .as("a.a_field1"),
        A.FIELD2   .as("a.a_field2")
    )
    .from(C)
    .join(B)
    .on(C.B_ID.eq(B.ID))
    .join(A)
    .on(B.A_ID.eq(A.ID))
    .fetchInto(CReportDTO2.class);
}

See Javadoc

If Field.getName() is MY_field.MY_nested_field (case-sensitive!), then this field's value will be considered a nested value MY_nested_field, which is set on a nested POJO

Note that this doesn't work with the constructor you've provided. You'll have to provide a default constructor as well, and make your fields non-final (in case they are).