How to efficiently map jooq records to kotlin domains

I have been searching for a solution for a few days now but I am struggling to find an approach that is efficient and generic. The Jooq documentation as well as solutions online have not really given me the opportunity to grasp how to map records to objects

Jooq Records mapping seems a very good solution but I don't know how to implement it when it comes to complex object.

Consider the following:

data class Person(
    val id: String,
    val name: String,
    val state: Boolean,
    val roles: List<Role> = emptyList(),
    val vehicle: Vehicle
)

data class Role(
    val id: String,
    val personId: String,
    val name:String,
    val description,
    val List<Authorization>
)

data class Authorization(val id:String, val roleId: String, val type:String)

data class Vehicle(val id: String, val personId: String,val name: String, val type: String)

This is my fun to read from the db using Jooq:

        val record = mysqlDSLContext.select()
            .from(PERSON)
            .leftJoin(ROLES).on(PERSON.ID.eq(ROLE.PERSON_ID))
            .leftJoin(VEHICLE).on(PERSON.ID.eq(VEHICLE.PERSON_ID))
            .fetchOne()

I want to map this into the Person data structure

With an object is easier but I am not sure what's the best way to approach this when I am retrieving data from multiple tables and want to put them in an object that has list and other objects.

Any help is appreciated. Thanks

Solution

You can map things in arbitrary ways from flat result sets using jOOQ's Collector API support if you wish to go down this route of using LEFT JOIN to denormalise your data, but quite likely these features are more suitable:

MULTISET to nest collections
ROW to nest records
ad-hoc converters to map between jOOQ types and your data classes

The example below will assume you're either:

Using the JavaGenerator, to profit from platform types
Using nullable values in your data classes

See issue #13999 regarding nullability in jOOQ/Kotlin

More assumptions:

You're using a more recent version of MySQL (8+)
You're using jooq-kotlin extension functions
You're using the latest jOOQ versions (3.19+)

val result: List<Person> =
ctx.select(
       PERSON.ID,
       PERSON.NAME,
       PERSON.STATE,
       multiset(
           select(
               PERSON.role.ID,
               PERSON.role.PERSON_ID,
               PERSON.role.NAME,
               PERSON.role.DESCRIPTION,
           )
           .from(PERSON.role)
       ).mapping(::Role),
       row(
           PERSON.vehicle.ID,
           PERSON.vehicle.PERSON_ID,
           PERSON.vehicle.NAME,
           PERSON.vehicle.TYPE
       ).mapping(::Vehicle)
   )
   .from(PERSON)
   .fetch(Records.mapping(::Person))