一.權(quán)限組

(一)權(quán)限組的常用操作

• 獲取所有角色
• 添加角色
• 刪除角色
• 查詢所有菜單
• 根據(jù)角色 id 查詢菜單 id
• 更新角色菜單

(二)功能實(shí)現(xiàn)

1.修改權(quán)限組控制器

修改: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 權(quán)限組
 * @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 = "根據(jù)角色 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);
    }
}

• 獲取所有角色、添加角色、刪除角色、根據(jù)角色 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實(shí)現(xiàn)類

修改: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">

    <!-- 通用查詢映射結(jié)果 -->
    <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>

    <!-- 通用查詢結(jié)果列 -->
    <sql id="Base_Column_List">
        id, url, path, component, name, iconCls, keepAlive, requireAuth, parentId, enabled
    </sql>

    <!-- 根據(jù)用戶 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>

    <!-- 通過(guò)角色獲取菜單列表 -->
    <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實(shí)現(xiàn)類

修改: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>
 * 服務(wù)實(shí)現(xiàn)類
 * </p>
 *
 * @author 陳鑫元
 * @since 2021-05-21
 */
@Service
public class MenuRoleServiceImpl extends ServiceImpl<MenuRoleMapper, MenuRole> implements IMenuRoleService {
    @Autowired
    private MenuRoleMapper menuRoleMapper;

    /**
     * 更新角色菜單
     * 方法：
     * 1、刪除此角色所有菜單；
     * 2、給該角色重新加上菜單；
     * 因是兩步操作，開啟事務(wù)
     *
     * @param rid
     * @param mids
     * @return
     */
    @Override
    @Transactional // 開啟事務(wù)
    public RespBean updateMenuRole(Integer rid, Integer[] mids) {
        // 如果調(diào)用此接口，沒(méi)傳其它參數(shù)，證明是刪除已有菜單
        menuRoleMapper.delete(new QueryWrapper<MenuRole>().eq("rid", rid));
        if (null == mids || 0 == mids.length) {
            return RespBean.success("更新成功!");
        }
        // 如果傳參過(guò)來(lái)，新建批量更新方法，更新角色菜單
        Integer result = menuRoleMapper.insertRecord(rid, mids);
        // 返回值為改動(dòng)行數(shù),等于 mids 數(shù)組長(zhǎng)度
        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">

    <!-- 通用查詢映射結(jié)果 -->
    <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>

    <!-- 通用查詢結(jié)果列 -->
    <sql id="Base_Column_List">
        id, mid, rid
    </sql>
    <!-- 更新角色菜單 -->
    <insert id="insertRecord">
        insert into t_menu_role(mid,rid) values
        <!-- 傳入的 ids 數(shù)組，用逗號(hào)分隔 ,-->
        <foreach collection="mids" item="mid" separator=",">
            (#{mid},#{rid})
        </foreach>
    </insert>
</mapper>

二.部門管理

(一)存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程（Stored Procedure）是在大型數(shù)據(jù)庫(kù)系統(tǒng)中，一組為了完成特定功能的 SQL 語(yǔ)句集，它存儲(chǔ)在數(shù)據(jù)庫(kù)中，一次編譯后永久有效，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)中的一個(gè)重要對(duì)象。在數(shù)據(jù)量特別龐大的情況下利用存儲(chǔ)過(guò)程能達(dá)到倍速的效率提升。

1.優(yōu)點(diǎn)

• 存儲(chǔ)過(guò)程可封裝，并隱藏復(fù)雜的商業(yè)邏輯。
• 存儲(chǔ)過(guò)程可以回傳值，并可以接受參數(shù)。
• 存儲(chǔ)過(guò)程無(wú)法使用 SELECT 指令來(lái)運(yùn)行，因?yàn)樗亲映绦?，與查看表，數(shù)據(jù)表或用戶定義函數(shù)不同。
• 存儲(chǔ)過(guò)程可以用在數(shù)據(jù)檢驗(yàn)，強(qiáng)制實(shí)行商業(yè)邏輯等。

2.缺點(diǎn)

• 存儲(chǔ)過(guò)程，往往定制化于特定的數(shù)據(jù)庫(kù)上，因?yàn)橹С值木幊陶Z(yǔ)言不同。- - 當(dāng)切換到其他廠商的數(shù)據(jù)庫(kù)系統(tǒng)時(shí)，需要重寫原有的存儲(chǔ)過(guò)程。
• 存儲(chǔ)過(guò)程的性能調(diào)校與撰寫，受限于各種數(shù)據(jù)庫(kù)系統(tǒng)。

