jOOQ: fetchInto with fieldsRow() returns null for Record object but not for POJOs

user_2230226 0
Viewed 63

I recently ran into some unexpected jOOQ behaviour. I'd written a query where I was mixing fetching into jOOQ-generated POJOs and jOOQ generated Record classes. A colleague correctly pointed out that I should be consistent and use just Record or just POJO classes, so I updated my query to fetch Record objects instead.

However, I updated the fetchInto call to point to a Record class instead of a POJO, and suddenly my previously-working and otherwise unchanged queries were returning Record objects where all fields were null!

Eventually I tried adjusting the select query and selected each relevant field individually, and then the relevant objects were populated correctly.

Why does this behaviour happen?

Code examples for each of the 3 scenarios:

fieldsRow() and fetching into a generated POJO, everything works as expected:

conf ->
    conf.dsl()
    .select(
        RUN.fieldsRow().as("run").convertFrom(r -> r.into(RunRecord.class)),
        RUN_PROGRESS
        .fieldsRow()
        .as("run_progress")
        .convertFrom(r -> r.into(RunProgressRecord.class)),
        multiset(
            select(REASON.fieldsRow()) <----- 
            .from(REASON)
            .where(REASON.RUN_ID.eq(RUN.ID)))
        .as("reasons")
        .convertFrom(r -> r.into(Reason.class)))   <---- 
    .from(RUN)
    .leftOuterJoin(RUN_PROGRESS)
    .on(RUN_PROGRESS.RUN_ID.eq(RUN.ID))
    .where(RUN.ID.eq(runId.getValue()))
    .fetch()

fieldsRow() and fetching into a generated Record class, all fields of the retrieved ReasonRecord objects are null!?!

conf ->
    conf.dsl()
    .select(
        RUN.fieldsRow().as("run").convertFrom(r -> r.into(RunRecord.class)),
        RUN_PROGRESS
        .fieldsRow()
        .as("run_progress")
        .convertFrom(r -> r.into(RunProgressRecord.class)),
        multiset(
            select(REASON.fieldsRow())
            .from(REASON)
            .where(REASON.RUN_ID.eq(RUN.ID)))
        .as("reasons")
        .convertFrom(r -> r.into(ReasonRecord.class)))   <---- 
    .from(RUN)
    .leftOuterJoin(RUN_PROGRESS)
    .on(RUN_PROGRESS.RUN_ID.eq(RUN.ID))
    .where(RUN.ID.eq(runId.getValue()))
    .fetch()

Selecting each Reason field individually, everything works as expected again:

conf ->
    conf.dsl()
    .select(
        RUN.fieldsRow().as("run").convertFrom(r -> r.into(RunRecord.class)),
        RUN_PROGRESS
        .fieldsRow()
        .as("run_progress")
        .convertFrom(r -> r.into(RunProgressRecord.class)),
        multiset(
            select(REASON.RUN_ID, REASON.REASON_)  <------
            .from(REASON)
            .where(REASON.RUN_ID.eq(RUN.ID)))
        .as("reasons")
        .convertFrom(r -> r.into(ReasonRecord.class)))
    .from(RUN)
    .leftOuterJoin(RUN_PROGRESS)
    .on(RUN_PROGRESS.RUN_ID.eq(RUN.ID))
    .where(RUN.ID.eq(runId.getValue()))
    .fetch()
jooq java
1 Answers

A simpler solution

Assuming that ReasonRecord is the generated TableRecord for the REASON table, then starting from jOOQ 3.17, you can simply project the table reference itself:

select(REASON)
.from(REASON)
.where(REASON.RUN_ID.eq(RUN.ID))

That way, you don't have to apply any ad-hoc conversion anymore to get a nested Result<ReasonRecord>. (You might still keep it in order to turn the Result<ReasonRecord> into a List<ReasonRecord> if your typing makes this necessary...).

Likewise, you don't need to use RUN.fieldsRow() anymore. Just project RUN:

conf.dsl()
    .select(
       RUN,
       RUN_PROGRESS,
       ...)
user_5217990
Related