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 创建表设置主键索引的时候，索引名称可以跟表名称类似，例如这个建表语句：

1. CREATE TABLE MY_TABLE_1(
2.     field_a VARCHAR2(32),
3.     field_b VARCHAR2(50),
4.     field_c VARCHAR2(255),
5.     field_d VARCHAR2(32),
6.     field_e VARCHAR2(2),
7.     constraint MY_TABLE_1 primary key(field_a)
8. ) ;

复制代码

建表语句在反面设置表的主键，主键索引名称设置的与表名称一样，这个对 Oracle 没问题。
但是到了 OpenGauss 的时候会报 relation “xxx” already exists ，但是实际上这个名称的表并没有创建：

解决办法：设置索引名称与表名不一样，比如加个前缀。
此外，Oracle 的插入 SQL 中使用双引号转义的语句，到了 OpenGauss 也会报名称不存在异常，需要注意。
distinct 与 order by

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

1. SELECT DISTINCT field1,field2
2. FROM MY_TABLE
3. ORDER BY field3 DESC

复制代码

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

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

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

1. <insert id="insertBatchSomeColumn" parameterType="java.util.List">
2.      begin
3.     <foreach collection="list" item="tempData" index="index" separator =";">
4.         INSERT INTO my_table(a,b,c,d)
5.         VALUES (
6.         #{tempData.a,jdbcType=VARCHAR},
7.         #{tempData.b,jdbcType=VARCHAR},
8.         #{tempData.c,jdbcType=VARCHAR},
9.         #{tempData.d,jdbcType=VARCHAR}
10.         )
11.     </foreach>
12.     ;end;
13. </insert>

复制代码

方法二：

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

复制代码

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

1. <insert id="insertBatchSomeColumn" parameterType="java.util.List">
2.    INSERT INTO my_table(a,b,c,d) VALUES
3.    <foreach collection="list" item="tempData" index="index" separator =",">
4.        (
5.        #{tempData.a,jdbcType=VARCHAR},
6.        #{tempData.b,jdbcType=VARCHAR},
7.        #{tempData.c,jdbcType=VARCHAR},
8.        #{tempData.d,jdbcType=VARCHAR}
9.        )
10.    </foreach>
11. </insert>

复制代码

Quartz 兼容设置

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

• 修改spring.quartz.properties.org.quartz.jobStore.driverDelegateClass 这个属性为org.quartz.impl.jdbcjobstore.PostgreSQLDelegate。
  
• Quartz 的初始化 SQL 语句导入需要修改，NCLOB 类型需要改为 bytea，例如：JOB_DATA bytea。
  
• 调度任务的布尔字段类型，例如 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 转为大写字母：

1. public class MyBatisCustomWrapper extends MapWrapper {
3.     public MyBatisCustomWrapper(MetaObject metaObject, Map<String, Object> map) {
4.         super(metaObject, map);
5.     }
7.     @Override
8.     public String findProperty(String name, boolean useCamelCaseMapping) {
9.         // 转小写为toUpperCase()
10.         return name == null ? "" : name.toUpperCase();
11.     }
12. }

复制代码

第二步，界说工厂类：

1. public class MyBatisMapWrapperFactory implements ObjectWrapperFactory {
3.     @Override
4.     public boolean hasWrapperFor(Object object) {
5.         return object != null && object instanceof Map;
6.     }
8.     @Override
9.     public ObjectWrapper getWrapperFor(MetaObject metaObject, Object object) {
10.         return new MyBatisCustomWrapper(metaObject,(Map)object);
11.     }
12. }

复制代码

第三步，注入定制工厂：

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

复制代码

datasource 设置

Oracle 数据库连接设置一般会用到 validation-query: SELECT 1 FROM DUAL，换成 OpenGauss 后需要注释掉这个设置。
启示录

目前发现的就是这些问题，解决的照旧比较顺遂的。照旧需要对整个体系的功能逐个举行测试，直接用 MyBatis 的框架封装的方法没有问题，贫苦的是各种通过 @Select 注解嵌入在代码中的SQL语句，需要逐个排查。
一开始约定好SQL语句都在 resource 中界说的话，相对会比较好一点，如果有不同的话，就可以放在不同目录里面通过 mybatis-plus.mapper-locations 设置来指定。但是在 DAO 里面界说的 SQL 就必须通过界说多个方法来区分了。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。