3.創(chuàng)建存儲(chǔ)過(guò)程

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 存儲(chǔ)過(guò)程中的關(guān)鍵語(yǔ)法

• 聲明語(yǔ)句結(jié)束符，可以自定義:
  DELIMITER $$
  或
  DELIMITER //
• 聲明存儲(chǔ)過(guò)程:
  CREATE PROCEDURE demo_in_parameter(IN p_in int)
• 存儲(chǔ)過(guò)程開始和結(jié)束符號(hào):
  BEGIN .... END
• 變量賦值:
  SET @p_in=1
• 變量定義:
  DECLARE l_int int unsigned default 4000000;
• 創(chuàng)建mysql存儲(chǔ)過(guò)程、存儲(chǔ)函數(shù):
  create procedure存儲(chǔ)過(guò)程名(參數(shù))
• 存儲(chǔ)過(guò)程體:
  create function 存儲(chǔ)函數(shù)名(參數(shù))

5.存儲(chǔ)過(guò)程的參數(shù)

MySQL存儲(chǔ)過(guò)程的參數(shù)用在存儲(chǔ)過(guò)程的定義，共有三種參數(shù)類型,IN,OUT,INOUT,形式如：
CREATEPROCEDURE 存儲(chǔ)過(guò)程名([ [ IN | OUT | INOUT ] 參數(shù)名 數(shù)據(jù)類型...])
IN 輸入?yún)?shù)：表示調(diào)用者向過(guò)程傳入值（傳入值可以是字面量或變量）
OUT 輸出參數(shù)：表示過(guò)程向調(diào)用者傳出值(可以返回多個(gè)值)（傳出值只能是變量）
INOUT 輸入輸出參數(shù)：既表示調(diào)用者向過(guò)程傳入值，又表示過(guò)程向調(diào)用者傳出值（值只能是變量）

注意:

• 如果過(guò)程沒(méi)有參數(shù)，也必須在過(guò)程名后面寫上小括號(hào)例:
  CREATE PROCEDURE sp.name([ proc_parameter[....] ]) ...
• 確保參數(shù)的名字不等于列的名字，否則在過(guò)程體中,參數(shù)名被當(dāng)做列名來(lái)處理

6.變量

(1) 變量定義
局部變量聲明一定要放在存儲(chǔ)過(guò)程體的開始：
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
其中，datatype 為 MySQL 的數(shù)據(jù)類型，如: int, float, date,varchar(length)
(2) 變量賦值
SET 變量名 = 表達(dá)式值 [,variable_name = expression ...]

注意:

• 用戶變量名一般以@開頭
• 濫用用戶變量會(huì)導(dǎo)致程序難以理解及管理

7.注釋

MySQL 存儲(chǔ)過(guò)程可使用兩種風(fēng)格的注釋

• 兩個(gè)橫桿--風(fēng)格：該風(fēng)格一般用于單行注釋。
• /* */風(fēng)格： 一般用于多行注釋。

8.MySQL存儲(chǔ)過(guò)程的調(diào)用

用call加過(guò)程名以及一個(gè)括號(hào)，括號(hào)里面根據(jù)需要，加入?yún)?shù)，參數(shù)包括輸入?yún)?shù)、輸出參數(shù)、輸入輸出參數(shù)。
call sp_name[(傳參)];

9.例子:

創(chuàng)建并使用存儲(chǔ)過(guò)程拼接Hello,world

CREATE PROCEDURE GreetWorld() SELECT CONCAT(@gretting, 'world');
SET @gretting = 'Hello,';
CALL GreetWorld()
創(chuàng)建并使用存儲(chǔ)過(guò)程p2使用p1定義的全局參數(shù)

