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:
<code>data class Person(
val id: String,
val name: String,
val state: Boolean,
val roles: List<Role> = emptyList(),
val vehicle: Vehicle
)
</code>
<code>data class Person(
val id: String,
val name: String,
val state: Boolean,
val roles: List<Role> = emptyList(),
val vehicle: Vehicle
)
</code>
data class Person(
val id: String,
val name: String,
val state: Boolean,
val roles: List<Role> = emptyList(),
val vehicle: Vehicle
)
<code>data class Role(
val id: String,
val personId: String,
val name:String,
val description
)
</code>
<code>data class Role(
val id: String,
val personId: String,
val name:String,
val description
)
</code>
data class Role(
val id: String,
val personId: String,
val name:String,
val description
)
<code>data class Vehicle(val id: String, val personId: String,val name: String, val type: String)
</code>
<code>data class Vehicle(val id: String, val personId: String,val name: String, val type: String)
</code>
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:
<code> 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()
</code>
<code> 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()
</code>
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
5
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:
MULTISETto nest collectionsROWto 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-kotlinextension functions - You’re using the latest jOOQ versions (3.19+)
<code>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))
</code>
<code>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))
</code>
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))