Oracle 适配 OpenGauss 数据库差异语法汇总

背景

国产化进程中，需要将某项目的数据库从 Oracle 转为 OpenGauss ，项目初期也是规划了适配不同数据库的，MyBatis 配置加载路径设计的是根据数据库类型加载指定文件夹的 xml 文件。

后面由于固定了数据库类型为 Oracle 后，只写了 Oracle 的没有其他类型。从 Oracle 适配 OpenGauss 多少还是有些差异 SQL 语法的，本文记录一下。

之前不太了解 OpenGauss，但是接触过盘维数据库，这俩都可以用 postgre 的 Java 数据库驱动，把它们都归于 postgre 来看就可以了，目前发现了一些适配问题，都一一解决了，汇总如下。

varchar2 类型

Oracle 的数据库建表语句中的 varchar2 类型，直接在 OpenGauss 数据库中执行，也能兼容，但是实际类型为 varchar 。

NCLOB 类型

Oracle 的 NCLOB 类型到了 OpenGauss 应该配置为 bytea 。

LISTAGG 函数

Oracle 有 LISTAGG 函数生成动态拼接的 SQL ，MySQL 对应的函数是 GROUP_CONCAT ，到了以 postgre 为内核的 OpenGauss 应该用 string_agg 。

主键索引名称

Oracle 创建表设置主键索引的时候，索引名称可以跟表名称相同，例如这个建表语句：

CREATE TABLE MY_TABLE_1(
    field_a VARCHAR2(32),
    field_b VARCHAR2(50),
    field_c VARCHAR2(255),
    field_d VARCHAR2(32),
    field_e VARCHAR2(2),
    constraint MY_TABLE_1 primary key(field_a)
) ;

建表语句在后面设置表的主键，主键索引名称配置的与表名称一样，这个对 Oracle 没问题。

但是到了 OpenGauss 的时候会报 relation “xxx” already exists ，但是实际上这个名称的表并没有创建：

解决办法：设置索引名称与表名不一样，比如加个前缀。

distinct 与 order by 语法

对于 Oracle 数据库而已，使用 distinct 后 order by 的字段可以不包含在查询字段列表中，例如这个 SQL 语句是正确的在 Oracle 中：

SELECT DISTINCT field1,field2
FROM MY_TABLE
ORDER BY field3 DESC

但是在 OpenGauss 中报异常，SELECT DISTINCT ORDER BY 字段必须出现在查询字段列表中：

解决办法：统一 SQL 把排序字段加在查询字段列表中。

批量插入语法

Oracle 的批量插入 SQL 语句有两种方式，一种是用 begin end; 包裹的存储过程，另一种是使用 dual 中建表。

方法一：

<insert id="insertBatchSomeColumn" parameterType="java.util.List">
     begin
    <foreach collection="list" item="tempData" index="index" separator =";">
        INSERT INTO my_table(a,b,c,d)
        VALUES (
        #{tempData.a,jdbcType=VARCHAR},
        #{tempData.b,jdbcType=VARCHAR},
        #{tempData.c,jdbcType=VARCHAR},
        #{tempData.d,jdbcType=VARCHAR}
        )
    </foreach>
    ;end;
</insert>

方法二：

<insert id="insertBatchSomeColumn">
    INSERT INTO my_table(a, b, c, d)
    <foreach collection="list" item="item" index="index" separator="union all" open="("  close=")">
        select #{item.a,jdbcType=VARCHAR},
        #{item.b,jdbcType=VARCHAR},
        #{item.c,jdbcType=VARCHAR},
        #{item.d,jdbcType=VARCHAR} from dual
    </foreach>
</insert>

但是对于 OpenGauss 数据库的批量插入SQL 语法应该调整为：

<insert id="insertBatchSomeColumn" parameterType="java.util.List">
   INSERT INTO my_table(a,b,c,d) VALUES
   <foreach collection="list" item="tempData" index="index" separator =",">
       (
       #{tempData.a,jdbcType=VARCHAR},
       #{tempData.b,jdbcType=VARCHAR},
       #{tempData.c,jdbcType=VARCHAR},
       #{tempData.d,jdbcType=VARCHAR}
       )
   </foreach>
</insert>

Quartz 兼容配置

使用了 Quartz 定时调度框架，当数据库换成 postgre 驱动的时候，需要调整 Quartz 的配置，主要有三点：

1. 修改spring.quartz.properties.org.quartz.jobStore.driverDelegateClass 这个属性为org.quartz.impl.jdbcjobstore.PostgreSQLDelegate。
2. Quartz 的初始化 SQL 语句导入需要修改，NCLOB 类型需要改为 bytea，例如：JOB_DATA bytea。
3. 调度任务的布尔字段类型，例如 QZ_CLS_JOB_DETAILS 表的 IS_DURABLE、IS_NONCONCURRENT 和 IS_UPDATE_DATA，需要从 varchar2(1) 改为 varchar(5)。因为 Oracle 存储布尔字段时用的字符串 0 和 1，但是 postgre 驱动用的是 true 和 false ，导致任务调度时出现字段超长异常。

字段大小写问题

Oracle 字段默认都是转化为大写的，MySQL 大小写不区分，但是 postgre 内核默认字段都是小写的。

这是比较麻烦的，如果查询语句中使用 Map 接收查询结果时，查询结果字段名称都转化为小写了。而从 Map 中 get 数据时的 key 都是大写的话，就会出现值为空的问题。

解决办法：自定义 MyBatis 的 Map 封装工厂，步骤如下：

第一步，定义 MapWrapper 实现子类定制查询结果的 Key 转为大写字母：

public class MyBatisCustomWrapper extends MapWrapper {

    public MyBatisCustomWrapper(MetaObject metaObject, Map<String, Object> map) {
        super(metaObject, map);
    }

    @Override
    public String findProperty(String name, boolean useCamelCaseMapping) {
        // 转小写为toUpperCase()
        return name == null ? "" : name.toUpperCase();
    }
}

第二步，定义工厂类：

public class MyBatisMapWrapperFactory implements ObjectWrapperFactory {

    @Override
    public boolean hasWrapperFor(Object object) {
        return object != null && object instanceof Map;
    }

    @Override
    public ObjectWrapper getWrapperFor(MetaObject metaObject, Object object) {
        return new MyBatisCustomWrapper(metaObject,(Map)object);
    }
}

第三步，注入定制工厂：

@Bean
public ConfigurationCustomizer mapUpgrade() {
    return configuration -> configuration.setObjectWrapperFactory(new MyBatisMapWrapperFactory());
}

启示录

目前发现的就是这些问题，解决的还是比较顺利的。还是需要对整个系统的功能逐个进行测试，直接用 MyBatis 的框架封装的方法没有问题，麻烦的是各种通过 @Select 注解嵌入在代码中的SQL语句，需要逐个排查。

一开始约定好SQL语句都在 resource 中定义的话，相对会比较好一点，如果有不同的话，就可以放在不同目录里面通过 mybatis-plus.mapper-locations 配置来指定。但是在 DAO 里面定义的 SQL 就必须通过定义多个方法来区分了。