若依前后端分离版切换数据库方法,以切换sqlServer为例[超详细]

已于 2024-03-11 17:09:05 修改
阅读量6.5k 收藏 51
点赞数 68
文章标签： 数据库 sql mybatis sqlserver
于 2022-07-18 16:26:24 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a864034462/article/details/125852203
版权

这么幸苦维护的一篇文章却没有人点赞,我还不如删了

接下来一步一步操作:

第一步:

ruoyi-admin下的poom里加上依赖

<!--sqlserver数据库配置-->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
        </dependency>

数据库配置这样修改:

# 数据源配置
 datasource:
   type: com.alibaba.druid.pool.DruidDataSource
   driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
   druid:
     # 主库数据源
     master:
       url: jdbc:sqlserver://localhost:1433;DataBaseName=数据库名
       username: 账户
       password: 密码

validationQuery的值也要修改成SELECT 1

validationQuery: SELECT 1

PageHelper分页插件,注销helperDialect方言设置,新增autoRuntimeDialect=true表示运行时获取数据源

#PageHelper分页插件
pagehelper:
 #  helperDialect: mysql
 supportMethodsArguments: true
 params: count=countSql
 autoRuntimeDialect: true
 #  如果不能分页就把这句加上
 #  dialect: com.github.pagehelper.dialect.helper.SqlServerDialect

第二步,整合mybatis-plus (可选):

ruoyi-common\pom.xml模块添加整合依赖

<!-- mybatis-plus 增强CRUD -->
<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>3.4.2</version>
</dependency>

application配置文件里修改MyBatis配置,改为mybatis-plus:
2024.2.18 更新.修改最新的配置👇

mybatis-plus:
  global-config:
    db-config:
      logic-delete-field: deleted
      logic-delete-value: 1
      logic-not-delete-value: 0
  #      update-strategy: ignored
  mapper-locations: classpath*:mapper/**/*Mapper.xml
  configuration:
    cache-enabled: true
    use-generated-keys: true
    log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl
    default-executor-type: SIMPLE
  #注意下面的包名,如果你改了包名,这里要改掉
  type-aliases-package: com.ruoyi.**.domain

framework.config包下添加Mybatis Plus配置MybatisPlusConfig.java。 PS：原来的MyBatisConfig.java需要删除掉

package com.ruoyi.framework.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.BlockAttackInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * Mybatis Plus 配置
 * 
 * @author ruoyi
 */
@EnableTransactionManagement(proxyTargetClass = true)
@Configuration
public class MybatisPlusConfig
{
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor()
    {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 分页插件
        interceptor.addInnerInterceptor(paginationInnerInterceptor());
        // 乐观锁插件
        interceptor.addInnerInterceptor(optimisticLockerInnerInterceptor());
        // 阻断插件
        interceptor.addInnerInterceptor(blockAttackInnerInterceptor());
        return interceptor;
    }

    /**
     * 分页插件，自动识别数据库类型 https://baomidou.com/guide/interceptor-pagination.html
     */
    public PaginationInnerInterceptor paginationInnerInterceptor()
    {
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        // 设置数据库类型为mysql
        // paginationInnerInterceptor.setDbType(DbType.MYSQL);
          // 设置数据库类型为sqlserver
        paginationInnerInterceptor.setDbType(DbType.SQL_SERVER);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        paginationInnerInterceptor.setMaxLimit(-1L);
        return paginationInnerInterceptor;
    }

    /**
     * 乐观锁插件 https://baomidou.com/guide/interceptor-optimistic-locker.html
     */
    public OptimisticLockerInnerInterceptor optimisticLockerInnerInterceptor()
    {
        return new OptimisticLockerInnerInterceptor();
    }

    /**
     * 如果是对全表的删除或更新操作，就会终止该操作 https://baomidou.com/guide/interceptor-block-attack.html
     */
    public BlockAttackInnerInterceptor blockAttackInnerInterceptor()
    {
        return new BlockAttackInnerInterceptor();
    }
}

第三步:

quartz.config包下的ScheduleConfig.java解除注释
在这里插入图片描述
SysMenuMapper.xml文件内,把4个ifnull全部改为isnull
在这里插入图片描述
自此是可以启动了,但是有个小问题.当按时间查询语句的时候遇到date_format会报错.比如这里
在这里插入图片描述
应改为这样

