如何使用jOOQ在PostgreSQL间执行记录Upsert操作？

问题原因 #

你遇到的错误根源在于：

• 用DSL.table(DSL.name(...))创建的Table<Record>是无字段元数据的动态表，jOOQ无法识别表的字段结构和主键信息。
• 通过字符串拼接的SELECT *查询得到的Record没有绑定任何表元数据，导致set(record)无法映射字段到目标表，最终生成空的values ()和无效的冲突更新语句。

解决方案 #

方案1：使用jOOQ代码生成器（推荐） #

jOOQ代码生成器会根据数据库结构生成带完整元数据的表类、记录类，这是最可靠的实现方式：

1. 配置jOOQ代码生成器，生成目标表的实体类（如Mytable和MytableRecord）。
2. 修改代码如下：

try {
    // 源库连接配置
    String sourceDbUrl = "jdbc:postgresql://localhost:5432/sourcedb";
    String sourceDbUsername = "postgres";
    String sourceDbPassword = "admin";
    Connection sourceConnection = DriverManager.getConnection(sourceDbUrl, sourceDbUsername, sourceDbPassword);
    Configuration sourceConfig = new DefaultConfiguration().set(sourceConnection).set(SQLDialect.POSTGRES);
    DSLContext sourceDslContext = DSL.using(sourceConfig);

    // 目标库连接配置
    String targetDbUrl = "jdbc:postgresql://localhost:5432/targetdb";
    String targetDbUsername = "postgres";
    String targetDbPassword = "admin";
    Connection targetConnection = DriverManager.getConnection(targetDbUrl, targetDbUsername, targetDbPassword);
    Configuration targetConfig = new DefaultConfiguration().set(targetConnection).set(SQLDialect.POSTGRES);
    DSLContext targetDslContext = DSL.using(targetConfig);

    // 导入生成的表类（需替换为实际生成的包路径）
    import static generated.tables.Mytable.MYTABLE;

    // 查询源库数据
    Result<MytableRecord> records = sourceDslContext.selectFrom(MYTABLE).fetch();
    
    // 执行Upsert
    for (MytableRecord record : records) {
        targetDslContext.insertInto(MYTABLE)
                .set(record)
                .onDuplicateKeyUpdate()
                .set(record)
                .execute();
    }
} catch (Exception e) {
    e.printStackTrace();
}

生成的类包含表的所有字段、主键信息，jOOQ能自动生成合法的Upsert语句。

方案2：手动加载表元数据（无代码生成器时使用） #

如果无法使用代码生成器，可以从数据库连接中加载表的元数据，让jOOQ识别表结构：

try {
    // 源库连接配置（略）
    Connection sourceConnection = DriverManager.getConnection(sourceDbUrl, sourceDbUsername, sourceDbPassword);
    Configuration sourceConfig = new DefaultConfiguration().set(sourceConnection).set(SQLDialect.POSTGRES);
    DSLContext sourceDslContext = DSL.using(sourceConfig);

    // 目标库连接配置（略）
    Connection targetConnection = DriverManager.getConnection(targetDbUrl, targetDbUsername, targetDbPassword);
    Configuration targetConfig = new DefaultConfiguration().set(targetConnection).set(SQLDialect.POSTGRES);
    DSLContext targetDslContext = DSL.using(targetConfig);

    String sourceSchema = "public";
    String targetSchema = "public";
    String tableName = "mytable";

    // 从目标库加载带元数据的表对象（源库结构相同，可复用）
    Table<Record> tableWithMeta = targetDslContext.meta().getTable(DSL.name(targetSchema, tableName));

    // 查询源库数据（使用带元数据的表，确保Record绑定字段）
    Result<Record> records = sourceDslContext.selectFrom(tableWithMeta).fetch();
    
    // 执行Upsert
    for (Record record : records) {
        targetDslContext.insertInto(tableWithMeta)
                .set(record)
                .onDuplicateKeyUpdate()
                .set(record)
                .execute();
    }
} catch (Exception e) {
    e.printStackTrace();
}

通过meta().getTable()获取的表对象包含完整的字段和主键元数据，jOOQ能正确解析Record中的字段值并生成合法SQL。

方案3：批量优化（可选） #

如果数据量较大，建议使用批量操作减少数据库交互：

// 批量Upsert
targetDslContext.batchInsert(records)
        .onDuplicateKeyUpdate()
        .set(records)
        .execute();

或者直接通过跨库查询插入，避免内存加载所有数据：

targetDslContext.insertInto(tableWithMeta)
        .select(sourceDslContext.selectFrom(tableWithMeta))
        .onDuplicateKeyUpdate()
        .set(tableWithMeta.fields(), sourceDslContext.selectFrom(tableWithMeta).fields())
        .execute();

这种方式无需将所有数据加载到内存，性能更优。

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