若依系统不分离版框架Mysql更换SqlServer

前言：最近下载开源项目若依ruoyi，将其更数据库Mysql更换SqlServer的一些注意事项，与君分享。

1、数据库安装

使用SSMA将mysql迁移至mssql,参考这两个文档

MySQL数据库迁移到SQL Server----------SSMA(SQL Server Migration Assistant for MySQL)工具安装及使用_weixin_39921737的博客-CSDN博客_ssma安装

ssma for mysql_SSMA for MySQL_weixin_39926193的博客-CSDN博客

若依的数据库脚本是mysql，先用navcate创建一个名为dbo的数据库。（名称最好是dbo，如果数据库名为 ruoyi，迁移到mssql后表名称为 ruoyi.sys_dept）

 运行这两个脚本，创建表

参考上述文档，将在mysql中建好的表，迁移到sql server2016中

2、参考文章，修改后台代码

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: 你的密码

  1、配置检测连接是否有效如下：

2、分页插件配置如下：

三、代码部分修改

1、定时任务配置ScheduleConfig类设置sqlserver 启用配置，这个原文件注释了，暂不修改。

2、SQL语句函数修改，用IDEA功能 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，用多处，慢慢改，如下（<= 符号 用 &lt;=  替换）

  <if test="params.beginTime != null and params.beginTime != ''">
      <!-- 开始时间检索 -->
  	and datediff(day,login_time，#{params.beginTime}) &lt;= 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 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>

• select count(1)...limit 1 ，直接删除limit 1 就可以

3、生成代码部分的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>

  4、代码生成器中修改sql.vm

  -- 按钮父菜单ID
  Declare @parentId int
  SELECT @parentId = @@IDENTITY