100 主子表代码生成详解

1 新建数据库表结构（主子表）

-- ----------------------------
-- 客户表
-- ----------------------------
drop table if exists sys_customer;
create table sys_customer (customer_id           bigint(20)      not null auto_increment    comment '客户id',customer_name         varchar(30)     default ''                 comment '客户姓名',phonenumber           varchar(11)     default ''                 comment '手机号码',sex                   varchar(20)     default null               comment '客户性别',birthday              datetime                                   comment '客户生日',remark                varchar(500)    default null               comment '客户描述',primary key (customer_id)
) engine=innodb auto_increment=1 comment = '客户表';-- ----------------------------
-- 商品表
-- ----------------------------
drop table if exists sys_goods;
create table sys_goods (goods_id           bigint(20)      not null auto_increment    comment '商品id',-- 必须要有的关联字段（外键）customer_id        bigint(20)      not null                   comment '客户id',name               varchar(30)     default ''                 comment '商品名称',weight             int(5)          default null               comment '商品重量',price              decimal(6,2)    default null               comment '商品价格',date               datetime                                   comment '商品时间',type               char(1)         default null               comment '商品种类',primary key (goods_id)
) engine=innodb auto_increment=1 comment = '商品表';

2 代码生成使用

（1）登录系统（系统工具 -> 代码生成 -> 导入主表与子表）

（2）代码生成列表中找到需要表（可预览、编辑、同步、删除生成配置）

（3）点击生成代码会得到一个ruoyi.zip执行sql文件，按照包内目录结构复制到自己的项目中即可

（4）执行customerMenu.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('客户', '3', '1', 'customer', 'system/customer/index', 1, 0, 'C', '0', '0', 'system:customer:list', '#', 'admin', sysdate(), '', null, '客户菜单');-- 按钮父菜单ID
SELECT @parentId := LAST_INSERT_ID();-- 按钮 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:customer:query',        '#', 'admin', sysdate(), '', 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('客户新增', @parentId, '2',  '#', '', 1, 0, 'F', '0', '0', 'system:customer:add',          '#', 'admin', sysdate(), '', 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('客户修改', @parentId, '3',  '#', '', 1, 0, 'F', '0', '0', 'system:customer:edit',         '#', 'admin', sysdate(), '', 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('客户删除', @parentId, '4',  '#', '', 1, 0, 'F', '0', '0', 'system:customer:remove',       '#', 'admin', sysdate(), '', 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('客户导出', @parentId, '5',  '#', '', 1, 0, 'F', '0', '0', 'system:customer:export',       '#', 'admin', sysdate(), '', null, '');

（5）覆盖前端代码：主要是里面的模板有不同

（6）覆盖后端代码：主要是里面的模板有不同

  

（7）测试

1. F5刷新ruoyi-system
2. 重启前后端
3. 测试成功
   

3 后台（区别）详解

（1）SysCustomer：客户

    /** * 关联关系 商品信息 集合。* 后续：前端会把商品信息传进来。* */private List<SysGoods> sysGoodsList;

（2）SysGoods：商品。就跟单表一样

（3）SysCustomerServiceImpl：大区别

package com.ruoyi.system.service.impl;import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import com.ruoyi.common.utils.StringUtils;
import org.springframework.transaction.annotation.Transactional;
import com.ruoyi.system.domain.SysGoods;
import com.ruoyi.system.mapper.SysCustomerMapper;
import com.ruoyi.system.domain.SysCustomer;
import com.ruoyi.system.service.ISysCustomerService;/*** 客户Service业务层处理* * @author ruoyi* @date 2023-08-09*/
@Service
public class SysCustomerServiceImpl implements ISysCustomerService 
{@Autowiredprivate SysCustomerMapper sysCustomerMapper;/*** 查询客户* * 区别：XxxMapper.xml中会是一个关联查询*/@Overridepublic SysCustomer selectSysCustomerByCustomerId(Long customerId){return sysCustomerMapper.selectSysCustomerByCustomerId(customerId);}/*** 查询客户列表** 区别：XxxMapper.xml中会是一个关联查询*/@Overridepublic List<SysCustomer> selectSysCustomerList(SysCustomer sysCustomer){return sysCustomerMapper.selectSysCustomerList(sysCustomer);}/*** 新增客户** 区别：新增对应的商品信息（列表）*/@Transactional@Overridepublic int insertSysCustomer(SysCustomer sysCustomer){int rows = sysCustomerMapper.insertSysCustomer(sysCustomer);insertSysGoods(sysCustomer);return rows;}/*** 修改客户*/@Transactional@Overridepublic int updateSysCustomer(SysCustomer sysCustomer){/*** 删除子表中的全部数据*/sysCustomerMapper.deleteSysGoodsByCustomerId(sysCustomer.getCustomerId());/*** 子表数据入库*/insertSysGoods(sysCustomer);/*** 主表信息修改入库*/return sysCustomerMapper.updateSysCustomer(sysCustomer);}/*** 批量删除客户*/@Transactional@Overridepublic int deleteSysCustomerByCustomerIds(Long[] customerIds){/*** 删除子表数据*/sysCustomerMapper.deleteSysGoodsByCustomerIds(customerIds);/*** 删除主表数据*/return sysCustomerMapper.deleteSysCustomerByCustomerIds(customerIds);}/*** 删除客户信息*/@Transactional@Overridepublic int deleteSysCustomerByCustomerId(Long customerId){/*** 删除子表数据*/sysCustomerMapper.deleteSysGoodsByCustomerId(customerId);/*** 删除主表数据*/return sysCustomerMapper.deleteSysCustomerByCustomerId(customerId);}/*** 新增商品信息* 即从客户中获取对应的商品信息列表，然后入库*/public void insertSysGoods(SysCustomer sysCustomer){List<SysGoods> sysGoodsList = sysCustomer.getSysGoodsList();Long customerId = sysCustomer.getCustomerId();if (StringUtils.isNotNull(sysGoodsList)){List<SysGoods> list = new ArrayList<SysGoods>();for (SysGoods sysGoods : sysGoodsList){sysGoods.setCustomerId(customerId);list.add(sysGoods);}if (list.size() > 0){sysCustomerMapper.batchSysGoods(list);}}}
}

（4）SysCustomerMapper.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.system.mapper.SysCustomerMapper"><resultMap type="SysCustomer" id="SysCustomerResult"><result property="customerId"    column="customer_id"    /><result property="customerName"    column="customer_name"    /><result property="phonenumber"    column="phonenumber"    /><result property="sex"    column="sex"    /><result property="birthday"    column="birthday"    /><result property="remark"    column="remark"    /></resultMap><resultMap id="SysCustomerSysGoodsResult" type="SysCustomer" extends="SysCustomerResult"><collection property="sysGoodsList" notNullColumn="sub_goods_id" javaType="java.util.List" resultMap="SysGoodsResult" /></resultMap><resultMap type="SysGoods" id="SysGoodsResult"><result property="goodsId"    column="sub_goods_id"    /><result property="customerId"    column="sub_customer_id"    /><result property="name"    column="sub_name"    /><result property="weight"    column="sub_weight"    /><result property="price"    column="sub_price"    /><result property="date"    column="sub_date"    /><result property="type"    column="sub_type"    /></resultMap><sql id="selectSysCustomerVo">select customer_id, customer_name, phonenumber, sex, birthday, remark from sys_customer</sql><select id="selectSysCustomerList" parameterType="SysCustomer" resultMap="SysCustomerResult"><include refid="selectSysCustomerVo"/><where>  <if test="customerName != null  and customerName != ''"> and customer_name like concat('%', #{customerName}, '%')</if><if test="phonenumber != null  and phonenumber != ''"> and phonenumber = #{phonenumber}</if><if test="sex != null  and sex != ''"> and sex = #{sex}</if><if test="birthday != null "> and birthday = #{birthday}</if></where></select><!--区别：关联查询--><select id="selectSysCustomerByCustomerId" parameterType="Long" resultMap="SysCustomerSysGoodsResult">select a.customer_id, a.customer_name, a.phonenumber, a.sex, a.birthday, a.remark,b.goods_id as sub_goods_id, b.customer_id as sub_customer_id, b.name as sub_name, b.weight as sub_weight, b.price as sub_price, b.date as sub_date, b.type as sub_typefrom sys_customer aleft join sys_goods b on b.customer_id = a.customer_idwhere a.customer_id = #{customerId}</select><insert id="insertSysCustomer" parameterType="SysCustomer" useGeneratedKeys="true" keyProperty="customerId">insert into sys_customer<trim prefix="(" suffix=")" suffixOverrides=","><if test="customerName != null">customer_name,</if><if test="phonenumber != null">phonenumber,</if><if test="sex != null">sex,</if><if test="birthday != null">birthday,</if><if test="remark != null">remark,</if></trim><trim prefix="values (" suffix=")" suffixOverrides=","><if test="customerName != null">#{customerName},</if><if test="phonenumber != null">#{phonenumber},</if><if test="sex != null">#{sex},</if><if test="birthday != null">#{birthday},</if><if test="remark != null">#{remark},</if></trim></insert><update id="updateSysCustomer" parameterType="SysCustomer">update sys_customer<trim prefix="SET" suffixOverrides=","><if test="customerName != null">customer_name = #{customerName},</if><if test="phonenumber != null">phonenumber = #{phonenumber},</if><if test="sex != null">sex = #{sex},</if><if test="birthday != null">birthday = #{birthday},</if><if test="remark != null">remark = #{remark},</if></trim>where customer_id = #{customerId}</update><delete id="deleteSysCustomerByCustomerId" parameterType="Long">delete from sys_customer where customer_id = #{customerId}</delete><delete id="deleteSysCustomerByCustomerIds" parameterType="String">delete from sys_customer where customer_id in <foreach item="customerId" collection="array" open="(" separator="," close=")">#{customerId}</foreach></delete><delete id="deleteSysGoodsByCustomerIds" parameterType="String">delete from sys_goods where customer_id in <foreach item="customerId" collection="array" open="(" separator="," close=")">#{customerId}</foreach></delete><delete id="deleteSysGoodsByCustomerId" parameterType="Long">delete from sys_goods where customer_id = #{customerId}</delete><insert id="batchSysGoods">insert into sys_goods( goods_id, customer_id, name, weight, price, date, type) values<foreach item="item" index="index" collection="list" separator=",">( #{item.goodsId}, #{item.customerId}, #{item.name}, #{item.weight}, #{item.price}, #{item.date}, #{item.type})</foreach></insert>
</mapper>

