若依@v3.8.6前后端分离版集成达梦数据库(DM8)

一、安装达梦数据库(DM8)

默认引导安装即可

二、引入达梦数据库驱动

1、由于 SpringBoot 没有对达梦数据库做集成,需要手动引入达梦数据库驱动jar包,驱动jar包在其安装目录 \dmdbms\drivers\jdbc 目录下

 2、这三个驱动包分别对应的是 JDK 的版本,我用的是 JDK1.8,所以我选择的是 DmJdbcDriver18.jar 驱动包,将其放在 resources\lib\ 目录下

3、在pom.xml文件中,引入该文件

<!-- 达梦数据库驱动 -->
<dependency>
    <groupId>com.dm</groupId>
    <artifactId>DmJdbcDriver18</artifactId>
    <version>1.8</version>
    <scope>system</scope>
    <systemPath>${project.basedir}/src/main/resources/lib/DmJdbcDriver18.jar</systemPath>
</dependency>

三、配置达梦数据库信息

 在 application-druid.yml 文件中,配置信息如下:

# 数据源配置
spring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource
#        driverClassName: com.mysql.cj.jdbc.Driver
        driverClassName: dm.jdbc.driver.DmDriver
        druid:
            # 主库数据源
            master:
#                url: jdbc:mysql://localhost:3306/ry-vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
#                username: root
#                password: root
                url: jdbc:dm://localhost:5236/
                username: SYSDBA
                password: SYSDBA

与 MySQL 配置信息不同如下:

driver
之前:com.mysql.cj.jdbc.Driver
现在:dm.jdbc.driver.DmDriver

url
之前:jdbc:mysql://localhost:3306/ry-vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
现在:jdbc:dm://localhost:5236/

username
之前:root
现在:SYSDBA

password
之前:root
现在:SYSDBA

四、适配DM不支持的SQL函数 

1、replace into函数:SysUserOnlineMapper.xml里的saveOnline 方法,用到了不支持的replace into函数,需替换成merge into函数。

原语句:

