一.权限组

(一)权限组的常用操作

• 获取所有角色
• 添加角色
• 删除角色
• 查询所有菜单
• 根据角色 id 查询菜单 id
• 更新角色菜单

(二)功能实现

1.修改权限组控制器

修改:yeb/yeb-server/src/main/java/com/cxy/server/controller/PermissController.java文件

package com.cxy.server.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.cxy.server.pojo.Menu;
import com.cxy.server.pojo.MenuRole;
import com.cxy.server.pojo.Role;
import com.cxy.server.service.IMenuRoleService;
import com.cxy.server.service.IMenuService;
import com.cxy.server.service.IRoleService;
import com.cxy.server.utils.RespBean;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.stream.Collectors;

/**
 * @author 陈鑫元
 * @description 权限组
 * @date 2021-05-28 21:07
 * @since 1.0.0
 */
@RestController
@RequestMapping("system/basic/permiss")
public class PermissController {
    @Autowired
    private IRoleService roleService;

    @Autowired
    private IMenuService menuService;

    @Autowired
    private IMenuRoleService menuRoleService;

    @ApiOperation(value = "获取所有角色")
    @GetMapping("/")
    public List<Role> getAllRoles() {
        return roleService.list();
    }

    @ApiOperation(value = "添加角色")
    @PostMapping("/role")
    public RespBean addRole(@RequestBody Role role) {
        if (!role.getName().startsWith("ROLE_")) {
            role.setName("ROLE_" + role.getName());
        }
        if (roleService.save(role)) {
            return RespBean.success("添加成功!");
        }
        return RespBean.error("添加失败!");
    }

    @ApiOperation(value = "删除角色")
    @DeleteMapping("/role/{rid}")
    public RespBean deleteRole(@PathVariable Integer rid) {
        if (roleService.removeById(rid)) {
            return RespBean.success("删除成功");
        }
        return RespBean.error("删除失败");
    }

    @ApiOperation(value = "查询所有菜单")
    @GetMapping("/menus")
    public List<Menu> getAllMenus() {
        return menuService.getAllMenus();
    }

    @ApiOperation(value = "根据角色 id 查询菜单 id")
    @GetMapping("/mid/{rid}")
    public List<Integer> getMidByRid(@PathVariable Integer rid) {
        return menuRoleService.list(new QueryWrapper<MenuRole>()
                .eq("rid", rid))
                .stream().map(MenuRole::getMid)
                .collect(Collectors.toList());
    }

    @ApiOperation(value = "更新角色菜单")
    @PutMapping("/")
    public RespBean updateMenuRole(Integer rid, Integer[] mids) {
        return menuRoleService.updateMenuRole(rid, mids);
    }
}

• 获取所有角色、添加角色、删除角色、根据角色 id 查询菜单 id
  不需要自己写Service和Mapper层
• 查询所有菜单需要自己写IMenuService和MenuMapper
• 更新角色菜单需要自己写IMenuRoleService和MenuRoleMapper

2.修改菜单Service

修改:yeb/yeb-server/src/main/java/com/cxy/server/service/IMenuService.java文件

image.png

3.修改菜单Service实现类

修改:yeb/yeb-server/src/main/java/com/cxy/server/service/impl/MenuServiceImpl.java文件

image.png

4.修改菜单Mapper

修改:yeb/yeb-server/src/main/java/com/cxy/server/mapper/MenuMapper.java文件

image.png

5.修改菜单xml

修改:yeb/yeb-server/src/main/resources/mapper/MenuMapper.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.cxy.server.mapper.MenuMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.cxy.server.pojo.Menu">
        <id column="id" property="id"/>
        <result column="url" property="url"/>
        <result column="path" property="path"/>
        <result column="component" property="component"/>
        <result column="name" property="name"/>
        <result column="iconCls" property="iconCls"/>
        <result column="keepAlive" property="keepAlive"/>
        <result column="requireAuth" property="requireAuth"/>
        <result column="parentId" property="parentId"/>
        <result column="enabled" property="enabled"/>
    </resultMap>

    <resultMap id="Menus" type="com.cxy.server.pojo.Menu" extends="BaseResultMap">
        <collection property="children" ofType="com.cxy.server.pojo.Menu">
            <id column="id2" property="id"/>
            <result column="url2" property="url"/>
            <result column="path2" property="path"/>
            <result column="component2" property="component"/>
            <result column="name2" property="name"/>
            <result column="iconCls2" property="iconCls"/>
            <result column="keepAlive2" property="keepAlive"/>
            <result column="requireAuth2" property="requireAuth"/>
            <result column="parentId2" property="parentId"/>
            <result column="enabled2" property="enabled"/>
        </collection>
    </resultMap>
    <resultMap id="MenusWithRole" type="com.cxy.server.pojo.Menu" extends="BaseResultMap">
        <collection property="roles" ofType="com.cxy.server.pojo.Role">
            <id column="rid" property="id"/>
            <result column="rname" property="name"/>
            <result column="rnameZh" property="nameZh"/>
        </collection>
    </resultMap>

    <resultMap id="MenusWithChildren" type="com.cxy.server.pojo.Menu" extends="BaseResultMap">
        <id column="id1" property="id"/>
        <result column="name1" property="name"/>
        <collection property="children" ofType="com.cxy.server.pojo.Menu">
            <id column="id2" property="id"/>
            <result column="name2" property="name"/>
            <collection property="children" ofType="com.cxy.server.pojo.Menu">
                <id column="id3" property="id"/>
                <result column="name3" property="name"/>
            </collection>
        </collection>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, url, path, component, name, iconCls, keepAlive, requireAuth, parentId, enabled
    </sql>

    <!-- 根据用户 id 查询菜单列表 -->
    <select id="getMenusByAdminId" resultMap="Menus">
        SELECT DISTINCT
            m1.*, m2.id AS id2,
            m2.url AS url2,
            m2.path AS path2,
            m2.component AS component2,
            m2. NAME AS name2,
            m2.iconcls AS iconCls2,
            m2.keepalive AS keepAlive2,
            m2.requireauth AS requireAuth2,
            m2.parentid AS parentId2,
            m2.enabled AS enabled2
        FROM
            t_menu m1,
            t_menu m2,
            t_admin_role ar,
            t_menu_role mr
        WHERE
            m1.id = m2.parentid
        AND m2.id = mr.mid
        AND mr.rid = ar.rid
        AND ar.adminid = #{id}
        AND m2.enabled = TRUE
        ORDER BY
            m2.id
    </select>

    <!-- 通过角色获取菜单列表 -->
    <select id="getMenusWithRole" resultMap="MenusWithRole">
        SELECT
            m.id,
            m.url,
            m.path,
            m.component,
            m. NAME,
            m.iconCls,
            m.keepAlive,
            m.requireAuth,
            m.parentId,
            m.enabled,
            r.id AS rid,
            r. NAME AS rname,
            r.nameZh AS nameZh
        FROM
            t_menu m,
            t_menu_role mr,
            t_role r
        WHERE
            m.id = mr.mid
        AND r.id = mr.rid
        ORDER BY
            m.id
    </select>
    
    <!-- 查询所有菜单 -->
    <select id="getAllMenus" resultMap="MenusWithChildren">
        SELECT
            m1.id AS id1,
            m1. NAME AS name1,
            m2.id AS id2,
            m2. NAME AS name2,
            m3.id AS id3,
            m3. NAME AS name3
        FROM
            t_menu m1,
            t_menu m2,
            t_menu m3
        WHERE
            m1.id = m2.parentId
        AND m2.id = m3.parentId
        AND m3.enabled = TRUE
    </select>