4 前端（区别）详解

（1）ruoyi-ui\src\views\system\customer\index.vue

<template><!--1、指定了商品信息（列表）2、选择事件：@selection-change="handleSysGoodsSelectionChange"--><el-table :data="sysGoodsList" :row-class-name="rowSysGoodsIndex" @selection-change="handleSysGoodsSelectionChange" ref="sysGoods"><el-table-column type="selection" width="50" align="center" /><el-table-column label="序号" align="center" prop="index" width="50"/><el-table-column label="商品名称" prop="name" width="150"><template slot-scope="scope"><el-input v-model="scope.row.name" placeholder="请输入商品名称" /></template></el-table-column><el-table-column label="商品重量" prop="weight" width="150"><template slot-scope="scope"><el-input v-model="scope.row.weight" placeholder="请输入商品重量" /></template></el-table-column><el-table-column label="商品价格" prop="price" width="150"><template slot-scope="scope"><el-input v-model="scope.row.price" placeholder="请输入商品价格" /></template></el-table-column><el-table-column label="商品时间" prop="date" width="240"><template slot-scope="scope"><el-date-picker clearable v-model="scope.row.date" type="date" value-format="yyyy-MM-dd" placeholder="请选择商品时间" /></template></el-table-column><el-table-column label="商品种类" prop="type" width="150"><template slot-scope="scope"><el-select v-model="scope.row.type" placeholder="请选择商品种类"><el-option label="请选择字典生成" value="" /></el-select></template></el-table-column></el-table>
</template>
<script>
export default {name: "Customer",data() {// 商品表格数据，初始化为空sysGoodsList: [],},methods:{/** 修改按钮操作 */handleUpdate(row) {this.reset();const customerId = row.customerId || this.idsgetCustomer(customerId).then(response => {this.form = response.data;/*** 从客户中获取商品集合（列表）*/this.sysGoodsList = response.data.sysGoodsList;this.open = true;this.title = "修改客户";});},/** 提交按钮 */submitForm() {this.$refs["form"].validate(valid => {if (valid) {/*** 提交时把商品赋值到对应的客户中*/this.form.sysGoodsList = this.sysGoodsList;if (this.form.customerId != null) {updateCustomer(this.form).then(response => {this.$modal.msgSuccess("修改成功");this.open = false;this.getList();});} else {addCustomer(this.form).then(response => {this.$modal.msgSuccess("新增成功");this.open = false;this.getList();});}}});},/** 商品添加按钮操作 */handleAddSysGoods() {let obj = {};obj.name = "";obj.weight = "";obj.price = "";obj.date = "";obj.type = "";this.sysGoodsList.push(obj);},}
}
</script>

101 【更】3.4.0版本更新介绍

102 使用undertow容器来替代tomcat容器

1 undertow与tomcat的区别与联系

1. springboot默认使用tomcat做为http容器。
2. undertow与tomcat是一样的。
3. undertow在处理高并发请求、对内存的优化要好于tomcat。

2 替换过程详解

（1）ruoyi-framework\pom.xml模块修改web容器依赖，使用undertow来替代tomcat容器

        <!-- SpringBoot Web容器 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId><exclusions><!-- 排除springboot默认使用的tomcat容器 --><exclusion><artifactId>spring-boot-starter-tomcat</artifactId><groupId>org.springframework.boot</groupId></exclusion></exclusions></dependency><!-- web 容器使用 undertow 替换 tomcat --><!-- undertow的版本默认跟随springboot的版本--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-undertow</artifactId></dependency>

（2）修改application.yml，使用undertow来替代tomcat容器

# 开发环境配置
server:# 服务器的HTTP端口，默认为80port: 80servlet:# 应用的访问路径context-path: /# undertow 配置（可以参考官网，设置更多的属性）undertow:# HTTP post内容的最大大小。当值为-1时，默认值为大小是无限的max-http-post-size: -1# 以下的配置会影响buffer,这些buffer会用于服务器连接的IO操作,有点类似netty的池化内存管理# 每块buffer的空间大小,越小的空间被利用越充分buffer-size: 512# 是否分配的直接内存direct-buffers: truethreads:# 设置IO线程数, 它主要执行非阻塞的任务,它们会负责多个连接, 默认设置每个CPU核心一个线程io: 8# 阻塞任务线程池, 当执行类似servlet请求阻塞操作, undertow会从这个线程池中取得线程,它的值设置取决于系统的负载worker: 256

（3）修改文件上传工具类FileUploadUtils#getAbsoluteFile

        使用undertow容器以后上传文件可能会报错，这是因为undertow和tomcat的底层实现是不一样的，因此undertow是不需要去创建的：

private static final File getAbsoluteFile(String uploadDir, String fileName) throws IOException
{File desc = new File(uploadDir + File.separator + fileName);if (!desc.getParentFile().exists()){desc.getParentFile().mkdirs();}// undertow文件上传，因底层实现不同,无需创建新文件// if (!desc.exists())// {//    desc.createNewFile();// }return desc;
}

（3）测试

1. 重启后端
2. 简单测试几个功能：文件上传

3 拓展：模拟高并发的场景

103 实现优雅关闭应用（集成springboot自带的监控工具actuator）

1 为什么要优雅关闭呢？

       比如每一秒有上百笔的并发请求到订单接口，如果直接停掉应用，那这上百笔订单的线程没执行完导致这些订单就丢失了，这种情况是很严重的。所以一定要等到这些订单线程全部执行完之后，再去停掉应用，才能防止一些正在执行或者在执行的过程中的线程被强制停掉。

2 详细步骤

（1）ruoyi-admin/pom.xml中引入actuator依赖

<!-- 依赖springboot自带的监控工具actuator --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-actuator</artifactId></dependency>

（2）ruoyi-admin/application.yml配置文件中endpoint开启shutdown

# 可监控：内存、线程、日志
management:endpoint:shutdown:# shutdown打开，即允许使用它去停止应用enabled: trueendpoints:web:exposure:# 优雅关闭# include其实我们可以配置成" * "，但不建议这么写，因为很多东西都暴露出去对就项目来说是有风险的include: "shutdown"# 前缀路径base-path: /monitor

（3）SecurityConfig#configure()：因为在没有登录的情况下就停止了，所以需要配置白名单。

.antMatchers(HttpMethod.GET, "/", "/*.html", "/**/*.html", "/**/*.css", "/**/*.js", "/profile/**").permitAll()
/**
* 1、其中" /monitor/ "对应base-path: /monitor，shutdown对应include: "shutdown"
*/
.antMatchers("/monitor/shutdown").permitAll()
.antMatchers("/swagger-ui.html", "/swagger-resources/**", "/webjars/**", "/*/api-docs", "/druid/**").permitAll()

（4）Post请求测试验证优雅停机 curl -X POST http://localhost:80/monitor/shutdown

1. 重启后端

2. 这个接口是自带的，所以需要按照它的规则去请求。是POST请求，可以用postman工具发送post请求：

104 实现swagger文档增强（集成knife4j）

1 为什么要增强？

1. swagger界面风格、英文
2. knife4j对比swagger相比有以下优势，友好界面，离线文档，接口排序，安全控制，在线调试，文档清晰，后端注解增强，容易上手。

2 前端ui增强，详细步骤

（1）ruoyi-admin\pom.xml模块添加整合依赖（替换掉swagger的ui）

1. 针对不分离版 
   1. 把swagger的ui删除掉后：
   2. 替换成knife4j的ui：

      <!-- knife4j -->
      <dependency><groupId>com.github.xiaoymin</groupId><artifactId>knife4j-spring-boot-starter</artifactId><version>3.0.3</version>
      </dependency>

2. 针对分离版本
   1. 直接在ruoyi-admin\pom.xml加入knife4j的依赖即可

（2）修改首页跳转访问地址

        针对不分离版本：SwaggerController.java修改跳转访问地址" /doc.html "

// 默认swagger-ui.html前端ui访问地址
public String index()
{return redirect("/swagger-ui.html");
}
// 修改成knife4j前端ui访问地址doc.html
public String index()
{return redirect("/doc.html");
}

        针对前后端分离版本：ruoyi-ui\src\views\tool\swagger\index.vue修改跳转访问地址为" /doc.html "：

（3）测试

1. 重启前后端

2. 浏览器访问：http://localhost:8080/doc.html#/home

3. 登录系统，访问菜单系统工具/系统接口，出现如下图表示成功

（4）提示：引用knife4j-spring-boot-starter依赖，项目中的swagger依赖可以删除。

3 使用案例

4 拓展：后端注解增强

105 实现excel表格增强（集成easyexcel） 

1 excel表格实现1：自定义的@Excel、@Excels注解

2 excel表格实现2：easyExcel，使用简单、功能多、性能好、阿里开源

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

<!-- easyexcel -->
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version>
</dependency>

（2）自定义的ExcelUtil.java中easyexcel新增导出导入方法

/*** 对excel表单默认第一个索引名转换成list（EasyExcel）。** 这个两个方法是作者自己加的，当然开发人员还可以加更多其它的方法。** 兼容我们以前的那种写法：* （1）入参：流* （2）head(clazz)：兼容实体类* （3）最后调用api*/public List<T> importEasyExcel(InputStream is) throws Exception{return EasyExcel.read(is).head(clazz).sheet().doReadSync();}/*** 对list数据源将其里面的数据导入到excel表单（EasyExcel）。** 这个两个方法是作者自己加的，当然开发人员还可以加更多其它的方法。** 兼容我们以前的那种写法：* （1）从List<T> list中读取数据* （2）String sheetName：文件名* （3）把数据写入该文件* （4）前端根据对应的地址下载该文件*/public void exportEasyExcel(HttpServletResponse response, List<T> list, String sheetName){try{EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(list);}catch (IOException e){log.error("导出EasyExcel异常{}", e.getMessage());}}

（3）模拟测试，以操作日志为例，修改相关类。

（4）SysOperlogController.java改为exportEasyExcel

       导入和导出的方法加好之后，想在项目里面去使用easyExcel，其实以前你用怎么还是怎么用。那个默认的、自定义的、注解的导入导出方法，就现在直接改一下名字（使用带有" Easy "字样的方法）就行了。 

@Log(title = "操作日志", businessType = BusinessType.EXPORT)
@PreAuthorize("@ss.hasPermi('monitor:operlog:export')")
@PostMapping("/export")
public void export(HttpServletResponse response, SysOperLog operLog)
{List<SysOperLog> list = operLogService.selectOperLogList(operLog);ExcelUtil<SysOperLog> util = new ExcelUtil<SysOperLog>(SysOperLog.class);util.exportEasyExcel(response, list, "操作日志");
}

（5）SysOperLog.java修改为使用easyExcel的@ExcelProperty注解（不再使用自定义的注解）

package com.ruoyi.system.domain;/*** 操作日志记录表 oper_log** @author ruoyi*/
@ExcelIgnoreUnannotated //只有加了@ExcelProperty注解的才导出
@ColumnWidth(16)        //宽度
@HeadRowHeight(14)      //高度
@HeadFontStyle(fontHeightInPoints = 11)//样式
public class SysOperLog extends BaseEntity
{private static final long serialVersionUID = 1L;/** 日志主键 */@ExcelProperty(value = "操作序号")private Long operId;/** 操作模块 */@ExcelProperty(value = "操作模块")private String title;/** 业务类型（0其它 1新增 2修改 3删除） */@ExcelProperty(value = "业务类型", converter = BusiTypeStringNumberConverter.class)private Integer businessType;/** 业务类型数组 */private Integer[] businessTypes;/** 请求方法 */@ExcelProperty(value = "请求方法")private String method;/** 请求方式 */@ExcelProperty(value = "请求方式")private String requestMethod;/** 操作类别（0其它 1后台用户 2手机端用户） *//*** converter = OperTypeConverter.class：* （1）用于转换。如状态、性别、操作类型等等*/@ExcelProperty(value = "操作类别", converter = OperTypeConverter.class)private Integer operatorType;/** 操作人员 */@ExcelProperty(value = "操作人员")private String operName;/** 部门名称 */@ExcelProperty(value = "部门名称")private String deptName;/** 请求url */@ExcelProperty(value = "请求地址")private String operUrl;/** 操作地址 */@ExcelProperty(value = "操作地址")private String operIp;/** 操作地点 */@ExcelProperty(value = "操作地点")private String operLocation;/** 请求参数 */@ExcelProperty(value = "请求参数")private String operParam;/** 返回参数 */@ExcelProperty(value = "返回参数")private String jsonResult;/** 操作状态（0正常 1异常） */@ExcelProperty(value = "状态", converter = StatusConverter.class)private Integer status;/** 错误消息 */@ExcelProperty(value = "错误消息")private String errorMsg;/** 操作时间 *//*** @DateTimeFormat：easyExcel自带的时间转换注解。常用的，还有字符转换注解。*/@DateTimeFormat("yyyy-MM-dd HH:mm:ss")@ExcelProperty(value = "操作时间")private Date operTime;// get / set()// toString()
}

（6）ruoyi-system\com\ruoyi\system\domain\read\BusiTypeStringNumberConverter.java：添加字符串翻译内容

这个里边我现在是直接写死的，当然你们其实可以根据缓存里面的字典去查。 

package com.ruoyi.system.domain.read;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;/*** 业务类型字符串处理** Converter<Integer>中的整型、对应return Integer.class;，对应实体类中的属性的数据类型*/
@SuppressWarnings("rawtypes")
public class BusiTypeStringNumberConverter implements Converter<Integer>
{@Overridepublic Class supportJavaTypeKey(){return Integer.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey(){return CellDataTypeEnum.STRING;}@Overridepublic Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration){Integer value = 0;String str = cellData.getStringValue();if ("新增".equals(str)){value = 1;}else if ("修改".equals(str)){value = 2;}else if ("删除".equals(str)){value = 3;}else if ("授权".equals(str)){value = 4;}else if ("导出".equals(str)){value = 5;}else if ("导入".equals(str)){value = 6;}else if ("强退".equals(str)){value = 7;}else if ("生成代码".equals(str)){value = 8;}else if ("清空数据".equals(str)){value = 9;}return value;}@Overridepublic CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration){String str = "其他";if (1 == value){str = "新增";}else if (2 == value){str = "修改";}else if (3 == value){str = "删除";}else if (4 == value){str = "授权";}else if (5 == value){str = "导出";}else if (6 == value){str = "导入";}else if (7 == value){str = "强退";}else if (8 == value){str = "生成代码";}else if (9 == value){str = "清空数据";}return new CellData(str);}
}

