SqlServer教程

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

本文主要是介绍若依前后端分离版切换数据库方法,以切换sqlServer为例[超详细],对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

接下来一步一步操作:

第一步:

trace-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-commonpom.xml模块添加整合依赖

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

配置文件里修改MyBatis配置,改为mybatis-plus:

mybatis-plus:
  # 搜索指定包别名
  typeAliasesPackage: com.trace.**.domain
  # 配置mapper的扫描,找到所有的mapper.xml映射文件
  mapperLocations: classpath*:mapper/**/*Mapper.xml
  # 加载全局的配置文件
  configLocation: classpath:mybatis/mybatis-config.xml

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);
        // 设置最大单页限制数量,默认 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>

这里需要改好几个文件,但是有的不改其实关系也不大.(用不上就不报错呗) 比如sysdate()要改成getdate() if null要改成isnull date_format要改成datediff (注意这里datediff函数是有三个参数的,所以第一个参数要填dd)

这里是个坑,比如原来的语句是:
and datediff(dd,oper_time,%y%m%d) &lt;= datediff(dd,#{
          
   params.endTime},%y%m%d)
那么要改成:
and datediff(d, oper_time, #{
          
   params.endTime}) <![CDATA[>=]]> 0

FIND_IN_SET 函数需要改成CHARINDEX

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

最新发现,代码生成功能有问题,需要改动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>

生成后的代码包含的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, getdate(), , null, );

需要改成(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:inventory:query,        #, admin, getdate(), , null, );

当然获取时间的函数也需要改成getdate() PS: 自己该不好的话,就把整合版的下载下来对着抄吧.

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

首先如果不是要迁移项目的话,从头开始写的话,我建议直接用这个别人整合好的版本: 我自己整合好的版本我简单测了一下基本功能没问题,部门停用那里有点问题,网盘下载太慢了,就用这个小鸭子图吧 把图片下载下来,后缀jpg改成zip打开即可获得源码.目前这个版本代码生成是有问题的,把上面的关于代码生成需要修改的地方复制进来就可以了.

关于主从数据库

关于多数据的配置这里有别人的教程: 我自己整合了一个,可以移步下载 这里有的人可能不会切换数据源,其实很简单,只要在对应的mapper下加上@DataSource(value = DataSourceType.SLAVE)注解就行了,不用从数据源的mapper不用加,用再加.

另外记录几个注解:

@Transactional

添加事务的注解 详见

@version

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

这篇关于若依前后端分离版切换数据库方法,以切换sqlServer为例[超详细]的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!