運(yùn)行結(jié)果:

image.png

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

運(yùn)行結(jié)果:

image.png

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

• 獲取所有部門
• 添加部門
• 刪除部門

(三)功能實(shí)現(xiàn)

1.編寫存儲(chǔ)過(guò)程

添加部門存儲(chǔ)過(guò)程SELECT語(yǔ)句:

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為受影響的行數(shù)(正常是1) */
  SELECT last_insert_id() INTO did; /* did設(shè)置為插入之后生成的主鍵id(子id) */
  SET result2 = did; /* result2為插入之后生成的主鍵id(子id) */
  SELECT depPath INTO pDepPath FROM t_department WHERE id = parentId; /* pDepPath設(shè)置為傳入的父id所對(duì)應(yīng)的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

測(cè)試:

image.png

image.png

刪除部門存儲(chǔ)過(guò)程:

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; /* 部門表中沒(méi)有找到此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為受影響的行數(shù)(正常是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的父親只有他一個(gè)孩子 */
            END IF;
        END IF;
  END IF;
END

測(cè)試:

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>
 * 服務(wù)類
 * </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實(shí)現(xiàn)類

修改: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>
 * 服務(wù)實(shí)現(xiàn)類
 * </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">

    <!-- 通用查詢映射結(jié)果 -->
    <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>
    <!-- 通用查詢結(jié)果列 -->
    <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>

三.管理員管理

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

• 獲取所有管理員
• 更新管理員
• 更新管理員角色
• 獲取所有角色
• 刪除管理員

(二)功能實(shí)現(xiàn)

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>
 * 服務(wù)類
 * </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);

    /**
     * 根據(jù)用戶名獲取用戶
     *
     * @param username
     * @return
     */
    Admin getAdminByUserName(String username);

    /**
     * 根據(jù)用戶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實(shí)現(xiàn)類

修改: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>
 * 服務(wù)實(shí)現(xiàn)類
 * </p>
 *
 * @author 陳鑫元
 * @since 2021-05-21
 */
@Service
public class AdminServiceImpl extends ServiceImpl<AdminMapper, Admin> implements IAdminService {
    @Autowired
    private UserDetailsService userDetailsService; // 權(quán)限框架的