<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
	and datediff(d, create_time, #{params.beginTime}) <![CDATA[<=]]> 0
</if>
<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
	and datediff(d, create_time, #{params.endTime}) <![CDATA[>=]]> 0
</if>

2023.2.17更新: 我觉得上面不妥,下面这样最好:
<if test="params.endTime != null and params.endTime != ''">
	and verify_time between #{params.beginVerifyTime} and #{params.endVerifyTime}
</if>

这里需要改好几个文件的sql语句,但是有的不改其实关系也不大.(用不上就不报错呗)
比如sysdate()要改成getdate()
if null要改成isnull
limit 1 全部删掉
find_in_set(#{deptId}, ancestors)改成CHARINDEX(#{deptId}, ancestors) (2024.2.21更新,这个不改用户管理会出错)
date_format要改成datediff (注意这里datediff函数是有三个参数的,所以第一个参数要填dd)

这里是个坑,比如原来的语句是:
<if test="xxx">
and datediff(dd,oper_time,'%y%m%d') &lt;= datediff(dd,#{params.endTime},'%y%m%d')
</if>

那么要改成:
<if test="xxx">
and datediff(d, oper_time, #{params.endTime}) <![CDATA[>=]]> 0
</if>

FIND_IN_SET 函数需要改成CHARINDEX

比如原来的语句是:
select * from sys_dept where FIND_IN_SET (#{deptId}, ancestors)
那么要改成:
select * from sys_dept where CHARINDEX(#{deptId}, ancestors)> 0

2021.9 最新发现,代码生成功能有问题,需要改动GenTableColumnMapper.xml和GenTableMapper.xml两个文件,如下
GenTableColumnMapper:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.generator.mapper.GenTableColumnMapper">
    <resultMap type="GenTableColumn" id="GenTableColumnResult">
        <id     property="columnId"       column="column_id"      />
        <result property="tableId"        column="table_id"       />
        <result property="columnName"     column="column_name"    />
        <result property="columnComment"  column="column_comment" />
        <result property="columnType"     column="column_type"    />
        <result property="javaType"       column="java_type"      />
        <result property="javaField"      column="java_field"     />
        <result property="isPk"           column="is_pk"          />
        <result property="isIncrement"    column="is_increment"   />
        <result property="isRequired"     column="is_required"    />
        <result property="isInsert"       column="is_insert"      />
        <result property="isEdit"         column="is_edit"        />
        <result property="isList"         column="is_list"        />
        <result property="isQuery"        column="is_query"       />
        <result property="queryType"      column="query_type"     />
        <result property="htmlType"       column="html_type"      />
        <result property="dictType"       column="dict_type"      />
        <result property="sort"           column="sort"           />
        <result property="createBy"       column="create_by"      />
        <result property="createTime"     column="create_time"    />
        <result property="updateBy"       column="update_by"      />
        <result property="updateTime"     column="update_time"    />
    </resultMap>

    <sql id="selectGenTableColumnVo">
        select column_id, table_id, column_name, column_comment, column_type, java_type, java_field, is_pk, is_increment, is_required, is_insert, is_edit, is_list, is_query, query_type, html_type, dict_type, sort, create_by, create_time, update_by, update_time from gen_table_column
    </sql>

    <select id="selectGenTableColumnListByTableId" parameterType="Long" resultMap="GenTableColumnResult">
        <include refid="selectGenTableColumnVo"/>
        where table_id = #{tableId}
        order by sort
    </select>

    <select id="selectDbTableColumnsByName" parameterType="String" resultMap="GenTableColumnResult">
        SELECT
            cast(A.NAME as nvarchar) as column_name,
            cast(B.NAME as nvarchar) + (case when B.NAME ='numeric' then '('+cast(A.prec as nvarchar)+','+cast(A.scale as nvarchar)+')' else '' end) as column_type,
            cast(G.[VALUE] as nvarchar) as column_comment,
            (SELECT 1  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE Z  WHERE TABLE_NAME=D.NAME and A.NAME = Z.column_name  ) as is_pk,
            colorder as sort
        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!='DTPROPERTIES'
                 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.MAJOR_ID AND F.MINOR_ID   =0
        WHERE D.NAME = #{tableName}
        ORDER BY A.COLORDER
    </select>

    <insert id="insertGenTableColumn" parameterType="GenTableColumn" useGeneratedKeys="true" keyProperty="columnId">
        insert into gen_table_column (
        <if test="tableId != null and tableId != ''">table_id,</if>
        <if test="columnName != null and columnName != ''">column_name,</if>
        <if test="columnComment != null and columnComment != ''">column_comment,</if>
        <if test="columnType != null and columnType != ''">column_type,</if>
        <if test="javaType != null and javaType != ''">java_type,</if>
        <if test="javaField != null  and javaField != ''">java_field,</if>
        <if test="isPk != null and isPk != ''">is_pk,</if>
        <if test="isIncrement != null and isIncrement != ''">is_increment,</if>
        <if test="isRequired != null and isRequired != ''">is_required,</if>
        <if test="isInsert != null and isInsert != ''">is_insert,</if>
        <if test="isEdit != null and isEdit != ''">is_edit,</if>
        <if test="isList != null and isList != ''">is_list,</if>
        <if test="isQuery != null and isQuery != ''">is_query,</if>
        <if test="queryType != null and queryType != ''">query_type,</if>
        <if test="htmlType != null and htmlType != ''">html_type,</if>
        <if test="dictType != null and dictType != ''">dict_type,</if>
        <if test="sort != null">sort,</if>
        <if test="createBy != null and createBy != ''">create_by,</if>
        create_time
        )values(
        <if test="tableId != null and tableId != ''">#{tableId},</if>
        <if test="columnName != null and columnName != ''">#{columnName},</if>
        <if test="columnComment != null and columnComment != ''">#{columnComment},</if>
        <if test="columnType != null and columnType != ''">#{columnType},</if>
        <if test="javaType != null and javaType != ''">#{javaType},</if>
        <if test="javaField != null and javaField != ''">#{javaField},</if>
        <if test="isPk != null and isPk != ''">#{isPk},</if>
        <if test="isIncrement != null and isIncrement != ''">#{isIncrement},</if>
        <if test="isRequired != null and isRequired != ''">#{isRequired},</if>
        <if test="isInsert != null and isInsert != ''">#{isInsert},</if>
        <if test="isEdit != null and isEdit != ''">#{isEdit},</if>
        <if test="isList != null and isList != ''">#{isList},</if>
        <if test="isQuery != null and isQuery != ''">#{isQuery},</if>
        <if test="queryType != null and queryType != ''">#{queryType},</if>
        <if test="htmlType != null and htmlType != ''">#{htmlType},</if>
        <if test="dictType != null and dictType != ''">#{dictType},</if>
        <if test="sort != null">#{sort},</if>
        <if test="createBy != null and createBy != ''">#{createBy},</if>
        getdate()
        )
    </insert>

    <update id="updateGenTableColumn" parameterType="GenTableColumn">
        update gen_table_column
        <set>
            column_comment = #{columnComment},
            java_type = #{javaType},
            java_field = #{javaField},
            is_insert = #{isInsert},
            is_edit = #{isEdit},
            is_list = #{isList},
            is_query = #{isQuery},
            is_required = #{isRequired},
            query_type = #{queryType},
            html_type = #{htmlType},
            dict_type = #{dictType},
            sort = #{sort},
            update_by = #{updateBy},
            update_time = getdate()
        </set>
        where column_id = #{columnId}
    </update>

    <delete id="deleteGenTableColumnByIds" parameterType="Long">
        delete from gen_table_column where table_id in
        <foreach collection="array" item="tableId" open="(" separator="," close=")">
            #{tableId}
        </foreach>
    </delete>

    <delete id="deleteGenTableColumns">
        delete from gen_table_column where column_id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item.columnId}
        </foreach>
    </delete>

</mapper>

GenTableMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.generator.mapper.GenTableMapper">


    <resultMap type="GenTable" id="GenTableResult">
        <id     property="tableId"        column="table_id"          />
        <result property="tableName"      column="table_name"        />
        <result property="tableComment"   column="table_comment"     />
        <result property="subTableName"   column="sub_table_name"    />
        <result property="subTableFkName" column="sub_table_fk_name" />
        <result property="className"      column="class_name"        />
        <result property="tplCategory"    column="tpl_category"      />
        <result property="packageName"    column="package_name"      />
        <result property="moduleName"     column="module_name"       />
        <result property="businessName"   column="business_name"     />
        <result property="functionName"   column="function_name"     />
        <result property="functionAuthor" column="function_author"   />
        <result property="genType"        column="gen_type"          />
        <result property="genPath"        column="gen_path"          />
        <result property="options"        column="options"           />
        <result property="createBy"       column="create_by"         />
        <result property="createTime"     column="create_time"       />
        <result property="updateBy"       column="update_by"         />
        <result property="updateTime"     column="update_time"       />
        <result property="remark"         column="remark"            />
        <collection  property="columns"  javaType="java.util.List"  resultMap="GenTableColumnResult" />
    </resultMap>

    <resultMap type="GenTableColumn" id="GenTableColumnResult">
        <id     property="columnId"       column="column_id"      />
        <result property="tableId"        column="table_id"       />
        <result property="columnName"     column="column_name"    />
        <result property="columnComment"  column="column_comment" />
        <result property="columnType"     column="column_type"    />
        <result property="javaType"       column="java_type"      />
        <result property="javaField"      column="java_field"     />
        <result property="isPk"           column="is_pk"          />
        <result property="isIncrement"    column="is_increment"   />
        <result property="isRequired"     column="is_required"    />
        <result property="isInsert"       column="is_insert"      />
        <result property="isEdit"         column="is_edit"        />
        <result property="isList"         column="is_list"        />
        <result property="isQuery"        column="is_query"       />
        <result property="queryType"      column="query_type"     />
        <result property="htmlType"       column="html_type"      />
        <result property="dictType"       column="dict_type"      />
        <result property="sort"           column="sort"           />
        <result property="createBy"       column="create_by"      />
        <result property="createTime"     column="create_time"    />
        <result property="updateBy"       column="update_by"      />
        <result property="updateTime"     column="update_time"    />
    </resultMap>

    <sql id="selectGenTableVo">
        select table_id, table_name, table_comment, sub_table_name, sub_table_fk_name, class_name, tpl_category, package_name, module_name, business_name, function_name, function_author, gen_type, gen_path, options, create_by, create_time, update_by, update_time, remark from gen_table
    </sql>

    <select id="selectGenTableList" parameterType="GenTable" resultMap="GenTableResult">
        <include refid="selectGenTableVo"/>
        <where>
            <if test="tableName != null and tableName != ''">
                AND lower(table_name) like lower(concat('%', #{tableName}, '%'))
            </if>
            <if test="tableComment != null and tableComment != ''">
                AND lower(table_comment) like lower(concat('%', #{tableComment}, '%'))
            </if>
            <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
                AND CONVERT(varchar(100), create_time, 112) &gt;= CONVERT(varchar(100), #{params.beginTime}, 112)
            </if>
            <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
                AND CONVERT(varchar(100), create_time, 112) &lt;= CONVERT(varchar(100), #{params.endTime}, 112)
            </if>
        </where>
    </select>

    <select id="selectDbTableList" parameterType="GenTable" resultMap="GenTableResult">
        SELECT cast(D.NAME as nvarchar) as table_name,cast(F.VALUE as nvarchar) as table_comment,
        crdate as create_time,refdate as update_time FROM SYSOBJECTS   D
        inner JOIN SYS.EXTENDED_PROPERTIES F ON D.ID=F.MAJOR_ID
        AND F.MINOR_ID=0 AND D.XTYPE='U' AND D.NAME!='DTPROPERTIES'
        AND D.NAME NOT LIKE 'qrtz_%' AND D.NAME NOT LIKE 'gen_%'
        AND D.NAME NOT IN (select table_name from gen_table)
        <if test="tableName != null and tableName != ''">
            AND lower(CAST(D.NAME AS VARCHAR)) like lower(concat('%', #{tableName}, '%'))
        </if>
        <if test="tableComment != null and tableComment != ''">
            AND lower(CAST(F.value AS VARCHAR)) like lower(concat('%', #{tableComment}, '%'))
        </if>
        <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
            AND CONVERT(varchar(100), create_time, 112) &gt;= CONVERT(varchar(100), #{params.beginTime}, 112)
        </if>
        <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
            AND CONVERT(varchar(100), create_time, 112) &lt;= CONVERT(varchar(100), #{params.endTime}, 112)
        </if>
        order by create_time desc
    </select>

    <select id="selectDbTableListByNames" resultMap="GenTableResult">
        SELECT cast(D.NAME as nvarchar) as table_name,cast(F.VALUE as nvarchar) as table_comment,
        crdate as create_time,refdate as update_time FROM SYSOBJECTS D
        inner JOIN SYS.EXTENDED_PROPERTIES F ON D.ID=F.MAJOR_ID
        AND F.MINOR_ID=0 AND   D.XTYPE='U' AND D.NAME!='DTPROPERTIES'
        AND D.NAME NOT LIKE 'qrtz_%' AND D.NAME NOT LIKE 'gen_%'
        AND D.NAME in
        <foreach collection="array" item="name" open="(" separator="," close=")">
            #{name}
        </foreach>
    </select>

    <select id="selectTableByName" parameterType="String" resultMap="GenTableResult">
        select table_name, table_comment, create_time, update_time from information_schema.tables
        where table_comment <![CDATA[ <> ]]> '' and table_schema = (Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid))
          and table_name = #{tableName}
    </select>

    <select id="selectGenTableById" parameterType="Long" resultMap="GenTableResult">
        SELECT t.table_id, t.table_name, t.table_comment, t.sub_table_name, t.sub_table_fk_name, t.class_name, t.tpl_category, t.package_name, t.module_name, t.business_name, t.function_name, t.function_author, t.gen_type, t.gen_path, t.options, t.remark,
               c.column_id, c.column_name, c.column_comment, c.column_type, c.java_type, c.java_field, c.is_pk, c.is_increment, c.is_required, c.is_insert, c.is_edit, c.is_list, c.is_query, c.query_type, c.html_type, c.dict_type, c.sort
        FROM gen_table t
                 LEFT JOIN gen_table_column c ON t.table_id = c.table_id
        where t.table_id = #{tableId} order by c.sort
    </select>

    <select id="selectGenTableByName" parameterType="String" resultMap="GenTableResult">
        SELECT t.table_id, t.table_name, t.table_comment, t.sub_table_name, t.sub_table_fk_name, t.class_name, t.tpl_category, t.package_name, t.module_name, t.business_name, t.function_name, t.function_author, t.gen_type, t.gen_path, t.options, t.remark,
               c.column_id, c.column_name, c.column_comment, c.column_type, c.java_type, c.java_field, c.is_pk, c.is_increment, c.is_required, c.is_insert, c.is_edit, c.is_list, c.is_query, c.query_type, c.html_type, c.dict_type, c.sort
        FROM gen_table t
                 LEFT JOIN gen_table_column c ON t.table_id = c.table_id
        where t.table_name = #{tableName} order by c.sort
    </select>

    <select id="selectGenTableAll" parameterType="String" resultMap="GenTableResult">
        SELECT t.table_id, t.table_name, t.table_comment, t.sub_table_name, t.sub_table_fk_name, t.class_name, t.tpl_category, t.package_name, t.module_name, t.business_name, t.function_name, t.function_author, t.options, t.remark,
               c.column_id, c.column_name, c.column_comment, c.column_type, c.java_type, c.java_field, c.is_pk, c.is_increment, c.is_required, c.is_insert, c.is_edit, c.is_list, c.is_query, c.query_type, c.html_type, c.dict_type, c.sort
        FROM gen_table t
                 LEFT JOIN gen_table_column c ON t.table_id = c.table_id
        order by c.sort
    </select>

    <insert id="insertGenTable" parameterType="GenTable" useGeneratedKeys="true" keyProperty="tableId">
        insert into gen_table (
        <if test="tableName != null">table_name,</if>
        <if test="tableComment != null and tableComment != ''">table_comment,</if>
        <if test="className != null and className != ''">class_name,</if>
        <if test="tplCategory != null and tplCategory != ''">tpl_category,</if>
        <if test="packageName != null and packageName != ''">package_name,</if>
        <if test="moduleName != null and moduleName != ''">module_name,</if>
        <if test="businessName != null and businessName != ''">business_name,</if>
        <if test="functionName != null and functionName != ''">function_name,</if>
        <if test="functionAuthor != null and functionAuthor != ''">function_author,</if>
        <if test="genType != null and genType != ''">gen_type,</if>
        <if test="genPath != null and genPath != ''">gen_path,</if>
        <if test="remark != null and remark != ''">remark,</if>
        <if test="createBy != null and createBy != ''">create_by,</if>
        create_time
        )values(
        <if test="tableName != null">#{tableName},</if>
        <if test="tableComment != null and tableComment != ''">#{tableComment},</if>
        <if test="className != null and className != ''">#{className},</if>
        <if test="tplCategory != null and tplCategory != ''">#{tplCategory},</if>
        <if test="packageName != null and packageName != ''">#{packageName},</if>
        <if test="moduleName != null and moduleName != ''">#{moduleName},</if>
        <if test="businessName != null and businessName != ''">#{businessName},</if>
        <if test="functionName != null and functionName != ''">#{functionName},</if>
        <if test="functionAuthor != null and functionAuthor != ''">#{functionAuthor},</if>
        <if test="genType != null and genType != ''">#{genType},</if>
        <if test="genPath != null and genPath != ''">#{genPath},</if>
        <if test="remark != null and remark != ''">#{remark},</if>
        <if test="createBy != null and createBy != ''">#{createBy},</if>
        getdate()
        )
    </insert>

    <update id="updateGenTable" parameterType="GenTable">
        update gen_table
        <set>
            <if test="tableName != null">table_name = #{tableName},</if>
            <if test="tableComment != null and tableComment != ''">table_comment = #{tableComment},</if>
            <if test="subTableName != null">sub_table_name = #{subTableName},</if>
            <if test="subTableFkName != null">sub_table_fk_name = #{subTableFkName},</if>
            <if test="className != null and className != ''">class_name = #{className},</if>
            <if test="functionAuthor != null and functionAuthor != ''">function_author = #{functionAuthor},</if>
            <if test="genType != null and genType != ''">gen_type = #{genType},</if>
            <if test="genPath != null and genPath != ''">gen_path = #{genPath},</if>
            <if test="tplCategory != null and tplCategory != ''">tpl_category = #{tplCategory},</if>
            <if test="packageName != null and packageName != ''">package_name = #{packageName},</if>
            <if test="moduleName != null and moduleName != ''">module_name = #{moduleName},</if>
            <if test="businessName != null and businessName != ''">business_name = #{businessName},</if>
            <if test="functionName != null and functionName != ''">function_name = #{functionName},</if>
            <if test="options != null and options != ''">options = #{options},</if>
            <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
            <if test="remark != null">remark = #{remark},</if>
            update_time = getdate()
        </set>
        where table_id = #{tableId}
    </update>

    <delete id="deleteGenTableByIds" parameterType="Long">
        delete from gen_table where table_id in
        <foreach collection="array" item="tableId" open="(" separator="," close=")">
            #{tableId}
        </foreach>
    </delete>
</mapper>

2024.3.11更新:
若是代码生成后的sql语句还是有问题,那就没办法了只能改下sql语句,下面给个例子,按照这样仿照修改一下即可

-- 菜单 SQL
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('材质管理', '1069', '1', 'caizhi', 'system/caizhi/index', 1, 0, 'C', '0', '0', 'system:caizhi:list', '#', 'admin', getdate(), '', null, '材质管理菜单');

declare @parentId int;
-- 按钮父菜单ID
SELECT * INTO #TMP FROM (select parentId = @@IDENTITY) T ;
SELECT parentId FROM #TMP;

-- 按钮 SQL
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('材质管理查询', (SELECT parentId FROM #TMP), '1',  '#', '', 1, 0, 'F', '0', '0', 'system:caizhi:query',        '#', 'admin', getdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('材质管理新增', (SELECT parentId FROM #TMP), '2',  '#', '', 1, 0, 'F', '0', '0', 'system:caizhi:add',          '#', 'admin', getdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('材质管理修改', (SELECT parentId FROM #TMP), '3',  '#', '', 1, 0, 'F', '0', '0', 'system:caizhi:edit',         '#', 'admin', getdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('材质管理删除', (SELECT parentId FROM #TMP), '4',  '#', '', 1, 0, 'F', '0', '0', 'system:caizhi:remove',       '#', 'admin', getdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('材质管理导出', (SELECT parentId FROM #TMP), '5',  '#', '', 1, 0, 'F', '0', '0', 'system:caizhi:export',       '#', 'admin', getdate(), '', null, '');

2023.2.22补充 : SSMS里顺便把需要操作的表名字改一下
在这里插入图片描述

生成后的代码包含的sql文件内的sql语句也需要修改,比如说:

-- 按钮父菜单ID
SELECT @parentId := LAST_INSERT_ID();

需要改成

declare @parentId int;
-- 按钮父菜单ID
SELECT * INTO #TMP FROM (select parentId = @@IDENTITY) T ;
SELECT parentId FROM #TMP;

插入语句当中的@parentId

-- 按钮 SQL
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('商品查询', @parentId, '1',  '#', '', 1, 0, 'F', '0', '0', 'system:inventory:query','#', 'admin',  sysdate(), '', null, '');

需要改成(SELECT parentId FROM #TMP)
当然获取时间的函数 sysdate()也需要改成getdate()

-- 按钮 SQL
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('商品查询', (SELECT parentId FROM #TMP), '1',  '#', '', 1, 0, 'F', '0', '0', 'system:inventory:query',        '#', 'admin', getdate(), '', null, '');

删除import com.sun.xml.internal.bind.v2.TODO;
SysUserServiceImpl包下的import com.sun.xml.internal.bind.v2.TODO导包需要删除
在这里插入图片描述

PS: 自己改不好的话,就把整合版的下载下来对着抄吧.

最后在idea设置里把数据库方言设置一下

在这里插入图片描述
首先如果不是要迁移项目的话,从头开始写的话,我建议直接用这个别人整合好的版本:
https://gitee.com/MaShangYouLi/RuoYi-Vue-SQLServer-C
我自己整合好的版本我简单测了一下基本功能没问题,部门停用那里有点问题,网盘下载太慢了,就用这个小鸭子图吧

把图片下载下来,后缀jpg改成zip打开即可获得源码.目前这个版本代码生成是有问题的,把上面的关于代码生成需要修改的地方复制进来就可以了.

关于主从数据库

关于多数据的配置这里有别人的教程:https://blog.csdn.net/weixin_41084290/article/details/120013730
我自己整合了一个,可以移步https://gitee.com/zhang-zihao123/ruoyi下载
这里有的人可能不会切换数据源,其实很简单,只要在对应的mapper下加上@DataSource(value = DataSourceType.SLAVE)注解就行了,不用从数据源的mapper不用加,用再加.
2023.5.6更新:

  1. 在使用主从数据源的时候要小心注解@Transactional数据源切换的问题,这是个坑
  2. 分享一个若依快速搭建平台:http://fastbuild.run/ 可以一键搭建若依框架

另外记录几个注解(点击可查看详细解释):

@Transactional

数据库事务注解

@version

乐观锁注解
乐观锁要注意的一点是,如果在一个方法内有两个相同的类需要更新，我们在保证值的正确的情况下，可以主动给它+1，这样就能改变它的预期，解决乐观锁的问题。问题在此处

@Validated

有效性注解是一套帮助我们继续对传输的参数进行数据校验的注解，通过配置Validation可以很轻松的完成对数据的约束。
有效性注解作用在类、方法和参数上

分类限制说明
空和非空检查@Null限制只能为null

@NotNull限制必须不为null
@NotEmpty验证注解的元嘉值不为null目不为空 (字符串长度不为0、集合大小不为0)
@NotBlank验证注解的元素值不为空 (不为null、去除首位空格后长度为0)，不同于@NotEmpty@NotBlank只应用于字符串目在比较时会去除字符串的空格
Boolean值检查

@AssertFalse限制必须为False
@AssertTrue限制必须为True
长度检查@Size(max,min)限制字符长度必须在min到max之间
@Leanth限制字符长度必须在min到max之间
日期检查@Future限制日期为当前时间之后
@FutureOrPresent限制日期为当前时间或之后
@Past限制日期为当前时间之前
@PastOrPresent限制日期为当前时间或之前
数值检查@Max(Value)限制必须为一个不大于指定值的数字
@Min（Value）限制必须为一个不小于指定值的数字
@DecimalMin(value)限制必须为一个不小于指定值的数字
@DecimalMax(value)限制必须为一个不小于指定值的数字
@Digits(integer,fraction)限制必须为小数，且整数部分的位数不能超过Integer，小数部分的位数不能超过fraction
@Negative限制必须为负整数
@NegativeOrZero(value)限制必须为负整数或零
@Positive(value)限制必须为正整数
@PositiveOrZero(value)限制必须为正整数或零
其他检查@Pattern(Value)限制必须符合指定的正则表达式
@Email限制必须为email格式
[表格制作网站](https://www.tablesgenerator.com/html_tables#)
z子豪
关注
  • 68
    点赞
  • 踩
  • 51
    收藏
    觉得还不错? 一键收藏
  • 21
    评论
若依前后端分离集成多数据源SQL Server教程
猿小白的博客
01-31 829
本章教程，主要介绍如何在若依开源框架前后端分离版本中集成多数据源SQL Server教程。
切换数据库
04-25
在内网环境下，实现在某一个数据库中，操作其它数据库的数据或函数或其它。。。。
若依分离版本+SqlServer版本数据库
04-29
1、若依分离版本代码 2、数据由mysql变更为SqlServer 3、包含SqlServer数据库备份 4、下载用idea直接打开 5、启动方式和若依本身的启动顺序一样
Spring+Hibernate下的数据库连接动态切换
03-21
Spring+Hibernate下的数据库连接动态切换
RuoYi-Vue-Plus (多数据源注解使用、【手动、拦截器】切换数据源)
javaxueba的博客
07-26 985
[PLUS]结束请求 => URL[GET /testDynamic2],耗时:[33]毫秒。- [PLUS]结束请求 => URL[GET /testDynamic2],耗时:[31]毫秒。DynamicDataSourceContextHolder 工具类。切换数据源：master----------------------切换数据源：slave----------------------切换数据源：slave----------------------已经设置数据源，打印当前数据源：slave。
ruoyi（若依）系统使用SqlServer数据库
热门推荐
op4439的博客
11-22 1万+
ruoyi（若依）系统使用SqlServer数据库
【若依】框架多数据源切换
m0_67245030的博客
04-25 1832
文章讲解了实现若依框架多数据源之间切换
若依项目改造-sqlserver
最新发布
weixin_38076674的博客
08-12 208
sqlserver springboot vue 自定义配置数据库配置，无需登录
ruoyi-flowable-plus 若依切换SqlServer数据库
qq_40796482的博客
09-26 724
ruoyi-flowable-plus 使用sqlserver
若依系统不分离版框架Mysql更换SqlServer
kay523393的博客
07-30 3156
若依系统不分离版框架Mysql更换SqlServer需要修改的地方
若依ruoyi 动态多数据源配置（多种不同类型的数据库mysql，oracle，sqlite3，sqlserver等等）
shushengcoder的博客
04-08 2275
【代码】若依ruoyi 动态多数据源配置（多种不同类型的数据库mysql，oracle，sqlite3，sqlserver等等）
SQL Server无日志恢复数据库(2种方法)
09-10
在SQL Server中，数据库日志文件（.ldf）对于保持数据的一致性和完整性至关重要，因为它记录了所有事务的详细信息。当日志文件丢失或损坏时，可能会导致数据库不可用，甚至出现“置疑”的状态。在这种情况下，恢复...
SQLServer 数据库变成单个用户后无法访问问题的解决方法
12-15
在SQL Server中，数据库可以被设置为不同的访问模式，包括单用户模式（SINGLE_USER）和多用户模式（MULTI_USER）。单用户模式允许仅有一个连接可以访问数据库，这通常在进行备份、恢复或者维护操作时使用。然而，...
springboot多数据源切换mysql+sql server事例
04-28
本示例“springboot多数据源切换mysql+sql server”是关于如何在Spring Boot项目中配置和使用多个数据库的数据源切换。下面将详细阐述这个主题的知识点。 首先，**多数据源**是指在一个应用中同时连接并操作多个...
sqlserver replace函数 批量替换数据库中指定字段内指定字符串参考方法
12-15
语法 REPLACE ( ‘string_e­xpression1’ , ‘string_e­xpression2’ , ‘string_e­xpression3’ ) 参数说明 ‘string_e­xpression1’ 待搜索的字符串表达式。string_e­xpression1 可以是字符数据或二进制数据...
ruoyi-vue集成多数据源(sqlserver)
houoyy_的博客
05-11 861
ruoyi-vue集成多数据源(sqlserver)
若依分离版SQL Server开发和使用教程
yqj234的专栏
12-24 1849
若依MS SQL Server版开发教程
Sqlserver 用户模式切换
小小程序猿
04-27 2252
USE master; Go  DECLARE @SQL VARCHAR(MAX);  SET @SQL=''  SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)  FROM master..sysprocesses  WHERE dbid=DB_ID('0051'); EXEC(@SQL); GO  ALTER DATABASE [0051] SET
若依框架（前后端分离）——多数据源配置详细教程
weixin_45715650的博客
04-01 3067
planScheduleService是类名,selectMoInfo()则是在后面定义的。这样在前端发送请求，便可以获取成功了，以上便是配置数据源的具体流程例子。文件路径图片：配置处1——注意：框住了的即为需要注意并修改的地方。impl文件内的类配置，注意。文件路径图片：配置处2。
sqlserver命令 切换到某一个数据库
06-11
要切换到 SQL Server 中的某个数据库，可以使用以下命令： ``` USE <database_name> ``` 其中，`<database_name>` 是你要切换到的数据库名称。例如，如果要切换到名为 `mydatabase` 的数据库，可以执行以下命令： ``` USE mydatabase ``` 执行该命令后，SQL Server 将当前数据库切换为 `mydatabase`。
评论 21
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值