（7）ruoyi-system\com\ruoyi\system\domain\read\OperTypeConverter.java

package com.ruoyi.system.domain.read;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;/*** 操作类别字符串处理** @author ruoyi*/
@SuppressWarnings("rawtypes")
public class OperTypeConverter implements Converter<Integer>
{@Overridepublic Class supportJavaTypeKey(){return Integer.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey(){return CellDataTypeEnum.STRING;}@Overridepublic Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration){Integer value = 0;String str = cellData.getStringValue();if ("后台用户".equals(str)){value = 1;}else if ("手机端用户".equals(str)){value = 2;}return value;}@Overridepublic CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration){String str = "其他";if (1 == value){str = "后台用户";}else if (2 == value){str = "手机端用户";}return new CellData(str);}
}

（8）ruoyi-system\com\ruoyi\system\domain\read\StatusConverter.java

package com.ruoyi.system.domain.read;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;/*** 状态字符串处理** @author ruoyi*/
@SuppressWarnings("rawtypes")
public class StatusConverter implements Converter<Integer>
{@Overridepublic Class supportJavaTypeKey(){return Integer.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey(){return CellDataTypeEnum.STRING;}@Overridepublic CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration){return new CellData(0 == value ? "正常" : "异常");}@Overridepublic Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws Exception{return "正常".equals(cellData.getStringValue()) ? 0 : 1;}
}

（9）登录系统，进入系统管理-日志管理-操作日志-执行导出功能

106 实现mybatis增强（集成mybatis-plus）

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

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

2 ruoyi-admin文件application.yml，修改mybatis配置为mybatis-plus （其实就只改了名称而已，配置参数一模一样的）

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

3 ruoyi-framework\src\main\java\com\ruoyi\framework\config\MybatisPlusConfig.java：添加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
{@Beanpublic 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();// 设置数据库类型为mysqlpaginationInnerInterceptor.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();}
}

4 添加测试表和菜单信息

