这么幸苦维护的一篇文章却没有人点赞,我还不如删了
接下来一步一步操作:
第一步:
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') <= 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) >= CONVERT(varchar(100), #{params.beginTime}, 112)
</if>
<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
AND CONVERT(varchar(100), create_time, 112) <= 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) >= CONVERT(varchar(100), #{params.beginTime}, 112)
</if>
<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
AND CONVERT(varchar(100), create_time, 112) <= 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更新:
- 在使用主从数据源的时候要小心注解@Transactional数据源切换的问题,这是个坑
- 分享一个若依快速搭建平台: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格式 |