1.数据库选用:sql server 2016
如果是sql sever2008,需要升级,新版本添加了新函数,可以减少修改工作量
sql server 2008 R2 企业版 升级2016,需要先把r2升级到sp3版本,参考
SqlServer 2008R2 10.50.1600.1 升级到 SqlServer 2016_Jalan.Wang的博客-CSDN博客
----------------------------
2.数据库安装
使用SSMA将mysql迁移至mssql,参考这两个文档
ssma for mysql_SSMA for MySQL_weixin_39926193的博客-CSDN博客
若依的数据库脚本是mysql,先用navcate创建一个名为dbo的数据库。(名称最好是dbo。如果数据库名为 ruoyi,迁移到mssql后表名称为 ruoyi.sys_dept)
运行这两个脚本,创建表
参考上述文档,将在mysql中建好的表,迁移到sql server2016中
3.修改后端。参考
ruoyi(若依)系统使用SqlServer数据库_op4439的博客-CSDN博客_若依数据库
一、ruoyi-admin 中pom.xml直接加上mssql驱动,
<!--mssql驱动包-->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
</dependency>
二、修改数据库配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
druid:
# 主库数据源
master:
url: jdbc:sqlserver://localhost:1433;DatabaseName=ry
username: sa
password: 你的密码
配置检测连接是否有效
分页插件配置
3、定时任务配置ScheduleConfig类设置sqlserver 启用配置。。。这个原文件注释了,暂不修改
4.SQL语句函数修改。用Find in files 全局检索修改
- ifnull() 改 isnull()
- find_in_set 修改,下面两处,在SysDeptMapper
<select id="selectChildrenDeptById" parameterType="Long" resultMap="SysDeptResult">
select * from sys_dept where charindex (',' + CONVERT (VARCHAR, #{deptId}), ',' + ancestors) > 0
</select>
<select id="selectNormalChildrenDeptById" parameterType="Long" resultType="int">
select count(*) from sys_dept where status = 0 and del_flag = '0' and charindex (',' + CONVERT (VARCHAR, #{deptId}), ',' + ancestors) > 0
</select>
-
concat 替换为 ''+'', server2016 支持这个函数,不需要修改
-
sysdate 替换为 getdate, 这个比较多,用替换Replace
-
-
date_format 日期范围检索的,改用datediff,用多处,慢慢改,如下(<= 符号 用 <= 替换)
-
<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 --> and datediff(day,login_time,#{params.beginTime}) <= 0 </if> <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 --> and datediff(day,login_time,#{params.endTime}) >= 0 </if>
-
limit 1 替换成 top 1 ,位置需要修改。(这种好像只有一处,sysConfigMapper.xml)
-
其余的 如select count(1)...limit 1 ,直接删除limit 1 就可以
<select id="checkConfigKeyUnique" parameterType="String" resultMap="SysConfigResult">
select top 1 config_id, config_name, config_key, config_value, config_type, create_by, create_time, update_by, update_time, remark
from sys_config
where config_key = #{configKey}
</select>
5.生成代码部分的sql修改
- selectDbTableList,selectDbTableListByNames
<select id="selectDbTableList" parameterType="GenTable" resultMap="GenTableResult">
SELECT so.name table_name,sep.value table_comment,so.create_date create_time,so.modify_date update_time
FROM sys.objects AS so
LEFT JOIN sys.extended_properties AS sep ON so.object_id = sep.major_id
WHERE so.type = 'U'
AND sep.minor_id = 0
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(cast(sep.value as varchar)) like lower(concat('%', #{tableComment}, '%'))
</if>
<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
AND datediff(day,#{params.beginTime},create_time) >=0
</if>
<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
AND datediff(day,create_time,#{params.endTime}) >=0
</if>
order by create_time desc
</select>
<select id="selectDbTableListByNames" resultMap="GenTableResult">
SELECT SO.name table_name,SEP.VALUE table_comment,SO.create_date create_time,SO.modify_date update_time
FROM sys.objects AS SO
LEFT JOIN sys.extended_properties AS SEP ON SO.object_id = SEP.major_id
WHERE SO.type = 'U'
AND SEP.minor_id = 0
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>
- selectDbTableColumnsByName , 在GenTableColumnMapper.xml文件中
-
<select id="selectDbTableColumnsByName" parameterType="String" resultMap="GenTableColumnResult"> SELECT a.name AS column_name, (CASE WHEN a.isnullable = 1 THEN 0 ELSE 1 END) AS is_required, (CASE WHEN ( SELECT COUNT(*) FROM sysobjects WHERE ( name IN ( SELECT name FROM sysindexes WHERE (id = a.id) AND (indid IN (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid IN (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK')) > 0 THEN 1 ELSE 0 END) AS is_pk, a.colorder AS sort, isnull(g.[value], ' ') AS column_comment, (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN 1 ELSE 0 END ) AS is_increment, b.name AS column_type FROM syscolumns a LEFT JOIN systypes b ON a.xtype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <![CDATA[ <> ]]> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT JOIN sys.extended_properties f ON d.id = f.class AND f.minor_id = 0 LEFT JOIN sys.objects h ON a.id = h.object_id LEFT JOIN sys.schemas i ON h.schema_id = i.schema_id WHERE d.name = #{tableName} ORDER BY a.colorder </select>
6.代码生成器修改。sql.vm
-
-- 按钮父菜单ID Declare @parentId int SELECT @parentId = @@IDENTITY
-