修改不兼容的SQL语句

使用VS Code打开源码，针对上述3类不兼容性的问题，修改SQL语句，方式如下：

不兼容问题一：DM8不支持replace into方法的使用，需要改成merge into的用法。

修改点：找到文件“ruoyi-system/src/main/resources/mapper/system/SysUserOnlineMapper.xml”，将id为“saveOnline”对应的SQL片段中<insert></insert>标签包裹的内容改写成merge into的用法，如下所示：

       
            <insert id="saveOnline" parameterType="SysUserOnline">
         merge into sys_user_online
                   using (select #{sessionId} sessionId, #{loginName} login_name, #{deptName} dept_name, #{ipaddr} ipaddr, #{loginLocation} login_location, #{browser} browser, #{os} os,
                                                 #{status} status, #{startTimestamp} start_timestamp, #{lastAccessTime} last_access_time, #{expireTime} expire_time from dual) d
                   on sys_user_online.sessionId = d.sessionId
                   when matched then
                            update set sys_user_online.login_name = d.login_name, sys_user_online.dept_name = d.dept_name, sys_user_online.ipaddr = d.ipaddr,
                                     sys_user_online.login_location = d.login_location, sys_user_online.browser = d.browser, sys_user_online.os = d.os, sys_user_online.status = d.status,
                                     sys_user_online.start_timestamp = d.start_timestamp, sys_user_online.last_access_time = d.last_access_time, sys_user_online.expire_time = d.expire_time
                   when not matched then
                            insert (sessionId, login_name, dept_name, ipaddr, login_location, browser, os, status, start_timestamp, last_access_time, expire_time)
                                     values(d.sessionId, d.login_name, d.dept_name, d.ipaddr, d.login_location, d.browser, d.os, d.status, d.start_timestamp, d.last_access_time, d.expire_time)
</insert>

效果如下：

不兼容问题二：cast函数。DM8中的cast函数不支持将blob类型转换成字符串类型。
修改点：找到文件“ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysNoticeMapper.java”，将函数“selectNoticeById”和“selectNoticeList”注解SQL语句中的“cast(notice_content as char)”方法替换成如下方法：
```
UTL_I18N.RAW_TO_CHAR(SUBSTRBLB(notice_content,1,3000),'UTF8')
```
效果如下：

不兼容问题三：DM8没有information_schema等MySQL自带的数据库表。

修改点：找到文件“ruoyi-generator/src/main/resources/mapper/generator/GenTableMapper.xml”，改写id为“selectDbTableList”的SQL片段，如下框红色字体部分：

       
            <select id="selectDbTableList" parameterType="GenTable" resultMap="GenTableResult">
         select so.NAME table_name, st.COMMENT$ table_comment, so.CRTDATE create_time from SYS.SYSOBJECTS so left join SYS.SYSTABLECOMMENTS st on so.NAME = st.TVNAME and st.TABLE_TYPE = 'TABLE' and st.SCHNAME = (select CURR_SCH from V$SESSIONS LIMIT 1)
         where so.TYPE$='SCHOBJ' and so.SUBTYPE$='UTAB' and so.PID = -1 and INSTR(so.NAME,'##') = 0 and so.SCHID = (select ID from SYS.SYSOBJECTS where TYPE$='SCH' and NAME= (select CURR_SCH from V$SESSIONS LIMIT 1)) and so.NAME NOT LIKE 'QRTZ_%' AND so.NAME NOT LIKE 'gen_%'
         and so.NAME NOT IN (select table_name from gen_table)
         <if test="tableName != null and tableName != ''">
                   and lower(so.NAME) like lower(concat('%', #{tableName}, '%'))
         </if>
         <if test="tableComment != null and tableComment != ''">
                   and lower(st.COMMENT$) like lower(concat('%', #{tableComment}, '%'))
         </if>
         order by so.CRTDATE desc
</select>

效果如下：

父主题： 应用迁移