A nice way to pass complex result types in JOOQ

Question

I have been playing with some of the new features of JOOQ 3.17 such as type safe nested table records mixed with implicit joins as is described here:

https://blog.jooq.org/projecting-type-safe-nested-tablerecords-with-jooq-3-17/

We have a complex view where you can modify many properties of a "company" object. Our old code had a zillion hibernate methods to CRUD related records for the company object on one rather large UI. Now I want to rewrite this in JOOQ. I came up with a single query that pulls a CompanyRecord and related records like this:

Record4<CompanyRecord, Result<ServiceCompanyPreferenceRecord>, Result<SubsidiaryRecord>, Result<CompanyCo2ParameterRecord>> fancyTypeResult =
        dslContext.get().select(
                        Tables.COMPANY,
                        multiset(
                                selectFrom(Tables.SERVICE_COMPANY_PREFERENCE)
                                        .where(Tables.SERVICE_COMPANY_PREFERENCE.COMPANY_ID.eq(Tables.COMPANY.ID))
                        ),
                        multiset(
                                selectFrom(Tables.SUBSIDIARY)
                                        .where(Tables.SUBSIDIARY.COMPANY_ID.eq(Tables.COMPANY.ID))
                        ),
                        multiset(
                                selectFrom(Tables.COMPANY_CO2_PARAMETER)
                                        .where(Tables.COMPANY_CO2_PARAMETER.COMPANY_ID.eq(Tables.COMPANY.ID))
                        )
                )
                .from(Tables.COMPANY)
                .where(Tables.COMPANY.ID.eq(companyId))
                .fetchOne();

This is fantastic because I can modify and save the CompanyRecord or and related ServiceCompanyPreferenceRecord, SubsidiaryRecord,or CompanyCo2ParameterRecord and those rows in the db are updated.

One problem I am having is passing type "Record4<CompanyRecord, Result<ServiceCompanyPreferenceRecord>, Result<SubsidiaryRecord>, Result<CompanyCo2ParameterRecord>>" is rather verbose. So having a function that finds a company all all records like this

public Record4<CompanyRecord, Result<ServiceCompanyPreferenceRecord>, Result<SubsidiaryRecord>, Result<CompanyCo2ParameterRecord>> loadCompanyAndRelatedPreferences(Long companyId){ ....

Could be a bit akward. I am wondering if simply making a POJO that holds a field of type "Record4<CompanyRecord, Result<ServiceCompanyPreferenceRecord>, Result<SubsidiaryRecord>, Result<CompanyCo2ParameterRecord>>" would make it easier as I can use that POJO without re-writing that type over and over. Another thought would be is this a good use case for Java records?

We have a number of screens that follow this pattern. Pull a record and related records, CRUD them. Any ideas on a nice way to handle this?

Answer 1

You can use convertFrom and mapping. Here's an example from https://github.com/72services/jtaf4

return dsl
        .select(
            COMPETITION.NAME,
            COMPETITION.COMPETITION_DATE,
            COMPETITION.ALWAYS_FIRST_THREE_MEDALS,
            COMPETITION.MEDAL_PERCENTAGE,
            multiset(
                select(
                    CATEGORY.ABBREVIATION,
                    CATEGORY.NAME,
                    CATEGORY.YEAR_FROM,
                    CATEGORY.YEAR_TO,
                    multiset(
                        select(
                            CATEGORY_ATHLETE.athlete().FIRST_NAME,
                            CATEGORY_ATHLETE.athlete().LAST_NAME,
                            CATEGORY_ATHLETE.athlete().YEAR_OF_BIRTH,
                            CATEGORY_ATHLETE.athlete().club().NAME,
                            multiset(
                                select(
                                    RESULT.event().ABBREVIATION,
                                    RESULT.RESULT_,
                                    RESULT.POINTS
                                )
                                    .from(RESULT)
                                    .where(RESULT.ATHLETE_ID.eq(CATEGORY_ATHLETE.athlete().ID))
                                    .and(RESULT.COMPETITION_ID.eq(COMPETITION.ID))
                                    .and(RESULT.CATEGORY_ID.eq(CATEGORY.ID))
                                    .orderBy(RESULT.POSITION)
                            ).convertFrom(r -> r.map(mapping(CompetitionRankingData.Category.Athlete.Result::new)))
                        )
                            .from(CATEGORY_ATHLETE)
                            .where(CATEGORY_ATHLETE.CATEGORY_ID.eq(CATEGORY.ID))
                    ).convertFrom(r -> r.map(mapping(CompetitionRankingData.Category.Athlete::new)))
                )
                    .from(CATEGORY)
                    .where(CATEGORY.SERIES_ID.eq(COMPETITION.SERIES_ID))
                    .orderBy(CATEGORY.ABBREVIATION)
            ).convertFrom(r -> r.map(mapping(CompetitionRankingData.Category::new)))
        )
        .from(COMPETITION)
        .where(COMPETITION.ID.eq(competitionId))
        .fetchOne(mapping(CompetitionRankingData::new));