RuoYi系统如何与SQLServer数据库完美搭配

最近在使用RuoYi系统开发ERP系统，由于需要使用SQLServer数据库来作为系统数据库所以有了今天的文章。

先给出完整代码，包含数据库脚本：https://github.com/elnujuw/RuoYi-SQLServer

本文使用的数据库版本是Microsoft SQL Server 2019，下面将介绍具体实现步骤：

1. ruoyi-admin工程下的pom.xml文件添加SQLServer驱动依赖包

   <!-- SQLServer驱动包 -->
   <dependency>
       <groupId>com.microsoft.sqlserver</groupId>
       <artifactId>mssql-jdbc</artifactId>
   </dependency>

2. 修改application-druid.yml配置文件

   1. 修改driverClassName

      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver

   2. 修改主数据源url

      url: jdbc:sqlserver://数据库ip:1433;SelectMethod=cursor;DatabaseName=数据库名称

   3. 修改validationQuery

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

3. 修改application-druid.yml配置文件

   # PageHelper分页插件
   pagehelper:
   	helperDialect: sqlserver2012

4. 修改SQL语句函数

   1. ifnull 替换为 isnull

   2. 替换find_in_set，例如：

      find_in_set(#{deptId}, ancestors) 替换为  charindex (',' + CONVERT (VARCHAR, #{deptId}), ',' + ancestors) > 0

   3. concat 替换为 ‘’+’’

   4. sysdate() 替换为 getdate()

   5. 替换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

   6. limit 1 替换为 top(1)

5. 修改代码生成器

   1. 修改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)

   2. 修改selectDbTableListByName为：

      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> 

   3. 修改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数据库mybatis批量插入时数字可能会丢失精度

     解决方法，参考以下批量插入，使用cast(#{item.rate,jdbcType=DECIMAL} as decimal(18,6))如：

     <insert id="batchBaseMoneyrateDetail">
         insert into base_moneyrate_d( moneyrate_id, item_id, money_id, rate) values
         <foreach item="item" index="index" collection="list" separator=",">
             ( #{item.moneyrateId}, #{item.itemId}, #{item.moneyId}, cast(#{item.rate,jdbcType=DECIMAL} as decimal(18,6)))
         </foreach>
     </insert>

   • 代码生成器导入表没有可选的表数据

     原因是数据库表没有设置表说明，解决方法（如角色表，添加表说明）：

     /* 为了防止任何可能出现的数据丢失问题，您应该先仔细检查此脚本，然后再在数据库设计器的上下文之外运行此脚本。*/
     BEGIN TRANSACTION
     SET QUOTED_IDENTIFIER ON
     SET ARITHABORT ON
     SET NUMERIC_ROUNDABORT OFF
     SET CONCAT_NULL_YIELDS_NULL ON
     SET ANSI_NULLS ON
     SET ANSI_PADDING ON
     SET ANSI_WARNINGS ON
     COMMIT
     BEGIN TRANSACTION
     GO
     DECLARE @v sql_variant 
     SET @v = N'角色表'
     EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'sys_role', NULL, NULL
     GO
     ALTER TABLE dbo.sys_role SET (LOCK_ESCALATION = TABLE)
     GO
     COMMIT