    @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) {
        // 校驗(yàn)驗(yàn)證碼
        String captcha = (String) request.getSession().getAttribute("captcah");
        if (null == captcha) {
            return RespBean.error("請(qǐng)先獲取驗(yàn)證碼！");
        }
        if (StringUtils.isEmpty(code) || !captcha.equalsIgnoreCase(code)) {
            return RespBean.error("驗(yàn)證碼輸入錯(cuò)誤，請(qǐng)重新輸入！");
        }
        // 登錄
        try {
            userDetailsService.loadUserByUsername(username); // 調(diào)用權(quán)限框架方法獲取用戶名
        } catch (UsernameNotFoundException e) {
            // 未找到該用戶
            return RespBean.error("用戶名或密碼不正確！");
        }
        UserDetails userDetails = userDetailsService.loadUserByUsername(username); // 調(diào)用權(quán)限框架方法獲取用戶名
        // passwordEncoder參數(shù)：第一個(gè)用戶傳過(guò)來(lái)的密碼，第二個(gè)從 userDetails 中獲取的密碼
        if (null == userDetails || !passwordEncoder.matches(password, userDetails.getPassword())) {
            // 密碼輸錯(cuò)了
            return RespBean.error("用戶名或密碼不正確！");
        }
        if (!userDetails.isEnabled()) {
            return RespBean.error("賬號(hào)被禁用，請(qǐng)聯(lián)系管理員！");
        }
        // 更新 security 登錄用戶對(duì)象，設(shè)置到全局
        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);
    }

    /**
     * 根據(jù)用戶名獲取用戶
     *
     * @param username
     * @return
     */
    @Override
    public Admin getAdminByUserName(String username) {
        // 要作空判斷
        return adminMapper.selectOne(new QueryWrapper<Admin>().eq("username", username));
    }

    /**
     * 根據(jù)用戶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">

    <!-- 通用查詢映射結(jié)果 -->
    <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>

    <!-- 通用查詢結(jié)果列 -->
    <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">

    <!-- 通用查詢映射結(jié)果 -->
    <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>

    <!-- 通用查詢結(jié)果列 -->
    <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>

四.員工管理

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

• 獲取所有員工(分頁(yè))
• 獲取所有政治面貌
• 獲取所有職稱
• 獲取所有民族
• 獲取所有職位
• 獲取所有部門
• 獲取工號(hào)
• 添加員工
• 更新員工
• 刪除員工
• 導(dǎo)出員工數(shù)據(jù)
• 導(dǎo)入員工數(shù)據(jù)

(二)功能實(shí)現(xiàn)

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 = "獲取所有員工(分頁(yè))")
    @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 = "獲取工號(hào)")
    @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 = "獲取所有員工(分頁(yè))")
    @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 = "獲取工號(hào)")
    @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 = "導(dǎo)出員工數(shù)據(jù)")
    @GetMapping(value = "/export", produces = "application/octet-stream")
    public void exportEmployee(HttpServletResponse response) {
        List<Employee> list = employeeService.getEmployee(null);
        // 03版的 HSSF ，兼容性好一點(diǎn)； 還有一個(gè)07版的 XSSF
        ExportParams params = new ExportParams("員工表", "員工表", ExcelType.HSSF);
        // 導(dǎo)出工具類
        Workbook workbook = ExcelExportUtil.exportExcel(params, Employee.class, list);
        ServletOutputStream out = null;
        try {
            // 流形式導(dǎo)出
            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 = "導(dǎo)入員工數(shù)據(jù)")
    @PostMapping("/import")
    public RespBean importEmployee(MultipartFile file) {
        ImportParams params = new ImportParams();
        // 去掉標(biāo)題行
        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. 通過(guò) name 獲取 inputNation (一個(gè)只有 name 且 id 為空的對(duì)象)
                Nation inputNation = new Nation(employee.getNation().getName());
                // 2. 通過(guò) inputNation 獲取對(duì)應(yīng)的下標(biāo)，通過(guò)下標(biāo)獲取完整的對(duì)象，通過(guò)對(duì)象獲取 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("導(dǎo)入成功!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return RespBean.error("導(dǎo)入失敗!");
    }
}

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>
 * 服務(wù)類
 * </p>
 *
 * @author 陳鑫元
 * @since 2021-05-21
 */
public interface IEmployeeService extends IService<Employee> {
    /**
     * 獲取所有員工(分頁(yè))
     *
     * @param currentPage
     * @param size
     * @param employee
     * @param beginDateScope
     * @return
     */
    RespPageBean getEmployeeByPage(Integer currentPage, Integer size, Employee employee, LocalDate[] beginDateScope);

    /**
     * 獲取工號(hào)
     *
     * @return
     */
    RespBean maxWorkID();

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

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

4.修改員工Service實(shí)現(xiàn)類

修改: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>
 * 服務(wù)實(shí)現(xiàn)類
 * </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;

    /**
     * 獲取所有員工(分頁(yè))
     *
     * @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;
    }

    /**
     * 獲取工號(hào)
     *
     * @return
     */
    @Override
    public RespBean maxWorkID() {
        List<Map<String, Object>> maps = employeeMapper.selectMaps(new QueryWrapper<Employee>()
                .select("max(workID)"));
        //  String.format(" %08d " = 獲得固定8位長(zhǎng)度的數(shù)字 （ 不足的前面補(bǔ)0 ）
        String workID = String.format("%08d", Integer.parseInt(maps.get(0).get("max(workID)").toString()) + 1);
        if (null != workID) {
            return RespBean.success("獲取員工工號(hào)成功!", workID);
        }
        return RespBean.error("獲取員工工失敗!");
    }

