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

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

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

sqlserver 获取schema、表、字段等关键信_aixin999的博客-CSDN博客