RuoYi-Vue切换达梦数据库（二）

标签：Vue NAME create RuoYi so time table 达梦 name

一、JDBC依赖

1、修改admin模块下的pom.xml文件

Maven仓库中存在，直接替换

注意jdbc包的版本：Dm7JdbcDriver18中7是DM数据库的版本，18是JDK版本。

<!--达梦数据库驱动-->
<dependency>
    <groupId>com.dameng</groupId>
    <artifactId>Dm8JdbcDriver18</artifactId>
    <version>8.1.1.49</version>
</dependency>
        
<!--达梦数据库方言-->
<dependency>
    <groupId>com.dameng</groupId>
    <artifactId>DmDialect-for-hibernate5.3</artifactId>
    <version>8.1.1.49</version>
</dependency>

二、更改数据库链接配置

更改driverClassName,url,username,password

spring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: dm.jdbc.driver.DmDriver
        druid:
            # 主库数据源
            master:
                url: jdbc:dm://localhost:5236
                username: RY_VUE
                password: 123456789

三、更改SQL函数

先运行起来、未发现什么报错

接下来就从登录开始每个功能都进行增删改查的操作

下面是需要调整的内容：

1、SysMenuMapper.xml文件

selectMenuVo、selectMenuTreeAll、selectMenuListByUserId、selectMenuTreeByUserId、updateMenu、insertMenu方法中的 `query`

用单引号括起，被当成字符串，报语法错误。可更改单引号为双引号或者直接去掉单引号。

2、SysDeptMapper.xml文件

selectChildrenDeptById方法中find_in_set函数，可替换成instr函数

// 原始
<select id="selectChildrenDeptById" parameterType="Long" resultMap="SysDeptResult">
    select * from sys_dept where find_in_set(#{deptId}, ancestors)
</select>

// 修改后
<select id="selectChildrenDeptById" parameterType="Long" resultMap="SysDeptResult">
    select * from sys_dept where instr(','||ancestors||',' , ','|| #{deptId} || ',')
</select>

3、SysNoticeMapper.xml文件

表SYS_NOTICE字段 NOTICE_CONTENT 在迁移过程中由mysql的 longblob类型变成DM中的BLOB类型

selectNoticeVo函数中查询SQL将BLOG类型转换成char导致语法出错

可将类型BLOG改成CLOB，更改前将表中数据清空；

再更改SQL中的 cast 函数，用 TO_CHAR 代替

// 原始
<sql id="selectNoticeVo">
    select notice_id, notice_title, notice_type, cast(notice_content as char) as notice_content, status, create_by, create_time, update_by, update_time, remark 
    from sys_notice
</sql>

// 修改后
<sql id="selectNoticeVo">
    select notice_id, notice_title, notice_type, cast(notice_content as char) as notice_content, status, create_by, create_time, update_by, update_time, remark 
    from sys_notice
</sql>

4、GenTableMapper.xml 文件

代码生成功能改动较大，需要重写针对DM重新对表信息、字段信息的SQL

在DM中查询表和列定义信息的简要说明：

SYS.SYSOBJECTS，记录系统中所有对象的信息，表定义信息从这里获取；
SYS.SYSTABLECOMMENTS，记录表或视图的注释信息；
SYS.SYSCOLUMNS，记录列定义信息；
SYS.SYSCOLUMNCOMMENTS，记录列的注释信息；
获取当前模式名称：SELECT SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID);

a）selectDbTableList 方法

// 原始
<select id="selectDbTableList" parameterType="GenTable" resultMap="GenTableResult">        
　　select table_name, table_comment, create_time, update_time from information_schema.tables
　　where table_schema = (select database())
　　AND table_name NOT LIKE 'qrtz_%' AND table_name NOT LIKE 'gen_%'
　　AND table_name NOT IN (select table_name from gen_table)
　　<if test="tableName != null and tableName != ''">
　　　　AND lower(table_name) like lower(concat('%', #{tableName}, '%'))
　　</if>
　　<if test="tableComment != null and tableComment != ''">
　　　　AND lower(table_comment) like lower(concat('%', #{tableComment}, '%'))
　　</if>
　　<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
　　　　AND date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
　　</if>
　　<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
　　　　AND date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
　　</if>
　　order by create_time desc
</select>

// 修改后
<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 SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID))
　　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 SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID))) 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>

