如何以编程方式添加可关联表的所有字段（jOOQ场景）

我有以下两张数据库表：

CREATE TABLE [instrument].[InstrumentType] (
    [InstrumentTypeId] INT           NOT NULL IDENTITY (1, 1),
    [Name]             VARCHAR (255) NOT NULL,
    CONSTRAINT [PK_InstrumentType]   PRIMARY KEY CLUSTERED ([InstrumentTypeId] ASC)
);

CREATE TABLE [instrument].[Instrument] (
    [InstrumentId]     INT            NOT NULL IDENTITY (1, 1),
    [InstrumentTypeId] INT            NOT NULL,
    [Name]             VARCHAR (255)  NOT NULL,
    CONSTRAINT [PK_Instrument]                PRIMARY KEY CLUSTERED ([InstrumentId] ASC),
    CONSTRAINT [FK_Instrument_InstrumentType] FOREIGN KEY ([InstrumentTypeId]) REFERENCES [instrument].[InstrumentType] ([InstrumentTypeId])
);

借助jOOQ的特性，我可以不用编写join语句，直接通过关联路径获取关联表的字段，示例代码如下：

create.select(INSTRUMENT.INSTRUMENTID, INSTRUMENT.NAME, INSTRUMENT.instrumenttype.NAME)
      .from(INSTRUMENT)
      .fetch()

我有大量类似结构的表，希望通过编程方式自动生成这类查询。尝试使用INSTRUMENT.references实现时，生成的SQL直接引用了InstrumentType表，而非使用INSTRUMENT.instrumenttype的路径，导致查询不符合预期。当前生成的SQL如下：

select
  [instrument].[Instrument].[InstrumentId],
  [instrument].[Instrument].[Name],
  [instrument].[InstrumentType].[Name]
from [instrument].[Instrument]

以下是我目前的实现代码，但尚未找到正确遍历外键路径的方式：

val T = INSTRUMENT

fun fetch(dsl: DSLContext): Any {
  return dsl
    .select(fieldList())
    .from(T)
    .fetch()
}

protected open fun fieldList(): Set<Field<*>> {
  val result = mutableSetOf<Field<*>>()
  val tables = fkTableList(T)
  for (t in tables) {
    val fk = t.references.flatMap { it.fields }.toSet()
    val include = t.fields().toSet()

    //移除INSTRUMENTTYPEID
    val selectedFields = include - fk
    result.addAll(selectedFields)
  }
  return result
}

private fun fkTableList(table: Table<*>): List<Table<*>> {
  val result = mutableListOf(table)
  val fkTables = table.references.map { it.key.table }
  result.addAll(fkTables.flatMap { fkTableList(it) })
  return result
}

请问如何修改代码，实现通过外键路径（如INSTRUMENT.instrumenttype）来添加关联表的所有字段？

问题核心在于你直接使用了关联表本身的字段，而非通过**主表的关联路径（jOOQ自动生成的嵌套表属性）**获取字段。jOOQ会为每个外键关联生成对应嵌套属性（比如INSTRUMENT.instrumenttype），通过该属性访问字段才会生成主表.关联路径.字段的SQL语法，而非直接引用关联表。

修改思路：

1. 遍历主表的外键关联，获取每个外键对应的嵌套关联表对象，而非直接获取关联表本身
2. 递归遍历这些嵌套关联表，收集所有非外键字段，同时保留路径关系
3. 避免直接使用table.references.map { it.key.table }，转而通过jOOQ生成的嵌套属性获取关联路径

以下是修改后的代码：

val T = INSTRUMENT

fun fetch(dsl: DSLContext): Any {
  return dsl
    .select(fieldList())
    .from(T)
    .fetch()
}

protected open fun fieldList(): MutableList<Field<*>> {
  val result = mutableListOf<Field<*>>()
  // 收集主表的非外键字段
  collectNonForeignKeyFields(T, result)
  // 递归收集所有关联表的字段（通过嵌套关联路径）
  collectAssociatedFields(T, result)
  return result
}

// 收集指定表的非外键字段
private fun collectNonForeignKeyFields(table: Table<*>, fields: MutableList<Field<*>>) {
  val foreignKeyFields = table.references
    .flatMap { it.fields }
    .toSet()
  fields.addAll(table.fields().filter { it !in foreignKeyFields })
}

// 递归收集嵌套关联表的字段
private fun collectAssociatedFields(table: Table<*>, fields: MutableList<Field<*>>) {
  // 通过反射获取jOOQ生成的所有嵌套关联表属性
  val associations = table.javaClass.declaredFields
    .filter { Table::class.java.isAssignableFrom(it.type) }
    .map {
      it.isAccessible = true
      it.get(table) as Table<*>
    }

  for (association in associations) {
    // 收集当前关联表的非外键字段
    collectNonForeignKeyFields(association, fields)
    // 递归处理关联表的下一级关联
    collectAssociatedFields(association, fields)
  }
}

代码说明 #

• 利用反射获取jOOQ为每个外键生成的嵌套关联表属性（如INSTRUMENT.instrumenttype），这些属性是Table类型的嵌套对象
• 通过嵌套Table对象访问字段，生成的SQL会自动使用主表.关联路径.字段的格式
• 递归处理所有层级的关联表，确保所有关联字段都被正确收集
• 过滤掉外键字段（如InstrumentTypeId），避免重复选择

修改后生成的SQL会与手动编写的示例一致，自动使用INSTRUMENT.instrumenttype.Name这类路径格式，无需手动编写join语句。

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