ruoyi（若依）系统使用SqlServer数据库

近期使用若依前后端分离版开发一个公司内部系统，由于指定使用SqlServer数据库，所以采用若依3.7.0版，自己转换脚本及对应查询。

具体修改如下：

1、admin把mysql引用替换为

<dependency>
   <groupId>com.microsoft.sqlserver</groupId>
   <artifactId>mssql-jdbc</artifactId>
</dependency>

2、数据库配置修改：driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver

其他就是连接及用户密码信息

# 配置检测连接是否有效 validationQuery: select x

分页pagehelper配置 helperDialect: sqlserver

3、定时任务配置ScheduleConfig类设置sqlserver 启用配置

4、SQL语句函数修改

ifnull 替换为 isnull 
find_in_set find_in_set(#{deptId}, ancestors) 替换为  charindex (, + CONVERT (VARCHAR, #{deptId}), , + ancestors) > 0
concat 替换为 +
sysdate  替换为 getdate
date_format date_format(u.create_time,%y%m%d) >= date_format(#{params.beginTime},%y%m%d) 替换为 DATEDIFF(day, u.create_time , #{params.endTime}) >= 0 
limit 1    替换为 top(1) （注意位置差异）

5、生成代码包SQL修改

selectDbTableList 修改为

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)

selectDbTableListByNames修改为

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>

selectDbTableColumnsByName修改为

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

参考：