A nice way to pass complex result types in JOOQ

user_97901 0
Viewed 49

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?

jooq sql java
2 Answers

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));
user_10451420

It looks like I can call fetchOneInto(SomeRecordClass.class) but this has no compile time checking that the result from JOOQ matches with the constructor of the Record Class. By record I mean JDK16 records.

public record CompanyAndPreferencesNice(CompanyRecord company,
                                        CompanyCo2ParameterRecord companyCo2Parameter,
                                        CompanyAccessCodeRecord companyAccessCode,
                                        Result<SubsidiaryRecord> subsidiaries,
                                        Result<CompanyAreaPreferenceRecord> companyAreaPreferences,
                                        Result<CompanySubareaPreferenceRecord> companySubareaPreferences,
                                        Result<ManifestDocumentPreferenceRecord> documentPreferences,
                                        Result<ServiceCompanyPreferenceRecord> serviceCompanyPreferences) {}


    return dslContext.get()
            .select(
                    COMPANY_CO2_PARAMETER.company(),
                    COMPANY_CO2_PARAMETER,
                    COMPANY_ACCESS_CODE,
                    multiset(
                            selectFrom(SUBSIDIARY)
                                    .where(SUBSIDIARY.COMPANY_ID.eq(companyId).and(SUBSIDIARY.ACTIVE.isTrue()))),
                    multiset(
                            selectFrom(COMPANY_AREA_PREFERENCE)
                                    .where(COMPANY_AREA_PREFERENCE.COMPANY_ID.eq(companyId).and(COMPANY_AREA_PREFERENCE.DELETED.isFalse()))),
                    multiset(
                            selectFrom(COMPANY_SUBAREA_PREFERENCE)
                                    .where(COMPANY_SUBAREA_PREFERENCE.COMPANY_ID.eq(companyId).and(COMPANY_SUBAREA_PREFERENCE.DELETED.isFalse()))),
                    multiset(
                            selectFrom(MANIFEST_DOCUMENT_PREFERENCE)
                                    .where(MANIFEST_DOCUMENT_PREFERENCE.COMPANY_ID.eq(companyId).and(MANIFEST_DOCUMENT_PREFERENCE.DELETED.isFalse()))),
                    multiset(
                            selectFrom(SERVICE_COMPANY_PREFERENCE)
                                    .where(SERVICE_COMPANY_PREFERENCE.COMPANY_ID.eq(companyId).and(SERVICE_COMPANY_PREFERENCE.DELETED.isFalse()))
                    ))
            .from(COMPANY_CO2_PARAMETER, COMPANY)
            .leftJoin(COMPANY_ACCESS_CODE).on(COMPANY_ACCESS_CODE.COMPANY_ID.eq(companyId))
            .where(COMPANY.ID.eq(companyId))
            .and(COMPANY_CO2_PARAMETER.COMPANY_ID.eq(companyId))
            .fetchOneInto(CompanyAndPreferencesNice.class);

I could also call fetchOne to return a typesafe Tuple then pass that as the sole value into a JDK 16 record like this...

public record CompanyAndPreferences(
        org.jooq.Record8<CompanyRecord, CompanyCo2ParameterRecord, CompanyAccessCodeRecord, Result<SubsidiaryRecord>, Result<CompanyAreaPreferenceRecord>, Result<CompanySubareaPreferenceRecord>, Result<ManifestDocumentPreferenceRecord>, Result<ServiceCompanyPreferenceRecord>> jooqRecord) {
}
    final var record = dslContext.get()
            .select(
                    COMPANY_CO2_PARAMETER.company(),
                    COMPANY_CO2_PARAMETER,
                    COMPANY_ACCESS_CODE,
                    multiset(
                            selectFrom(SUBSIDIARY)
                                    .where(SUBSIDIARY.COMPANY_ID.eq(companyId).and(SUBSIDIARY.ACTIVE.isTrue()))),
                    multiset(
                            selectFrom(COMPANY_AREA_PREFERENCE)
                                    .where(COMPANY_AREA_PREFERENCE.COMPANY_ID.eq(companyId).and(COMPANY_AREA_PREFERENCE.DELETED.isFalse()))),
                    multiset(
                            selectFrom(COMPANY_SUBAREA_PREFERENCE)
                                    .where(COMPANY_SUBAREA_PREFERENCE.COMPANY_ID.eq(companyId).and(COMPANY_SUBAREA_PREFERENCE.DELETED.isFalse()))),
                    multiset(
                            selectFrom(MANIFEST_DOCUMENT_PREFERENCE)
                                    .where(MANIFEST_DOCUMENT_PREFERENCE.COMPANY_ID.eq(companyId).and(MANIFEST_DOCUMENT_PREFERENCE.DELETED.isFalse()))),
                    multiset(
                            selectFrom(SERVICE_COMPANY_PREFERENCE)
                                    .where(SERVICE_COMPANY_PREFERENCE.COMPANY_ID.eq(companyId).and(SERVICE_COMPANY_PREFERENCE.DELETED.isFalse()))
                    ))
            .from(COMPANY_CO2_PARAMETER, COMPANY)
            .leftJoin(COMPANY_ACCESS_CODE).on(COMPANY_ACCESS_CODE.COMPANY_ID.eq(companyId))
            .where(COMPANY.ID.eq(companyId))
            .and(COMPANY_CO2_PARAMETER.COMPANY_ID.eq(companyId))
            .fetchOne();
    var companyAndPreferences = new CompanyAndPreferences(record);

This is more verbose but gives compile time checking. If I ever change the query result type it will give a compile error.

user_979010
Related