</mapper>

6.修改菜单角色Service

修改:yeb/yeb-server/src/main/java/com/cxy/server/service/IMenuRoleService.java文件

image.png

7.修改菜单角色Service实现类

修改:yeb/yeb-server/src/main/java/com/cxy/server/service/impl/MenuRoleServiceImpl.java文件

package com.cxy.server.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cxy.server.mapper.MenuRoleMapper;
import com.cxy.server.pojo.MenuRole;
import com.cxy.server.service.IMenuRoleService;
import com.cxy.server.utils.RespBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
@Service
public class MenuRoleServiceImpl extends ServiceImpl<MenuRoleMapper, MenuRole> implements IMenuRoleService {
    @Autowired
    private MenuRoleMapper menuRoleMapper;

    /**
     * 更新角色菜单
     * 方法：
     * 1、删除此角色所有菜单；
     * 2、给该角色重新加上菜单；
     * 因是两步操作，开启事务
     *
     * @param rid
     * @param mids
     * @return
     */
    @Override
    @Transactional // 开启事务
    public RespBean updateMenuRole(Integer rid, Integer[] mids) {
        // 如果调用此接口，没传其它参数，证明是删除已有菜单
        menuRoleMapper.delete(new QueryWrapper<MenuRole>().eq("rid", rid));
        if (null == mids || 0 == mids.length) {
            return RespBean.success("更新成功!");
        }
        // 如果传参过来，新建批量更新方法，更新角色菜单
        Integer result = menuRoleMapper.insertRecord(rid, mids);
        // 返回值为改动行数,等于 mids 数组长度
        if (result == mids.length) {
            return RespBean.success("更新成功！");
        }
        return RespBean.error("更新失败！");
    }
}

7.修改菜单角色Mapper

修改:yeb/yeb-server/src/main/java/com/cxy/server/mapper/MenuRoleMapper.java文件

image.png

8.修改菜单角色xml

修改:yeb/yeb-server/src/main/java/mapper/MenuRoleMapper.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.cxy.server.mapper.MenuRoleMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.cxy.server.pojo.MenuRole">
        <id column="id" property="id"/>
        <result column="mid" property="mid"/>
        <result column="rid" property="rid"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, mid, rid
    </sql>
    <!-- 更新角色菜单 -->
    <insert id="insertRecord">
        insert into t_menu_role(mid,rid) values
        <!-- 传入的 ids 数组，用逗号分隔 ,-->
        <foreach collection="mids" item="mid" separator=",">
            (#{mid},#{rid})
        </foreach>
    </insert>
</mapper>

二.部门管理

(一)存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

1.优点

• 存储过程可封装，并隐藏复杂的商业逻辑。
• 存储过程可以回传值，并可以接受参数。
• 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
• 存储过程可以用在数据检验，强制实行商业逻辑等。

2.缺点

• 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。- - 当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
• 存储过程的性能调校与撰写，受限于各种数据库系统。

3.创建存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

4.MYSQL 存储过程中的关键语法

• 声明语句结束符，可以自定义:
  DELIMITER $$
  或
  DELIMITER //
• 声明存储过程:
  CREATE PROCEDURE demo_in_parameter(IN p_in int)
• 存储过程开始和结束符号:
  BEGIN .... END
• 变量赋值:
  SET @p_in=1
• 变量定义:
  DECLARE l_int int unsigned default 4000000;
• 创建mysql存储过程、存储函数:
  create procedure存储过程名(参数)
• 存储过程体:
  create function 存储函数名(参数)

5.存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATEPROCEDURE 存储过程名([ [ IN | OUT | INOUT ] 参数名 数据类型...])
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

注意:

• 如果过程没有参数，也必须在过程名后面写上小括号例:
  CREATE PROCEDURE sp.name([ proc_parameter[....] ]) ...
• 确保参数的名字不等于列的名字，否则在过程体中,参数名被当做列名来处理

6.变量

(1) 变量定义
局部变量声明一定要放在存储过程体的开始：
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
其中，datatype 为 MySQL 的数据类型，如: int, float, date,varchar(length)
(2) 变量赋值
SET 变量名 = 表达式值 [,variable_name = expression ...]

注意:

• 用户变量名一般以@开头
• 滥用用户变量会导致程序难以理解及管理

7.注释

MySQL 存储过程可使用两种风格的注释

• 两个横杆--风格：该风格一般用于单行注释。
• /* */风格： 一般用于多行注释。

8.MySQL存储过程的调用

用call加过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。
call sp_name[(传参)];

9.例子:

创建并使用存储过程拼接Hello,world

CREATE PROCEDURE GreetWorld() SELECT CONCAT(@gretting, 'world');
SET @gretting = 'Hello,';
CALL GreetWorld()
创建并使用存储过程p2使用p1定义的全局参数

运行结果:

image.png

CREATE PROCEDURE p1() SET @last_proceduce = 'p1';
CREATE PROCEDURE p2() SELECT CONCAT('last proceduce was: ',@last_proceduce);
CALL p1();
CALL p2();

运行结果:

image.png

(二)部门管理的常用操作

• 获取所有部门
• 添加部门
• 删除部门

(三)功能实现

1.编写存储过程

添加部门存储过程SELECT语句:

CREATE PROCEDURE addDep(IN depName VARCHAR (32),IN parentId INT,IN enabled boolean,OUT result INT,OUT result2 INT)
BEGIN
  DECLARE did INT;
  DECLARE pDepPath VARCHAR(64);
  INSERT INTO t_department SET name = depName,parentId = parentId,enabled = enabled; /* 插入depName,parentId,enabled */
  SELECT row_count() INTO result; /* result为受影响的行数(正常是1) */
  SELECT last_insert_id() INTO did; /* did设置为插入之后生成的主键id(子id) */
  SET result2 = did; /* result2为插入之后生成的主键id(子id) */
  SELECT depPath INTO pDepPath FROM t_department WHERE id = parentId; /* pDepPath设置为传入的父id所对应的depPath */
  UPDATE t_department SET depPath = concat(pDepPath,'.',did) WHERE id = did; /* 更新子id的depPath */
  UPDATE t_department SET isParent = TRUE WHERE id = parentId; /* 更新父id的isParent */
END

测试:

image.png

image.png

删除部门存储过程:

CREATE PROCEDURE deleteDep(IN did INT,OUT result INT)
BEGIN
  DECLARE ecount INT;
  DECLARE pid INT;
  DECLARE pcount INT;
  DECLARE dcount INT;
  SELECT count(*) INTO dcount FROM t_department WHERE id = did AND isParent = FALSE;
  IF dcount = 0
        THEN SET result = -2; /* 部门表中没有找到此id或此id为父id,返回-2  */
  ELSE
        SELECT count(*) INTO ecount FROM t_employee WHERE departmentId = did;
        IF ecount > 0
            THEN SET result = -1; /* 员工表中此id下面还有员工,返回-1  */
        ELSE 
            SELECT parentId INTO pid FROM t_department WHERE id = did;
            DELETE FROM t_department WHERE id = did AND isParent = FALSE;
            SELECT row_count() INTO result; /* result为受影响的行数(正常是1) */
            SELECT count(*) INTO pcount FROM t_department WHERE parentId = pid;
            IF pcount = 0
                THEN UPDATE t_department SET isParent = FALSE WHERE id = pid; /* 员工表中此id的父亲只有他一个孩子 */
            END IF;
        END IF;
  END IF;
END

测试:

image.png

image.png

2.修改部门控制器

修改:yeb/yeb-server/src/main/java/com/cxy/server/controller/DepartmentController.java

package com.cxy.server.controller;


import com.cxy.server.pojo.Department;
import com.cxy.server.service.IDepartmentService;
import com.cxy.server.utils.RespBean;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * <p>
 * 前端控制器
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
@RestController
@RequestMapping("/system/basic/department")
public class DepartmentController {
    @Autowired
    private IDepartmentService departmentService;

    @ApiOperation(value = "获取所有部门")
    @GetMapping("/")
    public List<Department> getAllDepartment() {
        return departmentService.getAllDepartment();
    }

    @ApiOperation(value = "添加部门")
    @PostMapping("/")
    public RespBean addDep(@RequestBody Department dep) {
        return departmentService.addDep(dep);
    }

    @ApiOperation(value = "删除部门")
    @DeleteMapping("/{id}")
    public RespBean deleteDep(@PathVariable Integer id) {
        return departmentService.deleteDep(id);
    }
}

3.修改部门Service

修改:yeb/yeb-server/src/main/java/com/cxy/server/service/IDepartmentService.java

package com.cxy.server.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.cxy.server.pojo.Department;
import com.cxy.server.utils.RespBean;

import java.util.List;

/**
 * <p>
 * 服务类
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
public interface IDepartmentService extends IService<Department> {
    /**
     * 获取所有部门
     *
     * @return
     */
    List<Department> getAllDepartment();

    /**
     * 添加部门
     *
     * @param dep
     * @return
     */
    RespBean addDep(Department dep);

    /**
     * 删除部门
     *
     * @param id
     * @return
     */
    RespBean deleteDep(Integer id);
}

