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

最新推荐文章于 2025-03-13 15:36:49 发布

鄂东男孩

最新推荐文章于 2025-03-13 15:36:49 发布

阅读量3.7k

点赞数 5

分类专栏： java 文章标签： mysql sqlserver 数据库

本文链接：https://blog.youkuaiyun.com/kay523393/article/details/126059496

版权

java 专栏收录该内容

26 篇文章

订阅专栏

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

1、数据库安装

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

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

ssma for mysql_SSMA for MySQL_weixin_39926193的博客-优快云博客

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

运行这两个脚本，创建表

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

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

ruoyi（若依）系统使用SqlServer数据库_op4439的博客-优快云博客_若依数据库

一、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，用多处，慢慢改，如下（<= 符号用 <= 替换）

<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