    /**
     * 添加員工
     *
     * @param employee
     * @return
     */
    @Override
    public RespBean addEmp(Employee employee) {
        // 處理合同期限，保留兩位小數(shù)
        LocalDate beginContract = employee.getBeginContract();// 合同開始時(shí)間
        LocalDate endContract = employee.getEndContract();// 合同結(jié)束時(shí)間
        // 計(jì)算兩個(gè)日期相差多少天
        long days = beginContract.until(endContract, ChronoUnit.DAYS);
        // 保留兩位小數(shù)
        DecimalFormat decimalFormat = new DecimalFormat("##.00");
        // 計(jì)算以年為單位
        employee.setContractTerm(Double.parseDouble(decimalFormat.format(days / 365.00)));
        if (1 == employeeMapper.insert(employee)) {
            // 獲取當(dāng)前行添加員工記錄
            Employee emp = employeeMapper.getEmployee(employee.getId()).get(0);
            String msgId = UUID.randomUUID().toString();
            // String msgId = "123456";
            MailLog mailLog = new MailLog();
            // 消息id: 為隨機(jī)生成的UUID
            mailLog.setMsgId(msgId);
            // 接收員工id
            mailLog.setEid(employee.getId());
            // 狀態(tài)（0: 消息投遞中 1: 投遞成功 2: 投遞失?。?            mailLog.setStatus(0);
            // 路由鍵
            mailLog.setRouteKey(MailConstants.MAIL_ROUTING_KEY_NAME);
            // 交換機(jī)
            mailLog.setExchange(MailConstants.MAIL_EXCHANGE_NAME);
            // 重試次數(shù)
            mailLog.setCount(0);
            // 重試時(shí)間: 當(dāng)前時(shí)間加1分鐘
            mailLog.setTryTime(LocalDateTime.now().plusMinutes(MailConstants.MSG_TIMEOUT));
            // 創(chuàng)建時(shí)間
            mailLog.setCreateTime(LocalDateTime.now());
            // 更新時(shí)間
            mailLog.setUpdateTime(LocalDateTime.now());
            // 把設(shè)置的數(shù)據(jù)插入數(shù)據(jù)表
            mailLogMapper.insert(mailLog);
// 發(fā)送信息
            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> {

    /**
     * 獲取所有員工(分頁(yè))
     *
     * @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">

    <!-- 通用查詢映射結(jié)果 -->
    <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>

    <!-- 通用查詢結(jié)果列 -->
    <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>

    <!-- 獲取所有員工(分頁(yè)) -->
    <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(員工姓名) 實(shí)體類屬性 -->
        <if test="null!=employee.name and ''!=employee.name">
            AND e.name LIKE CONCAT('%', #{employee.name}, '%')
        </if>
        <!-- employee.politicId(政治面貌) 實(shí)體類屬性 -->
        <if test="null!=employee.politicId and ''!=employee.politicId">
            AND e.politicId = #{employee.politicId}
        </if>
        <!-- employee.nationId(民族) 實(shí)體類屬性 -->
        <if test="null!=employee.nationId and ''!=employee.nationId">
            AND nationId = #{employee.nationId}
        </if>
        <!-- employee.jobLevelId(職稱) 實(shí)體類屬性 -->
        <if test="null!=employee.jobLevelId and ''!=employee.jobLevelId">
            AND e.jobLevelId = #{employee.jobLevelId}
        </if>
        <!-- employee.posId(職位) 實(shí)體類屬性 -->
        <if test="null!=employee.posId and ''!=employee.posId">
            AND e.posId = #{employee.posId}
        </if>
        <!-- employee.engageForm(聘用形式) 實(shí)體類屬性 -->
        <if test="null!=employee.engageForm and ''!=employee.engageForm">
            AND e.engageForm = #{employee.engageForm}
        </if>
        <!-- employee.departmentId(所屬部門) 實(shí)體類屬性 -->
        <if test="null!=employee.departmentId and ''!=employee.departmentId">
            AND e.departmentId = #{employee.departmentId}
        </if>
        <!-- beginDateScope(beginDateScope[0]~beginDateScope[1]: 入職日期范圍) 控制器中定義的日期數(shù)組參數(shù)名 -->
        <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>