使用Kotlin JOOQ与MySQL实现两层嵌套集合查询映射报错求助

我在构建两层嵌套集合的JOOQ查询时遇到类型转换错误，无法将查询结果映射到Kotlin数据类。期望得到的数据结构如下：

[
    {
        "name": "My Super Region",
        "regions": [
            {
                "name": "My Region",
                "locations": [
                    {
                        "name": "My Location"
                    }
                ]
            }
        ]
    }
]

查看JOOQ查询的中间Result数据结构符合预期，但映射时抛出类型转换异常，报错信息如下：

java.lang.ClassCastException: class org.jooq.impl.RecordImpl3 cannot be cast to class com.abcxyz.repository.LocationsRepository$RegionRecord (org.jooq.impl.RecordImpl3 is in unnamed module of loader 'app'; com.abcxyz.LocationsRepository$RegionRecord is in unnamed module of loader io.ktor.server.engine.OverridingClassLoader$ChildURLClassLoader @f68f0dc)

我的实现代码如下：

fun getRegions(): List<SuperRegionRecord> {
    return DSL.using(dataSource, SQLDialect.MYSQL)
        .select(
            SUPER_REGIONS.ID,
            SUPER_REGIONS.NAME,
            multiset(
                select(
                    REGIONS.ID,
                    REGIONS.NAME,
                    multiset(
                        select(
                            LOCATIONS.ID,
                            LOCATIONS.NAME,
                        )
                        .from(LOCATIONS)
                        .where(LOCATIONS.REGION_ID.eq(REGIONS.ID))
                    ).`as`("locations")
                )
                .from(REGIONS)
                .where(REGIONS.SUPER_REGION_ID.eq(SUPER_REGIONS.ID))
            ).`as`("regions"),
        )
        .from(SUPER_REGIONS)
        .fetchInto(SuperRegionRecord::class.java)
}

data class SuperRegionRecord(
    val id: Int?,
    val name: String?,
    val regions: List<RegionRecord>?
)

data class RegionRecord(
    val id: Int?,
    val name: String?,
    val locations: List<LocationRecord>?
)

data class LocationRecord(
    val id: Int?,
    val name: String?
)

问题原因 #

JOOQ的fetchInto方法无法自动将嵌套的multiset返回的Record对象递归映射到自定义Kotlin数据类，它仅能处理顶层字段的映射，嵌套集合内的Record不会自动转换为RegionRecord或LocationRecord，因此触发类型转换异常。

解决方法 #

需要手动处理嵌套集合的映射，或借助JOOQ的RecordMapper实现递归转换，以下是两种可行方案：

方案1：使用map方法手动映射

在fetch之后手动将顶层Record转换为目标数据类，同时递归处理嵌套集合：

fun getRegions(): List<SuperRegionRecord> {
    return DSL.using(dataSource, SQLDialect.MYSQL)
        .select(
            SUPER_REGIONS.ID,
            SUPER_REGIONS.NAME,
            multiset(
                select(
                    REGIONS.ID,
                    REGIONS.NAME,
                    multiset(
                        select(LOCATIONS.ID, LOCATIONS.NAME)
                            .from(LOCATIONS)
                            .where(LOCATIONS.REGION_ID.eq(REGIONS.ID))
                    ).`as`("locations")
                )
                .from(REGIONS)
                .where(REGIONS.SUPER_REGION_ID.eq(SUPER_REGIONS.ID))
            ).`as`("regions"),
        )
        .from(SUPER_REGIONS)
        .fetch()
        .map { record ->
            SuperRegionRecord(
                id = record[SUPER_REGIONS.ID],
                name = record[SUPER_REGIONS.NAME],
                regions = record["regions", Result::class.java]?.map { regionRecord ->
                    RegionRecord(
                        id = regionRecord[REGIONS.ID],
                        name = regionRecord[REGIONS.NAME],
                        locations = regionRecord["locations", Result::class.java]?.map { locationRecord ->
                            LocationRecord(
                                id = locationRecord[LOCATIONS.ID],
                                name = locationRecord[LOCATIONS.NAME]
                            )
                        }
                    )
                }
            )
        }
}

方案2：配置全局RecordMapperProvider（适合项目全局复用）

若希望项目中所有嵌套映射自动处理，可配置JOOQ的RecordMapperProvider，结合Kotlin反射或Jackson实现递归映射，这种方式适合较大规模的项目，需要额外的全局配置。

另外需确保数据类字段名与查询选中的字段名完全匹配（包括大小写，MySQL默认不区分大小写，但代码中需保持一致），避免字段匹配失败导致的映射问题。

内容的提问来源于stack exchange，提问作者Offbeat Upbeat