4.修改部门Service实现类

修改:yeb/yeb-server/src/main/java/com/cxy/server/service/impl/DepartmentServiceImpl.java

package com.cxy.server.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cxy.server.mapper.DepartmentMapper;
import com.cxy.server.pojo.Department;
import com.cxy.server.service.IDepartmentService;
import com.cxy.server.utils.RespBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
@Service
public class DepartmentServiceImpl extends ServiceImpl<DepartmentMapper, Department> implements IDepartmentService {

    @Autowired
    private DepartmentMapper departmentMapper;

    /**
     * 获取所有部门
     *
     * @return
     */
    @Override
    public List<Department> getAllDepartment() {
        return departmentMapper.getAllDepartment(-1);
    }

    /**
     * 添加部门
     *
     * @param dep
     * @return
     */
    @Override
    public RespBean addDep(Department dep) {
        dep.setEnabled(true);
        departmentMapper.addDep(dep);
        if (1 == dep.getResult()) {
            return RespBean.success("添加成功!", dep);
        }
        return RespBean.error("添加失败!");
    }

    /**
     * 删除部门
     *
     * @param id
     * @return
     */
    @Override
    public RespBean deleteDep(Integer id) {
        Department dep = new Department();
        dep.setId(id);
        departmentMapper.deleteDep(dep);
        if (-2 == dep.getResult()) {
            return RespBean.error("该部门下还有子部门,删除失败!");
        }
        if (-1 == dep.getResult()) {
            return RespBean.error("该部门下还有员工,删除失败!");
        }
        if (1 == dep.getResult()) {
            return RespBean.success("删除成功!");
        }
        return RespBean.success("删除失败!");
    }
}

4.修改部门Mapper

修改:yeb/yeb-server/src/main/java/com/cxy/server/mapper/DepartmentMapper.java

package com.cxy.server.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cxy.server.pojo.Department;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
public interface DepartmentMapper extends BaseMapper<Department> {
    /**
     * 获取所有部门
     *
     * @param parentId
     * @return
     */
    List<Department> getAllDepartment(Integer parentId);

    /**
     * 添加部门
     *
     * @param dep
     * @return
     */
    void addDep(Department dep);

    /**
     * 删除部门
     *
     * @param dep
     * @return
     */
    void deleteDep(Department dep);
}

5.编写部门xml

修改:yeb/yeb-server/src/main/java/com/cxy/server/mapper/DepartmentMapper.java

