如何在JOOQ中完成三级嵌套查询并正确映射对象

对象关系定义 #

MainObject(
    var id: Long? = null,
    var name: String? = null,
    var firstLvlNestedObject: FirstLvlNestedObject? = null,
)

FirstLvlNestedObject(
    var id: Long? = null,
    var name: String? = null,
    var secondLvlNestedObjectList: List<SecondLvlNestedObject> = listOf(),
)

SecondLvlNestedObject(
    var id: Long? = null,
    var name: String? = null,
    var thirdLvlNestedObject: ThirdLvlNestedObject? = null,
)

ThirdLvlNestedObject(
    var id: Long? = null,
    var name: String? = null, 
)

当前查询代码（注释三级对象可运行） #

fun getById(id: Long): MainObject =
    dslContext.select(
        MAIN.ID, MAIN.NAME,
        field(
            select(
                row(
                    FIRST.ID, FIRST.NAME,
                    array(
                        select(
                            row(
                                SECOND.ID, SECOND.NAME
                                // 注释了三级对象相关代码
                                // field(
                                //     select(row(THIRD.fields().toMutableList()))
                                // ).`as`("thirdLvlNestedObject")
                            )
                        )
                        .from(SECOND)
                        .leftJoin(THIRD).on(SECOND.THIRD_ID.eq(THIRD.ID))
                        .where(SECOND.SECOND_GROUP_ID.eq(MAIN.SECOND_GROUP_ID))
                    )
                        .convertFrom {
                            it.map { second ->
                                SecondLvlNestedObject(
                                    id = second.value1()!!,
                                    name = second.value2(),
                                    // thirdLvlNestedObject 待映射
                                )
                            }
                        }
                )
                    .mapping { firstId, firstName, secondLvlList ->
                        FirstLvlNestedObject(
                            firstId,
                            firstName,
                            secondLvlList
                        )
                    }
            )
                .from(FIRST)
                .where(FIRST.ID.eq(MAIN.FIRST_ID))
        ).`as`("firstLvlNestedObject")
    )
        .from(MAIN)
        .where(MAIN.ID.eq(id))
        .fetchOneInto(MainObject::class.java)

解决方案 #

要实现三级对象的映射，需要在查询构造和对象转换两个环节同步调整，同时修正原代码中的语法错误，完整代码如下：

fun getById(id: Long): MainObject =
    dslContext.select(
        MAIN.ID, MAIN.NAME,
        field(
            select(
                row(
                    FIRST.ID, FIRST.NAME,
                    array(
                        select(
                            row(
                                SECOND.ID, SECOND.NAME,
                                // 构造三级对象的查询字段，关联SECOND和THIRD
                                field(
                                    select(row(THIRD.ID, THIRD.NAME))
                                        .from(THIRD)
                                        .where(THIRD.ID.eq(SECOND.THIRD_ID))
                                ).`as`("thirdLvlNestedObject")
                            )
                        )
                        .from(SECOND)
                        .leftJoin(THIRD).on(SECOND.THIRD_ID.eq(THIRD.ID))
                        .where(SECOND.SECOND_GROUP_ID.eq(MAIN.SECOND_GROUP_ID))
                    )
                        .convertFrom { result ->
                            result.map { secondRow ->
                                // 从secondRow中取出三级对象的行数据，转换为ThirdLvlNestedObject
                                val thirdRow = secondRow.value3()
                                SecondLvlNestedObject(
                                    id = secondRow.value1()!!,
                                    name = secondRow.value2(),
                                    thirdLvlNestedObject = thirdRow?.let {
                                        ThirdLvlNestedObject(
                                            id = it.value1(),
                                            name = it.value2()
                                        )
                                    }
                                )
                            }
                        }
                )
                    .mapping { firstId, firstName, secondLvlList ->
                        FirstLvlNestedObject(
                            id = firstId,
                            name = firstName,
                            secondLvlNestedObjectList = secondLvlList
                        )
                    }
            )
                .from(FIRST)
                .where(FIRST.ID.eq(MAIN.FIRST_ID))
        ).`as`("firstLvlNestedObject")
    )
        .from(MAIN)
        .where(MAIN.ID.eq(id))
        .fetchOneInto(MainObject::class.java)

关键调整说明 #

1. 三级对象查询构造：在SECOND的row构造中，新增查询三级对象的字段，通过field(select(row(THIRD.ID, THIRD.NAME)))关联SECOND和THIRD的外键关系，确保能正确查询到三级对象的数据。
2. 对象映射转换：在convertFrom的map操作中，从secondRow的第三个值（value3()）取出三级对象的行数据，通过let方法安全转换为ThirdLvlNestedObject，赋值给secondLvlNestedObject的对应属性。
3. 语法错误修正：修复了原代码中leftJoin、where语句的括号缺失问题，以及FIRST.ID(eq(...))的错误调用（应为FIRST.ID.eq(...)）。

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