b）selectDbTableListByNames 方法

// 原始
<select id="selectDbTableListByNames" resultMap="GenTableResult">
　　select table_name, table_comment, create_time, update_time from information_schema.tables
　　where table_name NOT LIKE 'qrtz_%' and table_name NOT LIKE 'gen_%' and table_schema = (select database())
　　and table_name in
　　<foreach collection="array" item="name" open="(" separator="," close=")">
　　　　#{name}
　　</foreach> 
</select>

// 修改后
<select id="selectDbTableListByNames" 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 SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID))
　　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 SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID))) and so.NAME NOT LIKE 'qrtz_%' and so.NAME NOT LIKE 'gen_%'
　　and so.NAME in
　　<foreach collection="array" item="name" open="(" separator="," close=")">
　　　　#{name}
　　</foreach>
</select>

c）selectTableByName 方法

......好像没用到，删掉

5、GenTableColumnMapper.xml 文件

selectDbTableColumnsByName 方法

// 原始
<select id="selectDbTableColumnsByName" parameterType="String" resultMap="GenTableColumnResult">
　　select column_name, (case when (is_nullable = 'no' <![CDATA[ && ]]> column_key != 'PRI') then '1' else null end) as is_required, (case when column_key = 'PRI' then '1' else '0' end) as is_pk, ordinal_position as sort, column_comment, (case when extra = 'auto_increment' then '1' else '0' end) as is_increment, column_type
　　from information_schema.columns where table_schema = (select database()) and table_name = (#{tableName})
　　order by ordinal_position
</select>

// 修改后
<select id="selectDbTableColumnsByName" parameterType="String" resultMap="GenTableColumnResult">
　　select sc.NAME column_name, (case when sc.INFO2 = 1 then '1' else '0'  end) is_pk, sc.COLID sort, scc.COMMENT$ column_comment, (case when sc.INFO2 = 1 then '1' else '0' end) as is_increment, sc.TYPE$ column_type
　　from SYS.SYSCOLUMNS sc left join SYS.SYSOBJECTS so on sc.ID = so.ID left join SYS.SYSCOLUMNCOMMENTS SCC on sc.NAME = scc.COLNAME and scc.TABLE_TYPE = 'TABLE' and scc.SCHNAME = (select sys_context('USERENV','CURRENT_SCHEMA')) and scc.TVNAME= so.NAME
　　where so.TYPE$='SCHOBJ' and so.SUBTYPE$='UTAB' and so.PID = -1 and so.SCHID = (select ID from SYS.SYSOBJECTS where TYPE$='SCH' and NAME= (select sys_context('USERENV','CURRENT_SCHEMA'))) and so.NAME = #{tableName}
</select>

6、sql.vm适配DM

适配模板中声明变量的方式，下面是完整内容：

-- 菜单 SQL
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}', '${parentMenuId}', '1', '${businessName}', '${moduleName}/${businessName}/index', 1, 0, 'C', '0', '0', '${permissionPrefix}:list', '#', 'admin', sysdate(), '', null, '${functionName}菜单');

DECLARE
-- 按钮父菜单ID
parentId BIGINT := @@IDENTITY;

BEGIN
-- 按钮 SQL
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}查询', parentId, '1',  '#', '', 1, 0, 'F', '0', '0', '${permissionPrefix}:query','#', 'admin', sysdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}新增', parentId, '2',  '#', '', 1, 0, 'F', '0', '0', '${permissionPrefix}:add','#', 'admin', sysdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}修改', parentId, '3',  '#', '', 1, 0, 'F', '0', '0', '${permissionPrefix}:edit','#', 'admin', sysdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}删除', parentId, '4',  '#', '', 1, 0, 'F', '0', '0', '${permissionPrefix}:remove','#', 'admin', sysdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}导出', parentId, '5',  '#', '', 1, 0, 'F', '0', '0', '${permissionPrefix}:export','#', 'admin', sysdate(), '', null, '');
END;

到现在基本上所有的功能都没报错了。可能会有遗漏也难免，遇到问题再解决吧。

参考

[1]. 解决达梦数据库如何查找表字段名。

[2]. RuoYi项目适配达梦数据库

标签：Vue,NAME,create,RuoYi,so,time,table,达梦,name
From： https://www.cnblogs.com/ryuug/p/16801973.html