<?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.cxy.server.mapper.DepartmentMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.cxy.server.pojo.Department">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="parentId" property="parentId"/>
        <result column="depPath" property="depPath"/>
        <result column="enabled" property="enabled"/>
        <result column="isParent" property="isParent"/>
    </resultMap>
    <resultMap id="DepartmentWithChildren" type="com.cxy.server.pojo.Department" extends="BaseResultMap">
        <collection property="children"
                    ofType="com.cxy.server.pojo.Department"
                    select="com.cxy.server.mapper.DepartmentMapper.getAllDepartment"
                    column="id">
        </collection>
    </resultMap>
    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name, parentId, depPath, enabled, isParent
    </sql>

    <!-- 获取所有部门 -->
    <select id="getAllDepartment" resultMap="DepartmentWithChildren">
        SELECT
            id, name, parentId, depPath, enabled, isParent
        FROM t_department
        WHERE parentId = #{parentId}
    </select>

    <!-- 添加部门 -->
    <insert id="addDep" statementType="CALLABLE">
        call addDep(#{name,mode=IN,jdbcType=VARCHAR},
                    #{parentId,mode=IN,jdbcType=INTEGER},
                    #{enabled,mode=IN,jdbcType=BOOLEAN},
                    #{result,mode=OUT,jdbcType=INTEGER},
                    #{id,mode=OUT,jdbcType=INTEGER})
    </insert>

    <!-- 删除部门 -->
    <delete id="deleteDep" statementType="CALLABLE">
        call deleteDep(#{id,mode=IN,jdbcType=INTEGER},
                    #{result,mode=OUT,jdbcType=INTEGER})
    </delete>
</mapper>

三.管理员管理

(一)管理员管理的常用操作

• 获取所有管理员
• 更新管理员
• 更新管理员角色
• 获取所有角色
• 删除管理员

(二)功能实现

1.修改管理员控制器

修改:yeb/yeb-server/src/main/java/com/cxy/server/controller/AdminController.java文件

package com.cxy.server.controller;


import com.cxy.server.pojo.Admin;
import com.cxy.server.pojo.Role;
import com.cxy.server.service.IAdminService;
import com.cxy.server.service.IRoleService;
import com.cxy.server.utils.RespBean;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * <p>
 * 前端控制器
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
@Api(tags = "管理员接口")
@RestController
@RequestMapping("/system/admin")
public class AdminController {
    @Autowired
    private IAdminService adminService;

    @Autowired
    private IRoleService roleService;

    @ApiOperation(value = "获取所有管理员")
    @GetMapping("/")
    public List<Admin> getAllAdmins(String keywords) {
        return adminService.getAllAdmins(keywords);
    }

    @ApiOperation(value = "更新管理员")
    @PutMapping("/")
    public RespBean updateAdmin(@RequestBody Admin admin) {
        if (adminService.updateById(admin)) {
            return RespBean.success("更新成功!");
        }
        return RespBean.error("更新失败");
    }

    @ApiOperation(value = "删除管理员")
    @DeleteMapping("/{id}")
    public RespBean deleteAdmin(@PathVariable Integer id) {
        if (adminService.removeById(id)) {
            return RespBean.success("删除成功!");
        }
        return RespBean.error("删除失败");
    }

    @ApiOperation(value = "获取所有角色")
    @GetMapping("/roles")
    public List<Role> getAllRoles() {
        return roleService.list();
    }

    @ApiOperation(value = "更新管理员角色")
    @PutMapping("/role")
    public RespBean updateAdminRole(Integer adminId, Integer[] rids) {
        return adminService.updateAdminRole(adminId, rids);
    }
}

• 更新管理员、获取所有角色、删除管理员
  不需要自己写Service和Mapper层
• 获取所有管理员需要自己写IAdminService和AdminMapper、
• 更新管理员角色需要自己写IAdminService和AdminRoleMapper

2.修改管理员Service

修改:yeb/yeb-server/src/main/java/com/cxy/server/service/IAdminService.java

package com.cxy.server.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.cxy.server.pojo.Admin;
import com.cxy.server.pojo.Role;
import com.cxy.server.utils.RespBean;

import javax.servlet.http.HttpServletRequest;
import java.util.List;

/**
 * <p>
 * 服务类
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
public interface IAdminService extends IService<Admin> {

    /**
     * 登录之后返回 token
     *
     * @param username
     * @param password
     * @param code
     * @param request
     * @return
     */
    RespBean login(String username, String password, String code, HttpServletRequest request);

    /**
     * 根据用户名获取用户
     *
     * @param username
     * @return
     */
    Admin getAdminByUserName(String username);

    /**
     * 根据用户id查询角色列表
     *
     * @param adminId
     * @return
     */
    List<Role> getRoles(Integer adminId);

    /**
     * 获取所有管理员
     *
     * @param keywords
     * @return
     */
    List<Admin> getAllAdmins(String keywords);

    /**
     * 更新管理员角色
     *
     * @param adminId
     * @param rids
     * @return
     */
    RespBean updateAdminRole(Integer adminId, Integer[] rids);
}

3.修改管理员Service实现类

修改:yeb/yeb-server/src/main/java/com/cxy/server/service/impl/AdminServiceImpl.java文件

package com.cxy.server.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cxy.server.config.security.component.JwtTokenUtil;
import com.cxy.server.mapper.AdminMapper;
import com.cxy.server.mapper.AdminRoleMapper;
import com.cxy.server.mapper.RoleMapper;
import com.cxy.server.pojo.Admin;
import com.cxy.server.pojo.AdminRole;
import com.cxy.server.pojo.Role;
import com.cxy.server.service.IAdminService;
import com.cxy.server.utils.RespBean;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
@Service
public class AdminServiceImpl extends ServiceImpl<AdminMapper, Admin> implements IAdminService {
    @Autowired
    private UserDetailsService userDetailsService; // 权限框架的

    @Autowired
    private PasswordEncoder passwordEncoder; // 安全框架-密码加密解密

    @Autowired
    private JwtTokenUtil jwtTokenUtil;

    @Value("${jwt.tokenHead}")
    private String tokenHead; // token 头部信息

    @Autowired
    private AdminMapper adminMapper;

    @Autowired
    private RoleMapper roleMapper;

    @Autowired
    private AdminRoleMapper adminRoleMapper;

    /**
     * 登录之后返回 token
     *
     * @param username
     * @param password
     * @param code
     * @param request
     * @return
     */
    @Override
    public RespBean login(String username, String password, String code, HttpServletRequest request) {
        // 校验验证码
        String captcha = (String) request.getSession().getAttribute("captcah");
        if (null == captcha) {
            return RespBean.error("请先获取验证码！");
        }
        if (StringUtils.isEmpty(code) || !captcha.equalsIgnoreCase(code)) {
            return RespBean.error("验证码输入错误，请重新输入！");
        }
        // 登录
        try {
            userDetailsService.loadUserByUsername(username); // 调用权限框架方法获取用户名
        } catch (UsernameNotFoundException e) {
            // 未找到该用户
            return RespBean.error("用户名或密码不正确！");
        }
        UserDetails userDetails = userDetailsService.loadUserByUsername(username); // 调用权限框架方法获取用户名
        // passwordEncoder参数：第一个用户传过来的密码，第二个从 userDetails 中获取的密码
        if (null == userDetails || !passwordEncoder.matches(password, userDetails.getPassword())) {
            // 密码输错了
            return RespBean.error("用户名或密码不正确！");
        }
        if (!userDetails.isEnabled()) {
            return RespBean.error("账号被禁用，请联系管理员！");
        }
        // 更新 security 登录用户对象，设置到全局
        UsernamePasswordAuthenticationToken authenticationToken = new UsernamePasswordAuthenticationToken(userDetails
                , null, userDetails.getAuthorities());
        SecurityContextHolder.getContext().setAuthentication(authenticationToken);

        // 登录成功，生成 token
        String token = jwtTokenUtil.generateToken(userDetails);
        Map<String, String> tokenMap = new HashMap<>();
        tokenMap.put("tokenHead", tokenHead);
        tokenMap.put("token", token);
        return RespBean.success("登录成功！", tokenMap);
    }

    /**
     * 根据用户名获取用户
     *
     * @param username
     * @return
     */
    @Override
    public Admin getAdminByUserName(String username) {
        // 要作空判断
        return adminMapper.selectOne(new QueryWrapper<Admin>().eq("username", username));
    }

    /**
     * 根据用户id查询角色列表
     *
     * @param adminId
     * @return
     */
    @Override
    public List<Role> getRoles(Integer adminId) {
        return roleMapper.getRoles(adminId);
    }

    /**
     * 获取所有管理员
     *
     * @param keywords
     * @return
     */
    @Override
    public List<Admin> getAllAdmins(String keywords) {
        return adminMapper.getAllAdmins(((Admin) SecurityContextHolder
                .getContext()
                .getAuthentication()
                .getPrincipal()).getId(), keywords);
    }

    /**
     * 更新管理员角色
     *
     * @param adminId
     * @param rids
     * @return
     */
    @Override
    @Transactional
    public RespBean updateAdminRole(Integer adminId, Integer[] rids) {
        adminRoleMapper.delete(new QueryWrapper<AdminRole>().eq("adminId", adminId));
        Integer result = adminRoleMapper.updateAdminRole(adminId, rids);
        if (rids.length == result) {
            return RespBean.success("更新成功!");
        }
        return RespBean.error("更新失败!");
    }
}

4.修改管理员Mapper

修改:yeb/yeb-server/src/main/java/com/cxy/server/mapper/AdminMapper.java文件

image.png

5.修改管理员xml

修改:yeb/yeb-server/src/main/resource/mapper/AdminMapper.xmla文件

<?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">
<!--suppress ALL -->
<mapper namespace="com.cxy.server.mapper.AdminMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.cxy.server.pojo.Admin">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="phone" property="phone"/>
        <result column="telephone" property="telephone"/>
        <result column="address" property="address"/>
        <result column="enabled" property="enabled"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="userFace" property="userFace"/>
        <result column="remark" property="remark"/>
    </resultMap>
    <resultMap id="AdminWithRole" type="com.cxy.server.pojo.Admin" extends="BaseResultMap">
        <collection property="roles" ofType="com.cxy.server.pojo.Role">
            <id column="rid" property="id"/>
            <result column="rname" property="name"/>
            <result column="rnameZh" property="nameZh"/>
        </collection>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name, phone, telephone, address, enabled, username, password, userFace, remark
    </sql>

    <!-- 获取所有管理员 -->
    <select id="getAllAdmins" resultMap="AdminWithRole">
        SELECT
        a.id,
        a.name,
        phone,
        telephone,
        address,
        enabled,
        username,
        password,
        userFace,
        remark,
        r.id AS rid,
        r.name AS rname,
        r.nameZh AS rnameZh
        FROM
        t_admin a
        LEFT JOIN t_admin_role ar ON a.id = ar.adminId
        LEFT JOIN t_role r ON r.id = ar.rid
        WHERE
        a.id != #{id}
        <if test="null!=keywords and ''!=keywords">
            AND a.name LIKE CONCAT('%', #{keywords}, '%')
        </if>
        ORDER BY
        a.id
    </select>

</mapper>

6.修改管理员角色Mapper

修改:yeb/yeb-server/src/main/java/com/cxy/server/mapper/AdminRoleMapper.java文件

image.png

7.修改管理员角色xml

修改:yeb/yeb-server/src/main/resource/mapper/AdminRoleMapper.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.cxy.server.mapper.AdminRoleMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.cxy.server.pojo.AdminRole">
        <id column="id" property="id"/>
        <result column="adminId" property="adminId"/>
        <result column="rid" property="rid"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, adminId, rid
    </sql>
    <!-- 更新管理员角色 -->
    <update id="updateAdminRole">
        INSERT INTO t_admin_role(adminId, rid) VALUES
        <foreach collection="rids" item="rid" separator=",">
            (#{adminId},#{rid})
        </foreach>
    </update>

</mapper>

四.员工管理

(一)员工管理的常用操作

• 获取所有员工(分页)
• 获取所有政治面貌
• 获取所有职称
• 获取所有民族
• 获取所有职位
• 获取所有部门
• 获取工号
• 添加员工
• 更新员工
• 删除员工
• 导出员工数据
• 导入员工数据

(二)功能实现

1.修改员工控制器

修改:yeb/yeb-server/src/main/java/com/cxy/server/controller/EmployeeController.java文件

package com.cxy.server.controller;


import com.cxy.server.pojo.*;
import com.cxy.server.service.*;
import com.cxy.server.utils.RespBean;
import com.cxy.server.utils.RespPageBean;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.time.LocalDate;
import java.util.List;

/**
 * <p>
 * 前端控制器
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
@Api(tags = "员工控制器")
@RestController
@RequestMapping("/employee/basic")
public class EmployeeController {
    @Autowired
    private IEmployeeService employeeService;

    @Autowired
    private IPoliticsStatusService politicsStatusService;

    @Autowired
    private IJoblevelService joblevelService;

    @Autowired
    private INationService nationService;

    @Autowired
    private IPositionService positionService;

    @Autowired
    private IDepartmentService departmentService;

    @ApiOperation(value = "获取所有员工(分页)")
    @GetMapping("/")
    public RespPageBean getEmployee(@RequestParam(defaultValue = "1") Integer currentPage,
                                    @RequestParam(defaultValue = "10") Integer size,
                                    Employee employee, LocalDate[] beginDateScope) {
        return employeeService.getEmployeeByPage(currentPage, size, employee, beginDateScope);
    }

    @ApiOperation(value = "获取所有政治面貌")
    @GetMapping("/politicsstatus")
    public List<PoliticsStatus> getAllPoliticsStatus() {
        return politicsStatusService.list();
    }

    @ApiOperation(value = "获取所有职称")
    @GetMapping("/joblevels")
    public List<Joblevel> getAllJoblevels() {
        return joblevelService.list();
    }

    @ApiOperation(value = "获取所有民族")
    @GetMapping("/nations")
    public List<Nation> getAllNations() {
        return nationService.list();
    }

    @ApiOperation(value = "获取所有职位")
    @GetMapping("/positions")
    public List<Position> getAllPositions() {
        return positionService.list();
    }

    @ApiOperation(value = "获取所有部门")
    @GetMapping("/deps")
    public List<Department> getAllDeps() {
        return departmentService.getAllDepartments();
    }

    @ApiOperation(value = "获取工号")
    @GetMapping("/maxworkid")
    public RespBean maxWorkID() {
        return employeeService.maxWorkID();
    }

    @ApiOperation(value = "添加员工")
    @PostMapping("/")
    public RespBean addEmp(@RequestBody Employee employee) {
        return employeeService.addEmp(employee);
    }

    @ApiOperation(value = "更新员工")
    @PutMapping("/")
    public RespBean updateEmp(@RequestBody Employee employee) {
        if (employeeService.updateById(employee)) {
            return RespBean.success("更新成功!");
        }
        return RespBean.error("更新失败!");
    }

    @ApiOperation(value = "删除员工")
    @DeleteMapping("/{id}")
    public RespBean deleteEmp(@PathVariable Integer id) {
        if (employeeService.removeById(id)) {
            return RespBean.success("删除成功!");
        }
        return RespBean.error("删除失败!");
    }
}

2.修改员工控制器

修改:yeb/yeb-server/src/main/java/com/cxy/server/controller/EmployeeController.java文件

package com.cxy.server.controller;


import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.cxy.server.pojo.*;
import com.cxy.server.service.*;
import com.cxy.server.utils.RespBean;
import com.cxy.server.utils.RespPageBean;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.List;

/**
 * <p>
 * 前端控制器
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
@Api(tags = "员工控制器")
@RestController
@RequestMapping("/employee/basic")
public class EmployeeController {
    @Autowired
    private IEmployeeService employeeService;

    @Autowired
    private IPoliticsStatusService politicsStatusService;

    @Autowired
    private IJoblevelService joblevelService;

    @Autowired
    private INationService nationService;

    @Autowired
    private IPositionService positionService;

    @Autowired
    private IDepartmentService departmentService;

    @ApiOperation(value = "获取所有员工(分页)")
    @GetMapping("/")
    public RespPageBean getEmployee(@RequestParam(defaultValue = "1") Integer currentPage,
                                    @RequestParam(defaultValue = "10") Integer size,
                                    Employee employee, LocalDate[] beginDateScope) {
        return employeeService.getEmployeeByPage(currentPage, size, employee, beginDateScope);
    }

    @ApiOperation(value = "获取所有政治面貌")
    @GetMapping("/politicsstatus")
    public List<PoliticsStatus> getAllPoliticsStatus() {
        return politicsStatusService.list();
    }

    @ApiOperation(value = "获取所有职称")
    @GetMapping("/joblevels")
    public List<Joblevel> getAllJoblevels() {
        return joblevelService.list();
    }

    @ApiOperation(value = "获取所有民族")
    @GetMapping("/nations")
    public List<Nation> getAllNations() {
        return nationService.list();
    }

    @ApiOperation(value = "获取所有职位")
    @GetMapping("/positions")
    public List<Position> getAllPositions() {
        return positionService.list();
    }

    @ApiOperation(value = "获取所有部门")
    @GetMapping("/deps")
    public List<Department> getAllDeps() {
        return departmentService.getAllDepartments();
    }

    @ApiOperation(value = "获取工号")
    @GetMapping("/maxworkid")
    public RespBean maxWorkID() {
        return employeeService.maxWorkID();
    }

    @ApiOperation(value = "添加员工")
    @PostMapping("/")
    public RespBean addEmp(@RequestBody Employee employee) {
        return employeeService.addEmp(employee);
    }

    @ApiOperation(value = "更新员工")
    @PutMapping("/")
    public RespBean updateEmp(@RequestBody Employee employee) {
        if (employeeService.updateById(employee)) {
            return RespBean.success("更新成功!");
        }
        return RespBean.error("更新失败!");
    }

    @ApiOperation(value = "删除员工")
    @DeleteMapping("/{id}")
    public RespBean deleteEmp(@PathVariable Integer id) {
        if (employeeService.removeById(id)) {
            return RespBean.success("删除成功!");
        }
        return RespBean.error("删除失败!");
    }

    @ApiOperation(value = "导出员工数据")
    @GetMapping(value = "/export", produces = "application/octet-stream")
    public void exportEmployee(HttpServletResponse response) {
        List<Employee> list = employeeService.getEmployee(null);
        // 03版的 HSSF ，兼容性好一点； 还有一个07版的 XSSF
        ExportParams params = new ExportParams("员工表", "员工表", ExcelType.HSSF);
        // 导出工具类
        Workbook workbook = ExcelExportUtil.exportExcel(params, Employee.class, list);
        ServletOutputStream out = null;
        try {
            // 流形式导出
            response.setHeader("content-type", "application/octet-stream");
            // 防止中文乱码
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("员工表.xls", "UTF-8"));
            out = response.getOutputStream();
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != out) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @ApiOperation(value = "导入员工数据")
    @PostMapping("/import")
    public RespBean importEmployee(MultipartFile file) {
        ImportParams params = new ImportParams();
        // 去掉标题行
        params.setTitleRows(1);
        // 民族列表
        List<Nation> nationList = nationService.list();
        // 政治面貌列表
        List<PoliticsStatus> politicsStatusesList = politicsStatusService.list();
        // 部门列表
        List<Department> departmentsList = departmentService.list();
        // 职称列表
        List<Joblevel> joblevelsList = joblevelService.list();
        // 职位列表
        List<Position> positionsList = positionService.list();
        try {
            List<Employee> list = ExcelImportUtil.importExcel(file.getInputStream(), Employee.class, params);
            list.forEach(employee -> {
                // 民族 id
                // 1. 通过 name 获取 inputNation (一个只有 name 且 id 为空的对象)
                Nation inputNation = new Nation(employee.getNation().getName());
                // 2. 通过 inputNation 获取对应的下标，通过下标获取完整的对象，通过对象获取 id,
                employee.setNationId(nationList.get(nationList.indexOf(inputNation)).getId());
                // 政治面貌 id
                employee.setPoliticId(politicsStatusesList.get(politicsStatusesList.indexOf(new PoliticsStatus(employee.getPoliticsStatus().getName()))).getId());
                // 部门 id
                employee.setDepartmentId(departmentsList.get(departmentsList.indexOf(new Department(employee.getDepartment().getName()))).getId());
                // 职称 id
                employee.setJobLevelId(joblevelsList.get(joblevelsList.indexOf(new Joblevel(employee.getJoblevel().getName()))).getId());
                // 职位 id
                employee.setPosId(positionsList.get(positionsList.indexOf(new Position(employee.getPosition().getName()))).getId());

            });
            if (employeeService.saveBatch(list)) {
                return RespBean.success("导入成功!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return RespBean.error("导入失败!");
    }
}

3.修改员工Service

修改:yeb/yeb-server/src/main/java/com/cxy/server/service/IEmployeeService.java文件

package com.cxy.server.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.cxy.server.pojo.Employee;
import com.cxy.server.utils.RespBean;
import com.cxy.server.utils.RespPageBean;

import java.time.LocalDate;
import java.util.List;

/**
 * <p>
 * 服务类
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
public interface IEmployeeService extends IService<Employee> {
    /**
     * 获取所有员工(分页)
     *
     * @param currentPage
     * @param size
     * @param employee
     * @param beginDateScope
     * @return
     */
    RespPageBean getEmployeeByPage(Integer currentPage, Integer size, Employee employee, LocalDate[] beginDateScope);

    /**
     * 获取工号
     *
     * @return
     */
    RespBean maxWorkID();

    /**
     * 添加员工
     *
     * @param employee
     * @return
     */
    RespBean addEmp(Employee employee);

    /**
     * 查询员工
     *
     * @param id
     * @return
     */
    List<Employee> getEmployee(Integer id);
}

4.修改员工Service实现类

修改:yeb/yeb-server/src/main/java/com/cxy/server/service/impl/EmployeeServiceImpl.java文件

package com.cxy.server.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cxy.server.mapper.EmployeeMapper;
import com.cxy.server.mapper.MailLogMapper;
import com.cxy.server.pojo.Employee;
import com.cxy.server.pojo.MailLog;
import com.cxy.server.service.IEmployeeService;
import com.cxy.server.utils.MailConstants;
import com.cxy.server.utils.RespBean;
import com.cxy.server.utils.RespPageBean;
import org.springframework.amqp.rabbit.connection.CorrelationData;
import org.springframework.amqp.rabbit.core.RabbitTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;
import java.util.List;
import java.util.Map;
import java.util.UUID;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
@Service
public class EmployeeServiceImpl extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeService {

    @Autowired
    private EmployeeMapper employeeMapper;

    @Autowired
    private MailLogMapper mailLogMapper;

    @Autowired
    private RabbitTemplate rabbitTemplate;

    /**
     * 获取所有员工(分页)
     *
     * @param currentPage
     * @param size
     * @param employee
     * @param beginDateScope
     * @return
     */
    @Override
    public RespPageBean getEmployeeByPage(Integer currentPage, Integer size, Employee employee, LocalDate[] beginDateScope) {
        Page<Employee> page = new Page<>(currentPage, size);
        IPage<Employee> employeeByPage = employeeMapper.getEmployeeByPage(page, employee, beginDateScope);
        RespPageBean respPageBean = new RespPageBean(employeeByPage.getTotal(), employeeByPage.getRecords());
        return respPageBean;
    }

    /**
     * 获取工号
     *
     * @return
     */
    @Override
    public RespBean maxWorkID() {
        List<Map<String, Object>> maps = employeeMapper.selectMaps(new QueryWrapper<Employee>()
                .select("max(workID)"));
        //  String.format(" %08d " = 获得固定8位长度的数字 （ 不足的前面补0 ）
        String workID = String.format("%08d", Integer.parseInt(maps.get(0).get("max(workID)").toString()) + 1);
        if (null != workID) {
            return RespBean.success("获取员工工号成功!", workID);
        }
        return RespBean.error("获取员工工失败!");
    }

    /**
     * 添加员工
     *
     * @param employee
     * @return
     */
    @Override
    public RespBean addEmp(Employee employee) {
        // 处理合同期限，保留两位小数
        LocalDate beginContract = employee.getBeginContract();// 合同开始时间
        LocalDate endContract = employee.getEndContract();// 合同结束时间
        // 计算两个日期相差多少天
        long days = beginContract.until(endContract, ChronoUnit.DAYS);
        // 保留两位小数
        DecimalFormat decimalFormat = new DecimalFormat("##.00");
        // 计算以年为单位
        employee.setContractTerm(Double.parseDouble(decimalFormat.format(days / 365.00)));
        if (1 == employeeMapper.insert(employee)) {
            // 获取当前行添加员工记录
            Employee emp = employeeMapper.getEmployee(employee.getId()).get(0);
            String msgId = UUID.randomUUID().toString();
            // String msgId = "123456";
            MailLog mailLog = new MailLog();
            // 消息id: 为随机生成的UUID
            mailLog.setMsgId(msgId);
            // 接收员工id
            mailLog.setEid(employee.getId());
            // 状态（0: 消息投递中 1: 投递成功 2: 投递失败）
            mailLog.setStatus(0);
            // 路由键
            mailLog.setRouteKey(MailConstants.MAIL_ROUTING_KEY_NAME);
            // 交换机
            mailLog.setExchange(MailConstants.MAIL_EXCHANGE_NAME);
            // 重试次数
            mailLog.setCount(0);
            // 重试时间: 当前时间加1分钟
            mailLog.setTryTime(LocalDateTime.now().plusMinutes(MailConstants.MSG_TIMEOUT));
            // 创建时间
            mailLog.setCreateTime(LocalDateTime.now());
            // 更新时间
            mailLog.setUpdateTime(LocalDateTime.now());
            // 把设置的数据插入数据表
            mailLogMapper.insert(mailLog);
// 发送信息
            rabbitTemplate.convertAndSend(MailConstants.MAIL_EXCHANGE_NAME, MailConstants.MAIL_ROUTING_KEY_NAME, emp,
                    new CorrelationData(msgId)); // mq 路由 key
            return RespBean.success("添加成功！");
        }
        return RespBean.error("添加失败!");

    }

    /**
     * 查询员工
     *
     * @param id
     * @return
     */
    @Override
    public List<Employee> getEmployee(Integer id) {
        return employeeMapper.getEmployee(id);
    }
}

5.修改员工Mapper

修改:yeb/yeb-server/src/main/java/com/cxy/server/mapper/EmployeeMapper.java文件

package com.cxy.server.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.cxy.server.pojo.Employee;
import org.apache.ibatis.annotations.Param;

import java.time.LocalDate;
import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author 陈鑫元
 * @since 2021-05-21
 */
public interface EmployeeMapper extends BaseMapper<Employee> {

    /**
     * 获取所有员工(分页)
     *
     * @param page
     * @param employee
     * @param beginDateScope
     * @return
     */
    IPage<Employee> getEmployeeByPage(Page<Employee> page, @Param("employee") Employee employee, @Param("beginDateScope") LocalDate[] beginDateScope);

    /**
     * 查询员工
     *
     * @param id
     * @return
     */
    List<Employee> getEmployee(Integer id);
}

6.修改员工xml

修改:yeb/yeb-server/src/main/resources/mapper/EmployeeMapper.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.cxy.server.mapper.EmployeeMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.cxy.server.pojo.Employee">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="gender" property="gender"/>
        <result column="birthday" property="birthday"/>
        <result column="idCard" property="idCard"/>
        <result column="wedlock" property="wedlock"/>
        <result column="nationId" property="nationId"/>
        <result column="nativePlace" property="nativePlace"/>
        <result column="politicId" property="politicId"/>
        <result column="email" property="email"/>
        <result column="phone" property="phone"/>
        <result column="address" property="address"/>
        <result column="departmentId" property="departmentId"/>
        <result column="jobLevelId" property="jobLevelId"/>
        <result column="posId" property="posId"/>
        <result column="engageForm" property="engageForm"/>
        <result column="tiptopDegree" property="tiptopDegree"/>
        <result column="specialty" property="specialty"/>
        <result column="school" property="school"/>
        <result column="beginDate" property="beginDate"/>
        <result column="workState" property="workState"/>
        <result column="workID" property="workID"/>
        <result column="contractTerm" property="contractTerm"/>
        <result column="conversionTime" property="conversionTime"/>
        <result column="notWorkDate" property="notWorkDate"/>
        <result column="beginContract" property="beginContract"/>
        <result column="endContract" property="endContract"/>
        <result column="workAge" property="workAge"/>
        <result column="salaryId" property="salaryId"/>
    </resultMap>

    <resultMap id="EmployeeInfo" type="com.cxy.server.pojo.Employee" extends="BaseResultMap">
        <association property="nation" javaType="com.cxy.server.pojo.Nation">
            <id column="nid" property="id"/>
            <result column="nname" property="name"/>
        </association>
        <association property="politicsStatus" javaType="com.cxy.server.pojo.PoliticsStatus">
            <id column="pid" property="id"/>
            <result column="pname" property="name"/>
        </association>
        <association property="department" javaType="com.cxy.server.pojo.Department">
            <id column="did" property="id"/>
            <result column="dname" property="name"/>
        </association>
        <association property="joblevel" javaType="com.cxy.server.pojo.Joblevel">
            <id column="jid" property="id"/>
            <result column="jname" property="name"/>
        </association>
        <association property="position" javaType="com.cxy.server.pojo.Position">
            <id column="posid" property="id"/>
            <result column="posname" property="name"/>
        </association>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name, gender, birthday, idCard, wedlock, nationId, nativePlace, politicId, email, phone, address, departmentId, jobLevelId, posId, engageForm, tiptopDegree, specialty, school, beginDate, workState, workID, contractTerm, conversionTime, notWorkDate, beginContract, endContract, workAge, salaryId
    </sql>

    <!-- 获取所有员工(分页) -->
    <select id="getEmployeeByPage" resultMap="EmployeeInfo">
        SELECT
        e.id,
        e.name,
        gender,
        birthday,
        idCard,
        wedlock,
        nationId,
        nativePlace,
        politicId,
        email,
        phone,
        address,
        departmentId,
        jobLevelId,
        posId,
        engageForm,
        tiptopDegree,
        specialty,
        school,
        beginDate,
        workState,
        workID,
        contractTerm,
        conversionTime,
        notWorkDate,
        beginContract,
        endContract,
        workAge,
        salaryId,
        n.id AS nid,
        n.name AS nname,
        p.id AS pid,
        p.name AS pname,
        d.id AS did,
        d.name AS dname,
        j.id AS jid,
        j.name AS jname,
        pos.id AS posid,
        pos.name AS posname
        FROM
        t_employee e,
        t_nation n,
        t_politics_status p,
        t_department d,
        t_joblevel j,
        t_position pos
        WHERE
        e.nationId = n.id
        AND e.politicId = p.id
        AND e.departmentId = d.id
        AND e.jobLevelId = j.id
        AND e.posId = pos.id
        <!-- employee.name(员工姓名) 实体类属性 -->
        <if test="null!=employee.name and ''!=employee.name">
            AND e.name LIKE CONCAT('%', #{employee.name}, '%')
        </if>
        <!-- employee.politicId(政治面貌) 实体类属性 -->
        <if test="null!=employee.politicId and ''!=employee.politicId">
            AND e.politicId = #{employee.politicId}
        </if>
        <!-- employee.nationId(民族) 实体类属性 -->
        <if test="null!=employee.nationId and ''!=employee.nationId">
            AND nationId = #{employee.nationId}
        </if>
        <!-- employee.jobLevelId(职称) 实体类属性 -->
        <if test="null!=employee.jobLevelId and ''!=employee.jobLevelId">
            AND e.jobLevelId = #{employee.jobLevelId}
        </if>
        <!-- employee.posId(职位) 实体类属性 -->
        <if test="null!=employee.posId and ''!=employee.posId">
            AND e.posId = #{employee.posId}
        </if>
        <!-- employee.engageForm(聘用形式) 实体类属性 -->
        <if test="null!=employee.engageForm and ''!=employee.engageForm">
            AND e.engageForm = #{employee.engageForm}
        </if>
        <!-- employee.departmentId(所属部门) 实体类属性 -->
        <if test="null!=employee.departmentId and ''!=employee.departmentId">
            AND e.departmentId = #{employee.departmentId}
        </if>
        <!-- beginDateScope(beginDateScope[0]~beginDateScope[1]: 入职日期范围) 控制器中定义的日期数组参数名 -->
        <if test="null!=beginDateScope and 2==beginDateScope.length">
            AND e.beginDate BETWEEN #{beginDateScope[0]} AND #{beginDateScope[1]}
        </if>
        ORDER BY e.id
    </select>

    <!-- 查询员工 -->
    <select id="getEmployee" resultMap="EmployeeInfo">
        SELECT
        e.id,
        e.name,
        gender,
        birthday,
        idCard,
        wedlock,
        nationId,
        nativePlace,
        politicId,
        email,
        phone,
        address,
        departmentId,
        jobLevelId,
        posId,
        engageForm,
        tiptopDegree,
        specialty,
        school,
        beginDate,
        workState,
        workID,
        contractTerm,
        conversionTime,
        notWorkDate,
        beginContract,
        endContract,
        workAge,
        salaryId,
        n.id AS nid,
        n.name AS nname,
        p.id AS pid,
        p.name AS pname,
        d.id AS did,
        d.name AS dname,
        j.id AS jid,
        j.name AS jname,
        pos.id AS posid,
        pos.name AS posname
        FROM
        t_employee e,
        t_nation n,
        t_politics_status p,
        t_department d,
        t_joblevel j,
        t_position pos
        WHERE
        e.nationId = n.id
        AND e.politicId = p.id
        AND e.departmentId = d.id
        AND e.jobLevelId = j.id
        AND e.posId = pos.id
        <if test="null!=id">
            AND e.id = #{id}
        </if>
        ORDER BY e.id
    </select>
</mapper>