drop table if exists sys_student;
create table sys_student (student_id           int(11)         auto_increment    comment '编号',student_name         varchar(30)     default ''        comment '学生名称',student_age          int(3)          default null      comment '年龄',student_hobby        varchar(30)     default ''        comment '爱好（0代码 1音乐 2电影）',student_sex          char(1)         default '0'       comment '性别（0男 1女 2未知）',student_status       char(1)         default '0'       comment '状态（0正常 1停用）',student_birthday     datetime                          comment '生日',primary key (student_id)
) engine=innodb auto_increment=1 comment = '学生信息表';-- 菜单 sql
insert into sys_menu (menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values('学生信息', '3', '1', '/system/student', 'c', '0', 'system:student:view', '#', 'admin', sysdate(), '', null, '学生信息菜单');-- 按钮父菜单id
select @parentid := last_insert_id();-- 按钮 sql
insert into sys_menu (menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values('学生信息查询', @parentid, '1',  '#',  'f', '0', 'system:student:list',         '#', 'admin', sysdate(), '', null, '');insert into sys_menu (menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values('学生信息新增', @parentid, '2',  '#',  'f', '0', 'system:student:add',          '#', 'admin', sysdate(), '', null, '');insert into sys_menu (menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values('学生信息修改', @parentid, '3',  '#',  'f', '0', 'system:student:edit',         '#', 'admin', sysdate(), '', null, '');insert into sys_menu (menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values('学生信息删除', @parentid, '4',  '#',  'f', '0', 'system:student:remove',       '#', 'admin', sysdate(), '', null, '');insert into sys_menu (menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values('学生信息导出', @parentid, '5',  '#',  'f', '0', 'system:student:export',       '#', 'admin', sysdate(), '', null, '');

5 新增测试代码验证 新增 ruoyi-system\com\ruoyi\system\controller\SysStudentController.java

（1）删除SysStudentMapper.xml 

（2） 空架构ruoyi-system\com\ruoyi\system\mapper\SysStudentMapper.java，因为继承了BaseMap，所以包含了很多API，直接调用即可。

package com.ruoyi.system.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.system.domain.SysStudent;/*** 学生信息Mapper接口** 空架构ruoyi-system\com\ruoyi\system\mapper\SysStudentMapper.java，因为继承了BaseMap，所以包含了很多API，直接调用即可。* * 有比较复杂的查询（关联查询、子查询、多表查询），可以加接口并使用mybatis，因为mybatis-plus兼容mybatis**/
public interface SysStudentMapper extends BaseMapper<SysStudent>
{}

（3）空架子（那就加一个方法吧）新增 ruoyi-system\com\ruoyi\system\service\ISysStudentService.java

package com.ruoyi.system.service;
import java.util.List;
import com.baomidou.mybatisplus.extension.service.IService;
import com.ruoyi.system.domain.SysStudent;/*** 学生信息Service接口** @author ruoyi*/
public interface ISysStudentService extends IService<SysStudent>
{/*** 查询学生信息列表** @param sysStudent 学生信息* @return 学生信息集合*/public List<SysStudent> queryList(SysStudent sysStudent);
}

（4） ruoyi-system\com\ruoyi\system\service\impl\SysStudentServiceImpl.java

package com.ruoyi.system.service.impl;import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.system.mapper.SysStudentMapper;
import com.ruoyi.system.domain.SysStudent;
import com.ruoyi.system.service.ISysStudentService;import java.util.List;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.domain.SysStudent;
import com.ruoyi.system.mapper.SysStudentMapper;
import com.ruoyi.system.service.ISysStudentService;/*** 学生信息Service业务层处理** @author ruoyi*/
@Service
public class SysStudentServiceImpl extends ServiceImpl<SysStudentMapper, SysStudent> implements ISysStudentService
{@Overridepublic List<SysStudent> queryList(SysStudent sysStudent){// 注意：mybatis-plus lambda 模式不支持 eclipse 的编译器// LambdaQueryWrapper<SysStudent> queryWrapper = Wrappers.lambdaQuery();// queryWrapper.eq(SysStudent::getStudentName, sysStudent.getStudentName());QueryWrapper<SysStudent> queryWrapper = Wrappers.query();if (StringUtils.isNotEmpty(sysStudent.getStudentName())){queryWrapper.eq("student_name", sysStudent.getStudentName());}if (StringUtils.isNotNull(sysStudent.getStudentAge())){queryWrapper.eq("student_age", sysStudent.getStudentAge());}if (StringUtils.isNotEmpty(sysStudent.getStudentHobby())){queryWrapper.eq("student_hobby", sysStudent.getStudentHobby());}return this.list(queryWrapper);}
}

（6）ruoyi-system\com\ruoyi\system\controller\SysStudentController.java 

import java.util.Arrays;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.ruoyi.common.annotation.Log;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.common.enums.BusinessType;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.system.domain.SysStudent;
import com.ruoyi.system.service.ISysStudentService;/*** 学生信息Controller* * @author ruoyi*/
@RestController
@RequestMapping("/system/student")
public class SysStudentController extends BaseController
{@Autowiredprivate ISysStudentService sysStudentService;/*** 查询学生信息列表*/@PreAuthorize("@ss.hasPermi('system:student:list')")@GetMapping("/list")public TableDataInfo list(SysStudent sysStudent){startPage();List<SysStudent> list = sysStudentService.queryList(sysStudent);return getDataTable(list);}/*** 导出学生信息列表*/@PreAuthorize("@ss.hasPermi('system:student:export')")@Log(title = "学生信息", businessType = BusinessType.EXPORT)@GetMapping("/export")public AjaxResult export(SysStudent sysStudent){List<SysStudent> list = sysStudentService.queryList(sysStudent);ExcelUtil<SysStudent> util = new ExcelUtil<SysStudent>(SysStudent.class);return util.exportExcel(list, "student");}/*** 获取学生信息详细信息*/@PreAuthorize("@ss.hasPermi('system:student:query')")@GetMapping(value = "/{studentId}")public AjaxResult getInfo(@PathVariable("studentId") Long studentId){return AjaxResult.success(sysStudentService.getById(studentId));}/*** 新增学生信息*/@PreAuthorize("@ss.hasPermi('system:student:add')")@Log(title = "学生信息", businessType = BusinessType.INSERT)@PostMappingpublic AjaxResult add(@RequestBody SysStudent sysStudent){return toAjax(sysStudentService.save(sysStudent));}/*** 修改学生信息*/@PreAuthorize("@ss.hasPermi('system:student:edit')")@Log(title = "学生信息", businessType = BusinessType.UPDATE)@PutMappingpublic AjaxResult edit(@RequestBody SysStudent sysStudent){return toAjax(sysStudentService.updateById(sysStudent));}/*** 删除学生信息*/@PreAuthorize("@ss.hasPermi('system:student:remove')")@Log(title = "学生信息", businessType = BusinessType.DELETE)@DeleteMapping("/{studentIds}")public AjaxResult remove(@PathVariable Long[] studentIds){return toAjax(sysStudentService.removeByIds(Arrays.asList(studentIds)));}
}

（7）也要改：实体类ruoyi-system\com\ruoyi\system\domain\SysStudent.java

package com.ruoyi.system.domain;import java.io.Serializable;// mybatis-plus 多加
import java.util.Date;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.baomidou.mybatisplus.annotation.IdType;// mybatis-plus 多加
import com.baomidou.mybatisplus.annotation.TableField;// mybatis-plus 多加
import com.baomidou.mybatisplus.annotation.TableId;// mybatis-plus 多加
import com.baomidou.mybatisplus.annotation.TableName;// mybatis-plus 多加
import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.common.annotation.Excel;/*** 学生信息对象 sys_student** @author ruoyi*/
@TableName(value = "sys_student")
public class SysStudent implements Serializable
{@TableField(exist = false)private static final long serialVersionUID = 1L;/** 编号 */@TableId(type = IdType.AUTO)private Long studentId;/** 学生名称 */@Excel(name = "学生名称")private String studentName;/** 年龄 */@Excel(name = "年龄")private Integer studentAge;/** 爱好（0代码 1音乐 2电影） */@Excel(name = "爱好", readConverterExp = "0=代码,1=音乐,2=电影")private String studentHobby;/** 性别（0男 1女 2未知） */@Excel(name = "性别", readConverterExp = "0=男,1=女,2=未知")private String studentSex;/** 状态（0正常 1停用） */@Excel(name = "状态", readConverterExp = "0=正常,1=停用")private String studentStatus;/** 生日 */@JsonFormat(pattern = "yyyy-MM-dd")@Excel(name = "生日", width = 30, dateFormat = "yyyy-MM-dd")private Date studentBirthday;public void setStudentId(Long studentId){this.studentId = studentId;}public Long getStudentId(){return studentId;}public void setStudentName(String studentName){this.studentName = studentName;}public String getStudentName(){return studentName;}public void setStudentAge(Integer studentAge){this.studentAge = studentAge;}public Integer getStudentAge(){return studentAge;}public void setStudentHobby(String studentHobby){this.studentHobby = studentHobby;}public String getStudentHobby(){return studentHobby;}public void setStudentSex(String studentSex){this.studentSex = studentSex;}public String getStudentSex(){return studentSex;}public void setStudentStatus(String studentStatus){this.studentStatus = studentStatus;}public String getStudentStatus(){return studentStatus;}public void setStudentBirthday(Date studentBirthday){this.studentBirthday = studentBirthday;}public Date getStudentBirthday(){return studentBirthday;}@Overridepublic String toString() {return new ToStringBuilder(this,ToStringStyle.MULTI_LINE_STYLE).append("studentId", getStudentId()).append("studentName", getStudentName()).append("studentAge", getStudentAge()).append("studentHobby", getStudentHobby()).append("studentSex", getStudentSex()).append("studentStatus", getStudentStatus()).append("studentBirthday", getStudentBirthday()).toString();}
}

6 登录系统测试学生菜单增删改查功能。

7 拓展：修改模板，使用代码生成器生成mybatis-plus CURD代码

107 实现离线IP地址定位（集成ip2region） 

01 需求

1. 登录日志会查询并记录，登录地（网）址、登录地点。
2. 如果系统并发用户大的话，就会频繁地记录登录地（网）址、登录地点，对网络的消耗大。
3. 离线IP地址定位库主要用于内网或想减少对外访问http带来的资源消耗。
4. 最终：实现通过本地离线IP库去取归属地了，不需要再通过请求HTTP网络（外网）的方式。

1 ruoyi-common/pom.xml引入依赖

<!-- 离线IP地址定位库 -->
<dependency><groupId>org.lionsoul</groupId><artifactId>ip2region</artifactId><version>1.7.2</version>
</dependency>

2 ruoyi-common/uitl添加工具类RegionUtil.java

package com.ruoyi.common.utils;
import java.io.File;
import java.io.InputStream;
import java.lang.reflect.Method;
import org.apache.commons.io.FileUtils;
import org.lionsoul.ip2region.DataBlock;
import org.lionsoul.ip2region.DbConfig;
import org.lionsoul.ip2region.DbSearcher;
import org.lionsoul.ip2region.Util;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;/*** 根据ip离线查询地址** @author ruoyi*/
public class RegionUtil
{private static final Logger log = LoggerFactory.getLogger(RegionUtil.class);// 临时文件的地址private static final String JAVA_TEMP_DIR = "java.io.tmpdir";static DbConfig config = null;static DbSearcher searcher = null;/*** 初始化IP库*/static{try{// 因为jar无法读取文件,因此这里我们会，复制并创建临时文件，放到target下面// 为什么会读到target目录下面去呢？因为我们打包生成的jar包，就是生成到了target目录下，它们是一起的String dbPath = RegionUtil.class.getResource("/ip2region/ip2region.db").getPath();File file = new File(dbPath);if (!file.exists()){String tmpDir = System.getProperties().getProperty(JAVA_TEMP_DIR); // target目录dbPath = tmpDir + "ip2region.db"; // 文件名字 自定义file = new File(dbPath);ClassPathResource cpr = new ClassPathResource("ip2region" + File.separator + "ip2region.db");InputStream resourceAsStream = cpr.getInputStream();if (resourceAsStream != null){FileUtils.copyInputStreamToFile(resourceAsStream, file);}}config = new DbConfig();searcher = new DbSearcher(config, dbPath);log.info("bean [{}]", config);log.info("bean [{}]", searcher);}catch (Exception e){log.error("init ip region error:{}", e);}}/*** 解析IP** @param ip* @return*/public static String getRegion(String ip){try{// dbif (searcher == null || StringUtils.isEmpty(ip)){log.error("DbSearcher is null");return StringUtils.EMPTY;}long startTime = System.currentTimeMillis();// 查询算法int algorithm = DbSearcher.MEMORY_ALGORITYM;Method method = null;switch (algorithm){case DbSearcher.BTREE_ALGORITHM:method = searcher.getClass().getMethod("btreeSearch", String.class);break;case DbSearcher.BINARY_ALGORITHM:method = searcher.getClass().getMethod("binarySearch", String.class);break;case DbSearcher.MEMORY_ALGORITYM:method = searcher.getClass().getMethod("memorySearch", String.class);break;}DataBlock dataBlock = null;if (Util.isIpAddress(ip) == false){log.warn("warning: Invalid ip address");}dataBlock = (DataBlock) method.invoke(searcher, ip);String result = dataBlock.getRegion();long endTime = System.currentTimeMillis();log.debug("region use time[{}] result[{}]", endTime - startTime, result);return result;}catch (Exception e){log.error("error:{}", e);}return StringUtils.EMPTY;}}

3 修改AddressUtils.java

package com.ruoyi.common.utils.ip;import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.utils.RegionUtil;
import com.ruoyi.common.utils.StringUtils;/*** 获取地址类** @author ruoyi*/
public class AddressUtils
{private static final Logger log = LoggerFactory.getLogger(AddressUtils.class);// 未知地址public static final String UNKNOWN = "XX XX";public static String getRealAddressByIP(String ip){String address = UNKNOWN;// 内网不查询if (IpUtils.internalIp(ip)){return "内网IP";}if (RuoYiConfig.isAddressEnabled()){try{String rspStr = RegionUtil.getRegion(ip);if (StringUtils.isEmpty(rspStr)){log.error("获取地理位置异常 {}", ip);return UNKNOWN;}String[] obj = rspStr.split("\\|");String region = obj[2]; // 地区String city = obj[3];   // 城市return String.format("%s %s", region, city);}catch (Exception e){log.error("获取地理位置异常 {}", e);}}return address;}
}

4 添加离线IP地址库插件

        下载前端插件相关包和代码实现ruoyi/集成ip2region离线地址定位.zip

        链接: https://pan.baidu.com/s/13JVC9jm-Dp9PfHdDDylLCQ 提取码: y9jt

5 添加离线IP地址库。即所有的请求都会从ip2region.db中获取数据，就不用去请求外网地址的db，这样就提升了响应速度：

        在ruoyi-admin的src/main/resources下新建ip2region目录，并复制文件ip2region.db到目录下。如下图所示：

6 Test#main方法，简单测试

修改AddressUtils.java，跳过内网不查，打开开关：

package com.ruoyi.common.utils.ip;import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.utils.RegionUtil;
import com.ruoyi.common.utils.StringUtils;/*** 获取地址类** @author ruoyi*/
public class AddressUtils
{private static final Logger log = LoggerFactory.getLogger(AddressUtils.class);// 未知地址public static final String UNKNOWN = "XX XX";public static String getRealAddressByIP(String ip){String address = UNKNOWN;// 内网不查询/* TO DO main方法测试，跳过内网不查if (IpUtils.internalIp(ip)){return "内网IP";}*//* TO DO main方法测试，打开开关if (RuoYiConfig.isAddressEnabled()){*/try{String rspStr = RegionUtil.getRegion(ip);if (StringUtils.isEmpty(rspStr)){log.error("获取地理位置异常 {}", ip);return UNKNOWN;}String[] obj = rspStr.split("\\|");String region = obj[2]; // 地区String city = obj[3];   // 城市return String.format("%s %s", region, city);}catch (Exception e){log.error("获取地理位置异常 {}", e);}/* TO DO main方法测试，打开开关}*/return address;}
}

7 项目测试成功 

1. 重启后端
2. 测试成功

108 实现数据库密码加密（集成druid）

1 执行命令加密数据库密码 

找到druid-xxx.jar：

 打开druid-xxx.jar所在目录的cmd命令对话框：

 执行命令加密数据库密码：

java -cp druid-1.2.16.jar com.alibaba.druid.filter.config.ConfigTools password

得到加密后的字符串：

# 私钥
privateKey:MIIBVQIBADANBgkqhkiG9w0BAQEFAASCAT8wggE7AgEAAkEA8RA9i8V5Nd13YRjmJHXMDFXoFSAuh+WjTz8Fg9crfeVd+5l5Q7NayfktyUoj+MKljZgSeST4QevQM4Bc8wT6wQIDAQABAkEA06m24KsbqrgywgbizNDBwXMMvL/tG1X+9u4XIZQkk/zFLv1RJnUVAvHAnlZdaJ7W8oyH103Qf6qYhba6l3EBoQIhAPnORmJ98WHnKb5qKx1rt75ujVvL1dJzc5mC2I4BDqvTAiEA9wp4flvQTLNfxdrxoahJJuSEIvX1iFTPBGZYucb0BpsCIQDpJw2qf8H7jrX3c0AqhY9JvgVR2D4J3pfWf7l/UJ1Q4QIgZnNuMyEKirSdFGXPbbZn1/xPHFyanhZl4DI9u5XZ398CIFclldnmznkwj5hxehvLjavMOEDbCv2V8UhsklmPeEP9# 公钥
publicKey:MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAPEQPYvFeTXdd2EY5iR1zAxV6BUgLoflo08/BYPXK33lXfuZeUOzWsn5LclKI/jCpY2YEnkk+EHr0DOAXPME+sECAwEAAQ==# 密码
# 密码用来替换掉application-druid.yml的数据密码password：123456
password:1qHNnoy4ASqZ+8FFa2YLoix/FK5x+J7ziviZ8QbqsKetMPesbqjPh2f3I1X0izDqfHhKFaa1dh+1wUXy/bQ3KQ==

2 application-druid.yml：配置数据源，提示Druid数据源需要对数据库密码进行解密。

1. 密码替换
2. 配置公钥和私钥

spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.cj.jdbc.Driverdruid:master:url: jdbc:mysql://localhost:3306/ry?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8username: rootpassword: gkYlljNHKe0/4z7bbJxD7v/txWJIFbiGWwsIPo176Q7fG0UjcSizNxuRUI2ll27ZPQf2ekiHFptus2/Rc4cmvA==slave:enabled: falseurl: username: password: initialSize: 5minIdle: 10maxActive: 20maxWait: 60000connectTimeout: 30000socketTimeout: 60000timeBetweenEvictionRunsMillis: 60000minEvictableIdleTimeMillis: 300000maxEvictableIdleTimeMillis: 900000validationQuery: SELECT 1 FROM DUALtestWhileIdle: truetestOnBorrow: falsetestOnReturn: false# 第二步：配置连接属性# 第三步：把最新的公钥放到key=xxxconnectProperties: config.decrypt=true;config.decrypt.key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALizFQBZnHsPpj31Z8yOrrRL4R1jtrOnuEdW1Vt2vSKR/qRMqXjVeirWf8PT7srD33T8VuXzdwZpyhWVACDL1oUCAwEAAQ==webStatFilter: enabled: truestatViewServlet:enabled: trueallow:url-pattern: /druid/*login-username: ruoyilogin-password: 123456filter:config:# 第一步：是否配置加密，设置为trueenabled: truestat:enabled: truelog-slow-sql: trueslow-sql-millis: 1000merge-sql: truewall:config:multi-statement-allow: true

3 DruidProperties.java配置connectProperties属性

 // 添加：属性（我们刚刚加）@Value("${spring.datasource.druid.connectProperties}")private String connectProperties;public DruidDataSource dataSource(DruidDataSource datasource){// 添加：为数据库密码提供加密功能 datasource.setConnectionProperties(connectProperties);}

4 测试：启动应用程序测试验证加密结果 

        启动应用没有报错

5 提示：如若忘记密码可以使用工具类解密（传入生成的公钥+密码）

public static void main(String[] args) throws Exception
{String password = ConfigTools.decrypt("MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALizFQBZnHsPpj31Z8yOrrRL4R1jtrOnuEdW1Vt2vSKR/qRMqXjVeirWf8PT7srD33T8VuXzdwZpyhWVACDL1oUCAwEAAQ==","gkYlljNHKe0/4z7bbJxD7v/txWJIFbiGWwsIPo176Q7fG0UjcSizNxuRUI2ll27ZPQf2ekiHFptus2/Rc4cmvA==");System.out.println("解密密码：" + password);
}

109 实现滑块验证码（集成aj-captcha）

1 ruoyi-framework\pom.xml添加依赖

        <!-- 滑块验证码  --><dependency><groupId>com.github.anji-plus</groupId><artifactId>captcha-spring-boot-starter</artifactId><version>1.2.7</version></dependency>

2 修改application.yml，加入aj-captcha配置

# 滑块验证码
aj:captcha:# 缓存类型# 默认为本地缓存，如果有多台机器的话可能会出现误差# 自定义redis去实现cache-type: redis# blockPuzzle滑块 clickWord文字点选  default默认两者都实例化type: blockPuzzle# 右下角显示字water-mark: ruoyi.vip# 校验滑动拼图允许误差偏移量(默认5像素)slip-offset: 5# aes加密坐标开启或者禁用(true|false)aes-status: true# 滑动干扰项(0/1/2)interference-options: 2

同时在ruoyi-admin\src\main\resources\META-INF\services下创建com.anji.captcha.service.CaptchaCacheService文件同时设置文件内容为

com.ruoyi.framework.web.service.CaptchaRedisService

 为什么还要配置？com.anji.captcha.service.impl.CaptchaServiceFactory：

static {/*** 作用：加载CaptchaCacheService的实现类CaptchaRedisService** 为什么要新建呢？*      aj-captcha是通过ServiceLoader.load(CaptchaCacheService.class)这种方式去加载的，这种方式是jdk的加载方式。*      而jdk包的加载方式，需要我们在META-INF.services去创建对应的类，才能加载到对应的XxxServie。* 创建什么XxxService呢？*      默认情况下，会使用本地的实现类：com.anji.captcha.service.impl.CaptchaCacheServiceMemImpl，把所有的数据都保存在本地（local）。*      而我们需要使用redis做为缓存，所以我们会去自定义一个Redis的Service实现类。* 如何创建redis缓存的实现类？*      （1）创建一个类实现CaptchaCacheService*      （2）配置：META-INF.services去创建对应的类* 提示：只有在使用自定义redis时才需要这个东西，如果不使用redis的话就不用管它了*/ServiceLoader<CaptchaCacheService> cacheServices = ServiceLoader.load(CaptchaCacheService.class);Iterator var1 = cacheServices.iterator();while(var1.hasNext()) {CaptchaCacheService item = (CaptchaCacheService)var1.next();cacheService.put(item.type(), item);}logger.info("supported-captchaCache-service:{}", cacheService.keySet().toString());ServiceLoader<CaptchaService> services = ServiceLoader.load(CaptchaService.class);Iterator var5 = services.iterator();while(var5.hasNext()) {CaptchaService item = (CaptchaService)var5.next();instances.put(item.captchaType(), item);}logger.info("supported-captchaTypes-service:{}", instances.keySet().toString());}

配置效果：

3 在SecurityConfig中设置httpSecurity配置匿名访问（白名单）

/*** "/captcha/get"：获取验证码的信息接口* "/captcha/check"：检查验证码的信息接口*/
.antMatchers("/login", "/captcha/get", "/captcha/check").permitAll()
/*** 以前的验证码配置，删除掉*/
//.antMatchers("/login", "/register", "/captchaImage").permitAll()	

4 修改相关类

（1）移除原先验证码使用的类

移除原先不需要的类：

ruoyi-admin\com\ruoyi\web\controller\common\CaptchaController.java
ruoyi-framework\com\ruoyi\framework\config\CaptchaConfig.java
ruoyi-framework\com\ruoyi\framework\config\KaptchaTextCreator.java

（2）修改ruoyi-admin\com\ruoyi\web\controller\system\SysLoginController.java

/*** 登录方法* * @param loginBody 登录信息* @return 结果*/
@PostMapping("/login")
public AjaxResult login(@RequestBody LoginBody loginBody)
{AjaxResult ajax = AjaxResult.success();// 生成令牌// 滑块验证码：去掉UUIDString token = loginService.login(loginBody.getUsername(), loginBody.getPassword(), loginBody.getCode());ajax.put(Constants.TOKEN, token);return ajax;
}

（3）修改ruoyi-framework\com\ruoyi\framework\web\service\SysLoginService.java

package com.ruoyi.framework.web.service;import javax.annotation.Resource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.security.authentication.AuthenticationManager;
import org.springframework.security.authentication.BadCredentialsException;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.Authentication;
import org.springframework.stereotype.Component;
import com.anji.captcha.model.common.ResponseModel;
import com.anji.captcha.model.vo.CaptchaVO;
import com.anji.captcha.service.CaptchaService;
import com.ruoyi.common.constant.Constants;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.core.domain.model.LoginUser;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.exception.user.CaptchaException;
import com.ruoyi.common.exception.user.UserPasswordNotMatchException;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.MessageUtils;
import com.ruoyi.common.utils.ServletUtils;
import com.ruoyi.common.utils.ip.IpUtils;
import com.ruoyi.framework.manager.AsyncManager;
import com.ruoyi.framework.manager.factory.AsyncFactory;
import com.ruoyi.framework.security.context.AuthenticationContextHolder;
import com.ruoyi.system.service.ISysUserService;/*** 登录校验方法* * @author ruoyi*/
@Component
public class SysLoginService
{@Autowiredprivate TokenService tokenService;@Resourceprivate AuthenticationManager authenticationManager;@Autowiredprivate ISysUserService userService;@Autowired@Lazyprivate CaptchaService captchaService;/*** 登录验证* 滑块验证码：也不需要UUID了* @param username 用户名* @param password 密码* @param code 验证码* @return 结果*/public String login(String username, String password, String code){CaptchaVO captchaVO = new CaptchaVO();captchaVO.setCaptchaVerification(code);/* * 滑块验证码：这里直接通过它提供的验证方法去验证*/ResponseModel response = captchaService.verification(captchaVO);if (!response.isSuccess()){AsyncManager.me().execute(AsyncFactory.recordLogininfor(username, Constants.LOGIN_FAIL, MessageUtils.message("user.jcaptcha.error")));throw new CaptchaException();}// 用户验证Authentication authentication = null;try{UsernamePasswordAuthenticationToken authenticationToken = new UsernamePasswordAuthenticationToken(username, password);AuthenticationContextHolder.setContext(authenticationToken);// 该方法会去调用UserDetailsServiceImpl.loadUserByUsernameauthentication = authenticationManager.authenticate(authenticationToken);}catch (Exception e){if (e instanceof BadCredentialsException){AsyncManager.me().execute(AsyncFactory.recordLogininfor(username, Constants.LOGIN_FAIL, MessageUtils.message("user.password.not.match")));throw new UserPasswordNotMatchException();}else{AsyncManager.me().execute(AsyncFactory.recordLogininfor(username, Constants.LOGIN_FAIL, e.getMessage()));throw new ServiceException(e.getMessage());}}finally{AuthenticationContextHolder.clearContext();}AsyncManager.me().execute(AsyncFactory.recordLogininfor(username, Constants.LOGIN_SUCCESS, MessageUtils.message("user.login.success")));LoginUser loginUser = (LoginUser) authentication.getPrincipal();recordLoginInfo(loginUser.getUserId());// 生成tokenreturn tokenService.createToken(loginUser);}/*** 记录登录信息** @param userId 用户ID*/public void recordLoginInfo(Long userId){SysUser sysUser = new SysUser();sysUser.setUserId(userId);sysUser.setLoginIp(IpUtils.getIpAddr(ServletUtils.getRequest()));sysUser.setLoginDate(DateUtils.getNowDate());userService.updateUserProfile(sysUser);}
}

（4）新增 ruoyi-framework\com\ruoyi\framework\web\service\CaptchaRedisService.java：自定义的redis缓存的实现类，所以的数据都缓存到redis中

package com.ruoyi.framework.web.service;import java.util.concurrent.TimeUnit;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.StringRedisTemplate;
import com.anji.captcha.service.CaptchaCacheService;/*** 自定义redis验证码缓存实现类* * @author ruoyi*/
public class CaptchaRedisService implements CaptchaCacheService
{@Autowiredprivate StringRedisTemplate stringRedisTemplate;@Overridepublic void set(String key, String value, long expiresInSeconds){stringRedisTemplate.opsForValue().set(key, value, expiresInSeconds, TimeUnit.SECONDS);}@Overridepublic boolean exists(String key){return stringRedisTemplate.hasKey(key);}@Overridepublic void delete(String key){stringRedisTemplate.delete(key);}@Overridepublic String get(String key){return stringRedisTemplate.opsForValue().get(key);}@Overridepublic Long increment(String key, long val){return stringRedisTemplate.opsForValue().increment(key, val);}@Overridepublic String type(){return "redis";}
}

5 前端集成：添加滑动验证码插件到ruoyi-ui

1. 前端：
   1. 登录页面要改
   2. 登录逻辑要改

下载前端插件相关包和代码实现ruoyi-vue/集成滑动验证码.zip

链接: https://pan.baidu.com/s/13JVC9jm-Dp9PfHdDDylLCQ 提取码: y9jt

6 测试

1. 前端：npm install（安装："crypto-js": "4.1.1",） , npm run dev
2. 后端：重启
3. 登录成功
4. 到redis中查看滑动验证的信息
5. 滑动验证码2分钟过期

110 实现分库分表（集成sharding-jdbc，当当网捐给apache的中间件）

 0 需求

1. 当你们订单的数据量很大的时候，就可以去用了。比如说，每天订单量是上千万。这时把全部的订单都放到一张表肯定是不行的，一下就撑爆了。此时，就可以采用分库分表存储策略。
2. sharding-jdbc是由当当捐入给apache的一款分布式数据库中间件，支持垂直分库、垂直分表、水平分库、水平分表、读写分离、分布式事务和高可用等相关功能。

1 ruoyi-framework\pom.xml模块添加sharding-jdbc整合依赖    

<!-- sharding-jdbc分库分表 -->
<dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-core</artifactId><version>4.1.1</version>
</dependency>

2 创建两个测试数据库

create database `ry-order1`;
create database `ry-order2`;

3 创建两个测试订单表（每个库都两张表）

-- ----------------------------
-- 订单信息表sys_order_0
-- ----------------------------
drop table if exists sys_order_0;
create table sys_order_0
(order_id      bigint(20)      not null                   comment '订单ID',user_id       bigint(64)      not null                   comment '用户编号',status        char(1)         not null                   comment '状态（0交易成功 1交易失败）',order_no      varchar(64)     default null               comment '订单流水',primary key (order_id)
) engine=innodb comment = '订单信息表';-- ----------------------------
-- 订单信息表sys_order_1
-- ----------------------------
drop table if exists sys_order_1;
create table sys_order_1
(order_id      bigint(20)      not null                   comment '订单ID',user_id       bigint(64)      not null                   comment '用户编号',status        char(1)         not null                   comment '状态（0交易成功 1交易失败）',order_no      varchar(64)     default null               comment '订单流水',primary key (order_id)
) engine=innodb comment = '订单信息表';

4 下载插件相关包和代码实现覆盖到工程中

提示

下载插件相关包和代码实现ruoyi/集成sharding-jdbc实现分库分表.zip

链接: https://pan.baidu.com/s/13JVC9jm-Dp9PfHdDDylLCQ 提取码: y9jt

（1） 创建类ShardingDataSourceConfig配置sharding-jdbc信息

package com.ruoyi.framework.config;import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.ruoyi.framework.config.properties.DruidProperties;/*** sharding 配置信息* 这个的话是仅仅只是配置了分库分表啊，其实它里边还有很多东西可以配置，大家可以去看到它的一个文档啊。* @author ruoyi*/
@Configuration
public class ShardingDataSourceConfig
{/*** 数据源1*/@Bean@ConfigurationProperties("spring.datasource.druid.order1")@ConditionalOnProperty(prefix = "spring.datasource.druid.order1", name = "enabled", havingValue = "true")public DataSource order1DataSource(DruidProperties druidProperties){/*** 加数据源1*/DruidDataSource dataSource = DruidDataSourceBuilder.create().build();return druidProperties.dataSource(dataSource);}/*** 数据源2*/@Bean@ConfigurationProperties("spring.datasource.druid.order2")@ConditionalOnProperty(prefix = "spring.datasource.druid.order2", name = "enabled", havingValue = "true")public DataSource order2DataSource(DruidProperties druidProperties){/*** 加数据源2*/DruidDataSource dataSource = DruidDataSourceBuilder.create().build();return druidProperties.dataSource(dataSource);}/*** 加了sharding-jdbc的数据源shardingDataSource*/@Bean(name = "shardingDataSource")public DataSource shardingDataSource(@Qualifier("order1DataSource") DataSource order1DataSource, @Qualifier("order2DataSource") DataSource order2DataSource) throws SQLException{Map<String, DataSource> dataSourceMap = new HashMap<>();/*** 初始两个数据源*/dataSourceMap.put("order1", order1DataSource);dataSourceMap.put("order2", order2DataSource);// sys_order 表规则配置/*** sys_order：表前缀* "order$->{1..2}：现在只配置了两个库* sys_order_$->{0..1}：现在只配置了两个表*/TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("sys_order", "order$->{1..2}.sys_order_$->{0..1}");// 配置分库策略/*** user_id：分库策略是通过user_id去分的* "order$->{user_id % 2 + 1}"：分库策略*/orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "order$->{user_id % 2 + 1}"));// 配置分表策略/*** order_id：分表策略是通过order_id去分的* "sys_order_$->{order_id % 2}"：分表策略*/orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "sys_order_$->{order_id % 2}"));// 分布式主键/*** 主键策略：使用Sharding-jdbc默认的主键策略*/orderTableRuleConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "order_id"));// 配置分片规则ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);// 获取数据源对象DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, getProperties());return dataSource;}/*** 系统参数配置*/private Properties getProperties(){Properties shardingProperties = new Properties();shardingProperties.put("sql.show", true);return shardingProperties;}
}

（2） 配置文件application-druid.yml添加测试数据源

# 数据源配置
spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.cj.jdbc.Driverdruid:# 主库数据源master:url: jdbc:mysql://localhost:3306/ry?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8username: rootpassword: password# 订单库1order1:enabled: trueurl: jdbc:mysql://localhost:3306/ry-order1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8username: rootpassword: password# 订单库2order2:enabled: trueurl: jdbc:mysql://localhost:3306/ry-order2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8username: rootpassword: password...................

 （3）修改1：com.ruoyi.common.enums.DataSourceType.java：加入分库分表的枚举，后续进行切换动作

package com.ruoyi.common.enums;/*** 数据源* * @author ruoyi*/
public enum DataSourceType
{/*** 主库*/MASTER,/*** 从库*/SLAVE,/*** 分库分表* 需要做切换的动作*/SHARDING
}

（4）修改2：com.ruoyi.framework.config.DruidConfig：加入sharding数据源

package com.ruoyi.framework.config;import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.sql.DataSource;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
import com.alibaba.druid.util.Utils;
import com.ruoyi.common.enums.DataSourceType;
import com.ruoyi.common.utils.spring.SpringUtils;
import com.ruoyi.framework.config.properties.DruidProperties;
import com.ruoyi.framework.datasource.DynamicDataSource;/*** druid 配置多数据源* * @author ruoyi*/
@Configuration
public class DruidConfig
{@Bean@ConfigurationProperties("spring.datasource.druid.master")public DataSource masterDataSource(DruidProperties druidProperties){DruidDataSource dataSource = DruidDataSourceBuilder.create().build();return druidProperties.dataSource(dataSource);}@Bean@ConfigurationProperties("spring.datasource.druid.slave")@ConditionalOnProperty(prefix = "spring.datasource.druid.slave", name = "enabled", havingValue = "true")public DataSource slaveDataSource(DruidProperties druidProperties){DruidDataSource dataSource = DruidDataSourceBuilder.create().build();return druidProperties.dataSource(dataSource);}@Bean(name = "dynamicDataSource")@Primarypublic DynamicDataSource dataSource(DataSource masterDataSource){Map<Object, Object> targetDataSources = new HashMap<>();targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);setDataSource(targetDataSources, DataSourceType.SLAVE.name(), "slaveDataSource");/*** 其实就是这一句，加入sharding-jdbc的数据源。* 这个之前讲数据源的时候也讲到，就是说我们把所有的数据源加好之后需要在这里重新设一下，不然的话它就切换不到啊。*/setDataSource(targetDataSources, DataSourceType.SHARDING.name(), "shardingDataSource");return new DynamicDataSource(masterDataSource, targetDataSources);}/*** 设置数据源* * @param targetDataSources 备选数据源集合* @param sourceName 数据源名称* @param beanName bean名称*/public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName){try{DataSource dataSource = SpringUtils.getBean(beanName);targetDataSources.put(sourceName, dataSource);}catch (Exception e){}}/*** 去除监控页面底部的广告*/@SuppressWarnings({ "rawtypes", "unchecked" })@Bean@ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true")public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties){// 获取web监控页面的参数DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();// 提取common.js的配置路径String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";String commonJsPattern = pattern.replaceAll("\\*", "js/common.js");final String filePath = "support/http/resources/js/common.js";// 创建filter进行过滤Filter filter = new Filter(){@Overridepublic void init(javax.servlet.FilterConfig filterConfig) throws ServletException{}@Overridepublic void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)throws IOException, ServletException{chain.doFilter(request, response);// 重置缓冲区，响应头不会被重置response.resetBuffer();// 获取common.jsString text = Utils.readFromResource(filePath);// 正则替换banner, 除去底部的广告信息text = text.replaceAll("<a.*?banner\"></a><br/>", "");text = text.replaceAll("powered.*?shrek.wang</a>", "");response.getWriter().write(text);}@Overridepublic void destroy(){}};FilterRegistrationBean registrationBean = new FilterRegistrationBean();registrationBean.setFilter(filter);registrationBean.addUrlPatterns(commonJsPattern);return registrationBean;}
}

（5）创建1：测试类：com.ruoyi.web.controller.system.SysOrderController

package com.ruoyi.web.controller.system;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.ruoyi.system.domain.SysOrder;
import com.ruoyi.system.service.ISysOrderService;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.utils.uuid.IdUtils;/*** 订单 Controller* * @author ruoyi*/
@RestController
@RequestMapping("/order")
public class SysOrderController extends BaseController
{@Autowiredprivate ISysOrderService sysOrderService;/*** 我们通过userId进行分库*/@GetMapping("/add/{userId}")public AjaxResult add(@PathVariable("userId") Long userId){SysOrder sysOrder = new SysOrder();sysOrder.setUserId(userId);sysOrder.setStatus("0");sysOrder.setOrderNo(IdUtils.fastSimpleUUID());return AjaxResult.success(sysOrderService.insertSysOrder(sysOrder));}/*** 把两个数据库的两张表都统一查询出来*/@GetMapping("/list")public AjaxResult list(SysOrder sysOrder){return AjaxResult.success(sysOrderService.selectSysOrderList(sysOrder));}/*** 自动从对应库中的对应表查询单条*/@GetMapping("/query/{orderId}")public AjaxResult query(@PathVariable("orderId") Long orderId){return AjaxResult.success(sysOrderService.selectSysOrderById(orderId));}
}

 （6）创建2：实体类：com.ruoyi.system.domain.SysOrder

package com.ruoyi.system.domain;import com.ruoyi.common.core.domain.BaseEntity;/*** 订单对象 tb_order* * @author ruoyi*/
public class SysOrder extends BaseEntity
{private static final long serialVersionUID = 1L;/** 订单编号 */private Long orderId;/** 用户编号 */private Long userId;/** 状态 */private String status;/** 订单编号 */private String orderNo;public void setOrderId(Long orderId){this.orderId = orderId;}public Long getOrderId(){return orderId;}public void setUserId(Long userId){this.userId = userId;}public Long getUserId(){return userId;}public void setStatus(String status){this.status = status;}public String getStatus(){return status;}public void setOrderNo(String orderNo){this.orderNo = orderNo;}public String getOrderNo(){return orderNo;}
}

（7）创建3：XxxMapper.java： com.ruoyi.system.mapper.SysOrderMapper

package com.ruoyi.system.mapper;import java.util.List;
import com.ruoyi.system.domain.SysOrder;/*** 订单Mapper接口* * @author ruoyi*/
public interface SysOrderMapper
{/*** 查询订单* * @param orderId 订单编号* @return 订单信息*/public SysOrder selectSysOrderById(Long orderId);/*** 查询订单列表* * @param sysOrder 订单信息* @return 订单列表*/public List<SysOrder> selectSysOrderList(SysOrder sysOrder);/*** 新增订单* * @param sysOrder 订单* @return 结果*/public int insertSysOrder(SysOrder sysOrder);
}

（8）创建4：IXxxService接口：com.ruoyi.system.service.ISysOrderService

package com.ruoyi.system.service;import java.util.List;
import com.ruoyi.system.domain.SysOrder;/*** 订单Service接口* * @author ruoyi*/
public interface ISysOrderService
{/*** 查询订单* * @param orderId 订单编号* @return 订单信息*/public SysOrder selectSysOrderById(Long orderId);/*** 查询订单列表* * @param sysOrder 订单信息* @return 订单列表*/public List<SysOrder> selectSysOrderList(SysOrder sysOrder);/*** 新增订单* * @param sysOrder 订单* @return 结果*/public int insertSysOrder(SysOrder sysOrder);
}

 （9）创建5：XxxService实现类：

package com.ruoyi.system.service.impl;import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.system.mapper.SysOrderMapper;
import com.ruoyi.system.domain.SysOrder;
import com.ruoyi.system.service.ISysOrderService;
import com.ruoyi.common.annotation.DataSource;
import com.ruoyi.common.enums.DataSourceType;/*** 订单Service业务层处理* * @author ruoyi*/
@Service
public class SysOrderServiceImpl implements ISysOrderService 
{@Autowiredprivate SysOrderMapper myShardingMapper;/*** 查询订单* * @param orderId 订单编号* @return 订单信息*/@Override@DataSource(DataSourceType.SHARDING)public SysOrder selectSysOrderById(Long orderId){return myShardingMapper.selectSysOrderById(orderId);}/*** 查询订单列表* * @param sysOrder 订单信息* @return 订单列表*/@Override@DataSource(DataSourceType.SHARDING)public List<SysOrder> selectSysOrderList(SysOrder sysOrder){return myShardingMapper.selectSysOrderList(sysOrder);}/*** 新增订单* * @param sysOrder 订单* @return 结果*/@Override@DataSource(DataSourceType.SHARDING)public int insertSysOrder(SysOrder sysOrder){return myShardingMapper.insertSysOrder(sysOrder);}
}

（10）创建10：XxxMapper.xml：ruoyi-system\src\main\resources\mapper\system\SysOrderMapper.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.system.mapper.SysOrderMapper"><resultMap type="SysOrder" id="SysOrderResult"><result property="orderId"    column="order_id"    /><result property="userId"     column="user_id"     /><result property="status"     column="status"      /><result property="orderNo"    column="order_no"    /></resultMap><sql id="selectSysOrderVo">select order_id, user_id, status, order_no from sys_order</sql><select id="selectSysOrderList" parameterType="SysOrder" resultMap="SysOrderResult"><include refid="selectSysOrderVo"/></select><select id="selectSysOrderById" parameterType="Long" resultMap="SysOrderResult"><include refid="selectSysOrderVo"/>where order_id = #{orderId}</select><insert id="insertSysOrder" parameterType="SysOrder">insert into sys_order<trim prefix="(" suffix=")" suffixOverrides=","><if test="orderId != null">order_id,</if><if test="userId != null">user_id,</if><if test="status != null">status,</if><if test="orderNo != null">order_no,</if></trim><trim prefix="values (" suffix=")" suffixOverrides=","><if test="orderId != null">#{orderId},</if><if test="userId != null">#{userId},</if><if test="status != null">#{status},</if><if test="orderNo != null">#{orderNo},</if></trim></insert></mapper>

5 测试验证：crud

浏览器访问http://localhost/order/add/1入库到ry-order2

浏览器访问http://localhost/order/add/2入库到ry-order1

浏览器访问http://localhost/order/list查询到所有的数据

浏览器访问http://localhost/order/query/896582425981747200查询单条

同时根据订单号order_id % 2入库到sys_order_0或者sys_order_1

1. 屏蔽：com.ruoyi.framework.config.SecurityConfig

2. 重启后端
3. 测试成功