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

https://blog.csdn.net/op4439/article/details/121469911

近期使用若依前后端分离版开发一个公司内部系统，由于指定使用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

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

参考：sqlserver查看表名及表注释_毒牙-CSDN博客_sqlserver获取表注释