Spring Boot下兼容Oracle与PostgreSQL的数据库无关UPSERT实现方案

兼容Oracle与PostgreSQL的实现方案 #

要实现双库兼容的upsert（更新不存在则插入）逻辑，推荐优先使用业务层拆分SQL的实现，完全规避不同数据库的存储过程、特有语法差异：

方案1：业务层拆分执行（最推荐，无数据库依赖） #

将原有PL/SQL块的逻辑拆分为两步标准SQL执行，Java代码逻辑如下：

// 第一步：执行更新逻辑
private static final String SQL_UPDATE_USER = "UPDATE users SET ACTIVE=1 WHERE ukey = ? and lower(name) = lower(?)";
// 第二步：更新行数为0时执行插入
private static final String SQL_INSERT_USER = "INSERT INTO users(user_id, ukey, name, ACTIVE) VALUES (?, ?, ?, 1)";

// 业务执行逻辑
public void upsertUser(String ukey, String name) {
    // 执行更新，获取影响行数
    int updateCount = jdbcTemplate.update(SQL_UPDATE_USER, ukey, name);
    if (updateCount == 0) {
        // 兼容双库的序列主键获取
        Long userId = getNextSequenceValue("hibernate_sequence");
        jdbcTemplate.update(SQL_INSERT_USER, userId, ukey, name);
    }
}

// 双库兼容的序列值获取方法
private Long getNextSequenceValue(String sequenceName) {
    if (isOracleEnv()) {
        return jdbcTemplate.queryForObject("SELECT " + sequenceName + ".nextval FROM DUAL", Long.class);
    } else if (isPostgreSQLEnv()) {
        return jdbcTemplate.queryForObject("SELECT nextval('" + sequenceName + "')", Long.class);
    }
    throw new RuntimeException("不支持的数据库类型");
}

该方案优势：

• 完全使用标准SQL语法，无数据库特有的存储过程、关键字依赖
• 逻辑清晰，问题排查成本低
• 事务一致性易保证，两步执行放在同一个事务内即可保证原子性

方案2：多数据源原生语法适配（仅当必须用单条SQL时使用） #

如果业务要求必须用单条SQL实现，可通过多数据源SQL配置的方式做适配，两种数据库的原生实现分别为：

Oracle 原生实现

MERGE INTO users t
USING dual ON (t.ukey = :ukey AND lower(t.name) = lower(:name))
WHEN MATCHED THEN UPDATE SET t.active = 1
WHEN NOT MATCHED THEN INSERT (user_id, ukey, name, active) 
VALUES (hibernate_sequence.nextval, :ukey, :name, 1)

PostgreSQL 原生实现

INSERT INTO users (user_id, ukey, name, active)
VALUES (nextval('hibernate_sequence'), :ukey, :name, 1)
ON CONFLICT (ukey, lower(name)) DO UPDATE SET active = 1

注意：PostgreSQL实现需要提前在ukey和lower(name)上建立唯一约束，冲突判断规则需和业务逻辑匹配。

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