<INSERT id="saveOnline" parameterType="SysUserOnline">
 REPLACE INTO sys_user_online(sessionId, login_name, dept_name, ipaddr, login_location, browser, os, STATUS, start_timestamp, last_access_time, expire_time)
  VALUES (#{sessionId}, #{loginName}, #{deptName}, #{ipaddr}, #{loginLocation}, #{browser}, #{os}, #{status}, #{startTimestamp}, #{lastAccessTime}, #{expireTime})
</INSERT>

替换语句:

<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,#{tatus} 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>

2、find_in_set:SysDeptMapper.xml里selectChildrenDeptById方法和selectNormalChildrenDeptById方法,用到了不支持的find_in_set函数,需替换成like。 

原代码:

	<select id="selectChildrenDeptById" parameterType="Long" resultMap="SysDeptResult">
		select * from sys_dept where find_in_set(#{deptId}, ancestors)
	</select>
	
	<select id="selectNormalChildrenDeptById" parameterType="Long" resultType="int">
		select count(*) from sys_dept where status = 0 and del_flag = '0' and find_in_set(#{deptId}, ancestors)
	</select>

替换代码:

	<select id="selectChildrenDeptById" parameterType="Long" resultMap="SysDeptResult">
		select * from sys_dept where ancestors like concat('%', #{deptId}, '%')
	</select>
	
	<select id="selectNormalChildrenDeptById" parameterType="Long" resultType="int">
		select count(*) from sys_dept where status = 0 and del_flag = '0' and ancestors like concat('%', #{deptId}, '%')
	</select>

3、find_in_set:SysUserMapper.xml里selectUserList方法,用到了不支持的find_in_set函数,需替换成like。  

原代码:

<select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
		select u.user_id, u.dept_id, u.nick_name, u.user_name, u.email, u.avatar, u.phonenumber, u.sex, u.status, u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark, d.dept_name, d.leader from sys_user u
		left join sys_dept d on u.dept_id = d.dept_id
		where u.del_flag = '0'
		<if test="userId != null and userId != 0">
			AND u.user_id = #{userId}
		</if>
		<if test="userName != null and userName != ''">
			AND u.user_name like concat('%', #{userName}, '%')
		</if>
		<if test="status != null and status != ''">
			AND u.status = #{status}
		</if>
		<if test="phonenumber != null and phonenumber != ''">
			AND u.phonenumber like concat('%', #{phonenumber}, '%')
		</if>
		<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
			AND date_format(u.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(u.create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
		</if>
		<if test="deptId != null and deptId != 0">
			AND (u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE find_in_set(#{deptId}, ancestors) ))
		</if>
		<!-- 数据范围过滤 -->
		${params.dataScope}
	</select>

替换代码:

    <select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
		select u.user_id, u.dept_id, u.nick_name, u.user_name, u.email, u.avatar, u.phonenumber, u.sex, u.status, u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark, d.dept_name, d.leader from sys_user u
		left join sys_dept d on u.dept_id = d.dept_id
		where u.del_flag = '0'
		<if test="userId != null and userId != 0">
			AND u.user_id = #{userId}
		</if>
		<if test="userName != null and userName != ''">
			AND u.user_name like concat('%', #{userName}, '%')
		</if>
		<if test="status != null and status != ''">
			AND u.status = #{status}
		</if>
		<if test="phonenumber != null and phonenumber != ''">
			AND u.phonenumber like concat('%', #{phonenumber}, '%')
		</if>
		<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
			AND date_format(u.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(u.create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
		</if>
		<if test="deptId != null and deptId != 0">
			AND (u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE ancestors like concat('%', #{deptId}, '%') ))
		</if>
		<!-- 数据范围过滤 -->
		${params.dataScope}
	</select>

 4、find_in_set:DataScopeAspect.java里dataScopeFilter方法,用到了不支持的find_in_set函数,需替换成like。  

原代码:

                sqlString.append(StringUtils.format(
                        " OR {}.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = {} or find_in_set( {} , ancestors ) )",
                        deptAlias, user.getDeptId(), user.getDeptId()));

 替换代码:

                sqlString.append(StringUtils.format(
                        " OR {}.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = {} or ancestors like concat('%', {}, '%') )",
                        deptAlias, user.getDeptId(), user.getDeptId()));

五、适配DM数据库自动生成代码

1、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})
        select column_name,
		from user_col_comments where table_name = (#{tableName})
		order by ordinal_position
	</select>

替换代码:

<select id="selectDbTableColumnsByName" parameterType="String" resultMap="GenTableColumnResult">
        SELECT t3.COLUMN_NAME AS column_name
            , CASE
                WHEN t3.NULLABLE = 'N'
                    AND t4.CONSTRAINT_TYPE != 'P'
                THEN '1'
                ELSE NULL
            END AS is_required
            , IF(t4.CONSTRAINT_TYPE = 'P', 1, 0) AS is_pk
            , t3.COLUMN_ID AS sort, t5.COMMENTS AS column_comment
            , CASE
                WHEN (t3.TYPE = 'INT'
                        OR t3.TYPE = 'INTEGER'
                        OR t3.TYPE = 'BIGINT'
                        OR t3.TYPE = 'TINYINT'
                        OR t3.TYPE = 'SMALLINT')
                    AND t4.CONSTRAINT_TYPE = 'P'
                THEN '1'
                ELSE '0'
            END AS is_increment, DATA_TYPE AS DATA_TYPE
        FROM (
            SELECT COLUMN_NAME, COLUMN_ID
                , CONCAT(DATA_TYPE, '(', DATA_LENGTH, ')') AS DATA_TYPE
                , DATA_TYPE AS TYPE, TABLE_NAME, NULLABLE
            FROM SYS.USER_TAB_COLUMNS
            WHERE table_name = #{tableName}
        ) t3
            LEFT JOIN (
                SELECT COMMENTS, COLUMN_NAME, TABLE_NAME
                FROM SYS.USER_COL_COMMENTS
            ) t5
            ON t3.COLUMN_NAME = t5.COLUMN_NAME
                AND t3.TABLE_NAME = t5.TABLE_NAME
            LEFT JOIN (
                SELECT t1.CONSTRAINT_TYPE, t1.OWNER, t1.TABLE_NAME, t2.CONSTRAINT_NAME, t2.COLUMN_NAME
                FROM (
                    SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, OWNER, TABLE_NAME
                    FROM SYS.USER_CONSTRAINTS
                ) t1
                    INNER JOIN (
                        SELECT CONSTRAINT_NAME, OWNER, TABLE_NAME, COLUMN_NAME
                        FROM SYS.USER_CONS_COLUMNS
                    ) t2
                    ON t1.TABLE_NAME = t2.TABLE_NAME
                        AND t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
                WHERE t1.CONSTRAINT_TYPE = 'P'
            ) t4
            ON t3.COLUMN_NAME = t4.COLUMN_NAME
                AND t3.TABLE_NAME = t4.TABLE_NAME
        ORDER BY t3.COLUMN_ID
    </select>

2、GenTableMapper.xml里selectDbTableList方法、selectDbTableListByNames方法和selectTableByName方法

原代码:

	<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 != ''">&lt;!&ndash; 开始时间检索 &ndash;&gt;-->
	<!--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 != ''">&lt;!&ndash; 结束时间检索 &ndash;&gt;-->
	<!--AND date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')-->
	<!--</if>-->
	<!--order by create_time desc-->
	select table_name,comments AS table_comment from user_tab_comments WHERE 1=1
	AND table_name NOT LIKE '##%'AND table_name NOT LIKE 'BAK_%'
	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>
	order by table_name desc
</select>

	<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())
		select table_name,comments AS table_comment from user_tab_comments WHERE 1=1
		AND table_name NOT LIKE '##%'AND table_name NOT LIKE 'BAK_%'
		AND table_name NOT LIKE 'qrtz_%' and table_name NOT LIKE 'gen_%'
		and table_name in
		<foreach collection="array" item="name" open="(" separator="," close=")">
			#{name}
		</foreach>
	</select>

	<select id="selectTableByName" parameterType="String" resultMap="GenTableResult">
		select table_name,comments AS table_comment from user_tab_comments
		where table_comment <![CDATA[ <> ]]> ''
-- 		select table_name, table_comment, create_time, update_time from information_schema.tables
-- 		where table_comment <![CDATA[ <> ]]> '' and table_schema = (select database())
		and table_name = #{tableName}
	</select>

替换代码:

	<select id="selectDbTableList" parameterType="GenTable" resultMap="GenTableResult">
		SELECT t1.TABLE_NAME AS table_name, t2.COMMENTS AS table_comment, NULL AS create_time, NULL AS update_time
		FROM SYS.USER_TABLES t1
		INNER JOIN SYS.USER_TAB_COMMENTS t2 ON t1.TABLE_NAME = t2.TABLE_NAME
		WHERE t1.TABLE_NAME NOT LIKE 'qrtz_%'
		AND t1.TABLE_NAME NOT LIKE 'gen_%'
		AND t1.TABLE_NAME NOT IN (SELECT table_name AS TABLE_NAME FROM gen_table)
		<if test="tableName != null and tableName != ''">AND lower(t1.TABLE_NAME) like lower(concat('%', #{tableName}, '%'))</if>
		<if test="tableComment != null and tableComment != ''">AND lower(t1.TABLE_NAME) like lower(concat('%', #{tableName}, '%'))</if>
	</select>
	
	<select id="selectDbTableListByNames" resultMap="GenTableResult">
		SELECT t1.TABLE_NAME AS table_name, t2.COMMENTS AS table_comment, NULL AS create_time, NULL AS update_time
		FROM SYS.USER_TABLES t1
		INNER JOIN SYS.USER_TAB_COMMENTS t2 ON t1.TABLE_NAME = t2.TABLE_NAME
		WHERE t1.TABLE_NAME NOT LIKE 'qrtz_%'
		AND t1.TABLE_NAME NOT LIKE 'gen_%'
		AND t1.TABLE_NAME IN
		<foreach collection="array" item="name" open="(" separator="," close=")">#{name}</foreach>
	</select>
	
	<select id="selectTableByName" parameterType="String" resultMap="GenTableResult">
		SELECT t1.TABLE_NAME AS table_name, t2.COMMENTS AS table_comment, NULL AS create_time, NULL AS update_time
		FROM SYS.USER_TABLES t1
		INNER JOIN SYS.USER_TAB_COMMENTS t2 ON t1.TABLE_NAME = t2.TABLE_NAME
		WHERE t2.COMMENTS <![CDATA[ <> ]]> ''
		AND t1.TABLE_NAME = #{tableName}
	</select>

六、【达梦数据库驱动】外部jar包打包参考:
若依@v3.8.6前后端分离版【SpringBoot】外部jar包添加及打包 

  • 6
    点赞
  • 15
    收藏
    觉得还不错? 一键收藏
  • 7
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 7
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值