Docker部署达梦数据库
下载
安装
# 导入镜像
docker load -i dm8_20230808_rev197096_x86_rh6_64_single.tar
# 运行容器
docker run -d -p 5236:5236 --name dm8 --privileged=true -e CASE_SENSITIVE=N -e PAGE_SIZE=16 -e LD_LIBRARY_PATH=/opt/dmdbms/bin -e EXTENT_SIZE=32 -e BLANK_PAD_MODE=1 -e LOG_SIZE=1024 -e UNICODE_FLAG=1 -e LENGTH_IN_CHAR=1 -e INSTANCE_NAME=dm8 -v /mydata/services/dm8/data:/opt/dmdbms/data dm8_single:dm8_20230808_rev197096_x86_rh6_64
# -e CASE_SENSITIVE=N 为设置忽略大小写
若依框架整合
添加数据库驱动
<!--添加达梦数据库驱动安装包-->
<!--通过下载jar文件到本地的方式 jar文件位于安装目录的\drivers\jdbc文件夹下 -->
<!-- <dependency>-->
<!-- <groupId>com.dameng</groupId>-->
<!-- <artifactId>Dm8JdbcDriver18</artifactId>-->
<!-- <version>8.1.3.100</version>-->
<!-- <scope>system</scope>-->
<!-- <systemPath>${project.basedir}/src/main/resources/libraries/DmJdbcDriver18.jar</systemPath>-->
<!-- </dependency>-->
<!-- 使用本地jar包安装需要在打包插件中配置includeSystemScope属性为true -->
<!-- <plugin>-->
<!-- <groupId>org.springframework.boot</groupId>-->
<!-- <artifactId>spring-boot-maven-plugin</artifactId>-->
<!-- <configuration>-->
<!-- <includeSystemScope>true</includeSystemScope>-->
<!-- </configuration>-->
<!-- </plugin>-->
<!--通过Maven仓库 -->
<dependency>
<groupId>com.dameng</groupId>
<artifactId>DmJdbcDriver18</artifactId>
<version>8.1.3.62</version>
</dependency>
添加数据源配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: dm.jdbc.driver.DmDriver
druid:
# 主库数据源
master:
url: jdbc:dm://localhost:5236/zhglxt_cms
username: SYSDBA
# mysql数据库密码
password: SYSDBA001
如果连接不成功，连接url可使用jdbc:dm://localhost:5236?schema=zhglxt_cms的方式进行尝试
不兼容处调整
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') >= 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') <= 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') >= 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') <= 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()));
5、将"`"替换掉
IDEA批量查找，使用正则匹配进行替换
`([a-zA-Z]+)`替换为$1
6、将查询语句中AS后面的单引号改为双引号
IDEA批量查找，使用正则匹配进行替换
AS '([a-zA-Z]+)'替换为AS "$1"
适配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 != ''"><!– 开始时间检索 –>-->
<!--AND date_format(create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')-->
<!--</if>-->
<!--<if test="params.endTime != null and params.endTime != ''"><!– 结束时间检索 –>-->
<!--AND date_format(create_time,'%y%m%d') <= 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>
