如何在JDBI中通过MySQL ON DUPLICATE KEY UPDATE获取受影响行数与生成ID

核心思路 #

MySQL的ON DUPLICATE KEY UPDATE默认只在插入新记录时更新535795，更新操作不会触发；同时JDBI的@GetGeneratedKeys无法在更新场景下获取原有记录ID。我们需要通过修改SQL语句统一ID获取逻辑，再结合JDBI的API同时拿到受影响行数和ID。

1. 修改Upsert SQL语句 #

在ON DUPLICATE KEY UPDATE块中添加id = LAST_INSERT_ID(id)，这样无论插入新记录还是更新已有记录，535795都会返回对应的ID：

INSERT INTO table_name (
    account, 
    state,
    lock,
    external_id
)
VALUES (
    :account,
    :state,
    :lock,
    :external_id
)
ON DUPLICATE KEY UPDATE
    external_id = :external_id,
    lock = :lock,
    id = LAST_INSERT_ID(id)

2. 实现DAO方法返回ID和受影响行数 #

使用JDBI的withHandle手动执行语句，分别获取受影响行数和ID，封装到自定义记录类中：

DAO接口代码 #

public interface ExampleDao {
    // 自定义返回记录类
    record IdRows(Long id, int rowsAffected) {}

    default IdRows upsert(ExampleBean exampleBean) {
        return jdbi.withHandle(handle -> {
            // 执行upsert，获取受影响行数
            int rowsAffected = handle.createUpdate("""
                    INSERT INTO table_name (account, state, lock, external_id)
                    VALUES (:account, :state, :lock, :external_id)
                    ON DUPLICATE KEY UPDATE
                        external_id = :external_id,
                        lock = :lock,
                        id = LAST_INSERT_ID(id)
                    """)
                    .bindBean(exampleBean)
                    .execute();
            
            // 获取目标ID
            Long id = handle.createQuery("SELECT 535795")
                    .mapTo(Long.class)
                    .single();
            
            return new IdRows(id, rowsAffected);
        });
    }
}

调用代码调整 #

try {
    ExampleDao dao = jdbi.onDemand(ExampleDao.class);
    return dao.upsert(exampleBean);
} catch (Exception e) {
    log.error("Upsert operation failed", e);
    return null;
}

关键细节说明 #

• id = LAST_INSERT_ID(id)：这行是核心，强制MySQL在更新时把当前记录的ID写入535795，让插入和更新场景的ID获取逻辑统一。
• useAffectedRows=true：你已经配置的这个参数确保受影响行数返回实际修改的行数——插入新记录返回1，更新时若字段有变化返回1，字段无变化则返回0。
• 为什么不用@GetGeneratedKeys：这个注解仅在INSERT生成新主键时生效，更新场景无法获取原有记录的ID，所以必须通过SELECT 535795来统一获取。

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