## Day10 后端Web实战：员工管理3(删除.修改.统计)

---

#### 需求

<figure><img src="/AiJavaWeb/imgs/jwai10-01.png"><figcaption>图1 删除.修改.异常.统计需求</figcaption></figure>




##### 目录
* 删除员工
* 修改员工
* 异常处理
* 员工信息统计

  

### 1. 删除员工

#### 1.1 需求分析

<figure><img src="/AiJavaWeb/imgs/jwai10-02.png"><figcaption>图2 删除需求分析</figcaption></figure>

* 其实，删除单条数据也是一种特殊的批量删除，所以，删除员工的功能，我们只需要开发一个接口就可以了。

#### 1.2 删除员工--分层分析
<figure><img src="/AiJavaWeb/imgs/jwai10-03.png"><figcaption>图3 删除员工--接口要求</figcaption></figure>

```sql
-- 删除员工基本信息及其工作经历信息
delete from emp where id in(1,2,3);
delete from emp_expr where emp_id in (1,2,3);
```



<figure><img src="/AiJavaWeb/imgs/jwai10-04.png"><figcaption>图4 删除员工--分层功能</figcaption></figure>

#### 1.3 删除员工--实现

##### 删除员工-Controller接收请求参数   cn/dzj/controller/EmpController.java 添加下列方法

* 方式一：在Controller方法中通过数组来接收

```java
@DeleteMapping
public Result delete(Integer[] ids){
    log.info("根据id批量删除员工:{} ", ids);
    // empService.deleteByIds(ids);
    return Result.success();
}
```

* 方式二：在Controller方法中通过集合来接收

```java
@DeleteMapping
public Result delete(@RequestParam List<Integer> ids){
    log.info("根据id批量删除员工:{} ", ids);
    // empService.deleteByIds(ids);
    return Result.success();
}
```

<figure><img src="/AiJavaWeb/imgs/jwai10-05.png"><figcaption>图5 删除员工--接受请求参数</figcaption></figure>

##### 删除员工-Service&Mapper

##### cn/dzj/service/impl/EmpServiceImpl.java 添加下列方法

```java
@Transactional(rollbackFor = {Exception.class})
public void deleteByIds(List<Integer> ids) {
    //1. 根据ID删除员工基本信息
    empMapper.deleteByIds(ids);
    //2. 根据ID删除员工的工作经历信息
    empExprMapper.deleteByEmpIds(ids);
}
```

##### cn/dzj/mapper/EmpMapper.java 添加下列方法

```java
 void deleteByIds(List<Integer> ids);
```

##### cn/dzj/mapper/EmpExprMapper.java 添加下列方法

```java
void deleteByEmpIds(List<Integer> empIds);
```

##### src/main/resources/cn/dzj/mapper/EmpMapper.xml  添加下列代码

```xml
<!-- 根据ID批量删除员工信息 -->
<delete id="deleteByIds">
    delete from emp where id in
    <foreach collection="ids" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>
</delete>
```

##### src/main/resources/cn/dzj/mapper/EmpExprMapper.xml  添加下列代码

```xml
<!--根据员工ID批量删除员工工作经历信息-->
<delete id="deleteByEmpIds">
    delete from emp_expr where emp_id in
    <foreach collection="empIds" item="empId" separator="," open="(" close=")">
        #{empId}
    </foreach>
</delete>
```



<figure><img src="/AiJavaWeb/imgs/jwai10-06.png"><figcaption>图6 <mark>员工删除测试验证1</mark></figcaption></figure>



<figure><img src="/AiJavaWeb/imgs/jwai10-07.png"><figcaption>图7 <mark>员工删除测试验证2</mark></figcaption></figure>

#### 小结

<figure><img src="/AiJavaWeb/imgs/jwai10-08.png"><figcaption>图8 删除员工小结</figcaption></figure>



### 2. 修改员工

#### 需求



<figure><img src="/AiJavaWeb/imgs/jwai10-09.png"><figcaption>图9 修改员工需求</figcaption></figure>




#### 2.1 查询回显


##### 查询回显--分层分析

<figure><img src="/AiJavaWeb/imgs/jwai10-10.png"><figcaption>图10 查询回显--分层分析</figcaption></figure>

```sql
select e.*, ee.id ee_id, ee.company ee_company, ee.job ee_job, ee.begin ee_begin, ee.end ee_end, ee.emp_id ee_empid
from emp e left join emp_expr ee on e.id =ee.emp_id where e.id = 63;
```

#### resultMap

<figure><img src="/AiJavaWeb/imgs/jwai10-11.png"><figcaption>图11 resultMap</figcaption></figure>

##### cn/dzj/controller/EmpController.java       添加下列方法

```java
    /**
     *根据ID查询员工信息
     */
    @GetMapping("/{id}")
    public Result getInfo(@PathVariable Integer id) {
        log.info("根据ID查询员工信息：{}",id);
        Emp emp = empService.getInfo(id);
        return Result.success(emp);
    }
```

##### cn/dzj/service/EmpService.java      添加下列方法

```java
    Emp getInfo(Integer id);
```

##### cn/dzj/service/impl/EmpServiceImpl.java    添加下列方法

```java
    @Override
    public Emp getInfo(Integer id) {
        return empMapper.getById(id);
    }
```

##### cn/dzj/mapper/EmpMapper.java  添加下列方法

```java
    Emp getById(Integer id);
```

##### src/main/resources/cn/dzj/mapper/EmpMapper.xml  添加getById

```xml
    <!--根据ID查询员工基本信息及员工的工作经历信息-->
    <select id="getById" resultMap="empResultMap" ><!--resultType="cn.dzj.pojo.Emp"-->
        select
        e.*,
        ee.id ee_id,
        ee.emp_id ee_empid,
        ee.begin ee_begin,
        ee.end ee_end,
        ee.company ee_company,
        ee.job ee_job
        from emp e left join emp_expr ee on e.id = ee.emp_id
        where e.id= #{id}
    </select>

    <!--自定义结果集ResultMap-->
    <resultMap id="empResultMap" type="cn.dzj.pojo.Emp">
        <id column="id" property="id" />
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="salary" property="salary" />
        <result column="phone" property="phone" />
        <result column="job" property="job" />
        <result column="name" property="name" />
        <result column="gender" property="gender" />
        <result column="image" property="image" />
        <result column="entry_date" property="entryDate" />
        <result column="dept_id" property="deptId" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
        <!--封装exprList-->
        <collection property="exprList" ofType="cn.dzj.pojo.EmpExpr">
            <id column="ee_id" property="id"/>
            <result column="ee_company" property="company"/>
            <result column="ee_job" property="job"/>
            <result column="ee_begin" property="begin"/>
            <result column="ee_end" property="end"/>
            <result column="ee_empid" property="empId"/>
        </collection>
    </resultMap>
```



<figure><img src="/AiJavaWeb/imgs/jwai10-12.png"><figcaption>图12 <mark>查询回显验证1</mark></figcaption></figure>



<figure><img src="/AiJavaWeb/imgs/jwai10-13.png"><figcaption>图13 <mark>查询回显验证2</mark></figcaption></figure>



<figure><img src="/AiJavaWeb/imgs/jwai10-14.png"><figcaption>图14 resultType和resultMap小结</figcaption></figure>



#### 2.2 修改数据

#### 需求分析

<figure><img src="/AiJavaWeb/imgs/jwai10-15.png"><figcaption>图15 修改员工需求</figcaption></figure>

#### 修改员工--实现

##### cn/dzj/controller/EmpController.java       添加下列方法

```java
    /**
     *修改员工
     */
    @PutMapping
    public Result update(@RequestBody Emp emp) {
        log.info("修改员工：{}", emp);
        empService.update(emp);
        return Result.success();
    }
```

##### cn/dzj/service/EmpService.java      添加下列方法

```java
void update(Emp emp);
```

##### cn/dzj/service/impl/EmpServiceImpl.java    添加下列方法

```java
    @Transactional(rollbackFor = {Exception.class})
    public void update(Emp emp) {
        //1，根据ID修改员工的基本信息
        emp.setUpdateTime(LocalDateTime.now());
        empMapper.updateById(emp);

        //2，根据ID修改员工的工作经历信息
        //2.1先根据员工ID删除原有的工作经历
        empExprMapper.deleteByEmpIds(Arrays.asList(emp.getId()));
        //2.2再添加这个员工新的工作经历
        List<EmpExpr> exprList = emp.getExprList();
        if (!CollectionUtils.isEmpty(exprList))
            exprList.forEach(empExpr -> empExpr.setEmpId(emp.getId()));
        empExprMapper.insertBatch(exprList);
    }
```

##### cn/dzj/mapper/EmpMapper.java  添加下列方法

```java
    void updateById(Emp emp);
```

##### src/main/resources/cn/dzj/mapper/EmpMapper.xml  添加下列方法

```xml
    <!-- 根据ID更新员工基本信息-->
    <update id="updateById">
        UPDATE emp
        SET
        username = #{username},
        password = #{password},
        name = #{name},
        gender = #{gender},
        phone = #{phone},
        job = #{job},
        salary = #{salary},
        image = #{image},
        entry_date = #{entryDate},
        dept_id = #{deptId},
        update_time = #{updateTime}
        WHERE id=#{id}
    </update>
```



```sql
-- 根据ID更新员工基本信息
UPDATE emp
SET
    username = 'zhangsan',
    password ='654321',
    name='张三',
    gender =1,
    phone ='13800138000',
    job =2,
    salary =8000,
    image='/path/to/new/image.jpg',
    entry_date='2023-01-01',
    dept_id=2,
    update_time = Now()
WHERE id =5;
```

<figure><img src="/AiJavaWeb/imgs/jwai10-16.png"><figcaption>图16 <mark>修改员工测试验证1</mark></figcaption></figure>



<figure><img src="/AiJavaWeb/imgs/jwai10-17.png"><figcaption>图17 <mark>修改员工测试验证2</mark></figcaption></figure>

#### 修改员工一程序优化

##### src/main/resources/cn/dzj/mapper/EmpMapper.xml  修改updateById

```xml
    <!-- 根据ID更新员工基本信息-->
    <!-- set标签：会自动生成set关键字；会自动的删除掉更新字段后多余，-->
    <update id="updateById">
		<!-- UPDATE emp
        SET
        username = #{username},
        password = #{password},
        name = #{name},
        gender = #{gender},
        phone = #{phone},
        job = #{job},
        salary = #{salary},
        image = #{image},
        entry_date = #{entryDate},
        dept_id = #{deptId},
        update_time = #{updateTime}
        WHERE id=#{id}-->

        UPDATE emp
        <set>
            <if test="username !=null and username !=''">username = #{username},</if>
            <if test="password != null and password !=''">password=#{password},</if>
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="gender != null">gender =#{gender},</if>
            <if test="phone !=null and phone != ''">phone =#{phone},</if>
            <if test="job!=null">job =#{job},</if>
            <if test="salary!= null">salary = #{salary},</if>
            <if test="image!= null and image !=''">image =#{image},</if>
            <if test="entryDate !=null">entry_date =#{entryDate},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="updateTime != null">update_time =#{updateTime}</if>
        </set>
        WHERE id=#{id}
    </update>
```



<figure><img src="/AiJavaWeb/imgs/jwai10-18.png"><figcaption>图18 <mark>修改员工一程序优化验证1</mark></figcaption></figure>



<figure><img src="/AiJavaWeb/imgs/jwai10-19.png"><figcaption>图19 <mark>修改员工一程序优化验证2</mark></figcaption></figure>



##### 小结

<figure><img src="/AiJavaWeb/imgs/jwai10-20.png"><figcaption>图20 修改员工一程序优化小结</figcaption></figure>

### 3.异常处理

#### 问题提出

<figure><img src="/AiJavaWeb/imgs/jwai10-21.png"><figcaption>图21 问题提出</figcaption></figure>

#### 全局异常处理

* 现在项目中各层出现的异常,是如何处理? <mark>未做处理</mark>

<figure><img src="/AiJavaWeb/imgs/jwai10-22.png"><figcaption>图22 出现的异常未做处理的输出和利用Try-Catch处理的繁琐</figcaption></figure>

* 全局异常处理器

<figure><img src="/AiJavaWeb/imgs/jwai10-23.png"><figcaption>图23 全局异常处理</figcaption></figure>





<figure><img src="/AiJavaWeb/imgs/jwai10-24.png"><figcaption>图24 <mark>全局异常处理验证</mark></figcaption></figure>



#### 全局异常处理小结

<figure><img src="/AiJavaWeb/imgs/jwai10-25.png"><figcaption>图25 全局异常处理小结小结</figcaption></figure>



### 4.员工信息统计

#### 需求

<figure><img src="/AiJavaWeb/imgs/jwai10-26.png"><figcaption>图26 员工信息统计需求</figcaption></figure>

#### 4.1 职位统计

https://echarts.apache.org/zh/index.html

<figure><img src="/AiJavaWeb/imgs/jwai10-27.png"><figcaption>图27 职位统计</figcaption></figure>

```sql
-- 统计每一种职位对应的人数
-- case函数:case 表达式 when val1 then result1 when val2 then result2....else...end
-- 仅用于等值判断
select
    (case job
        when 1 then '班主任'
        when 2 then '讲师'
        when 3 then '学工主管'
        when 4 then '教研主管'
        when 5 then '咨询师'
        else '其他' end) pos,
        count(*) num
from emp group by job order by num;

-- case函数:case when 条件表达式1 then result1 when 条件表达式2 then result2....else...end
-- 仅用于逻辑判断
select
    (case
        when job=1 then'班主任'
        when job=2 then'讲师'
        when job=3 then'学工主管'
        when job=4 then'教研主管'
        when job=5 then'咨询师'
        else '其他' end) pos,
     count(*) num
from emp group by job order by num;
```


<figure><img src="/AiJavaWeb/imgs/jwai10-28.png"><figcaption>图28 职位统计分层功能</figcaption></figure>

##### cn/dzj/controller/ReportController.java

```java
package cn.dzj.controller;

import cn.dzj.pojo.JobOption;
import cn.dzj.pojo.Result;
import cn.dzj.service.ReportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@Slf4j
@RequestMapping("/report")
@RestController
public class ReportController{

    @Autowired
    private ReportService reportService;

    /**
     *统计员工职位人数
     */
    @GetMapping("/empJobData")
    public Result getEmpJobData() {
        log.info("统计员工职位人数");
        JobOption jobOption = reportService.getEmpJobData();
        return Result.success(jobOption);
    }
}
```

##### cn/dzj/service/ReportService.java

```java
public interface ReportService{
   JobOption getEmpJobData() ;
}
```

##### cn/dzj/service/impl/ReportServiceImpl.java

```java
@Service
public class ReportServiceImpl implements ReportService {
    @Autowired
    EmpMapper empMapper;
    public JobOption getEmpJobData() {
        List<Map<String,Object>> list = empMapper.countEmpJobData();
        List<Object> jobList = list.stream().map(dataMap -> dataMap.get("pos")).toList();
        List<Object> dataList = list.stream().map(dataMap -> dataMap.get("num")).toList();
        return new JobOption(jobList, dataList);
    }
}
```

##### cn/dzj/mapper/EmpMapper.java  添加下列方法

```java
    /**
     * 统计员工职位人数
     */
	@MapKey("pos")
    List <Map<String,Object>> countEmpJobData();
```

##### src/main/resources/cn/dzj/mapper/EmpMapper.xml 添加下列方法

```xml
    <!-- 统计员工职位人数 -->
    <select id="countEmpJobData" resultType="java.util.Map">
        select
        (case job
        when 1 then '班主任'
        when 2 then '讲师'
        when 3 then '学工主管'
        when 4 then '教研主管'
        when 5 then '咨询师'
        else '其他' end) pos,
        count(*) num
        from emp group by job order by num
    </select>
```



<figure><img src="/AiJavaWeb/imgs/jwai10-29.png"><figcaption>图29 <mark>职位统计验证1</mark></figcaption></figure>



<figure><img src="/AiJavaWeb/imgs/jwai10-30.png"><figcaption>图30 <mark>职位统计验证2</mark></figcaption></figure>



#### 职位统计小结

<figure><img src="/AiJavaWeb/imgs/jwai10-31.png"><figcaption>图31 职位统计</figcaption></figure>



<figure><img src="/AiJavaWeb/imgs/jwai10-32.png"><figcaption>图32 职位统计小结</figcaption></figure>



#### 4.2 性别统计

<figure><img src="/AiJavaWeb/imgs/jwai10-33.png"><figcaption>图33 性别统计</figcaption></figure>



```sql
-- 统计员工性别人数
-- if(条件,true_value,false_value)
select
    if(gender=1,'男性员工','女性员工') name,
    count(*) value
from emp group by gender;
```

##### cn/dzj/controller/ReportController.java

```java
    /**
     *统计员工性别人数
     */
    @GetMapping("/empGenderData")
    public Result getEmpGenderData(){
        log.info("统计员工性别人数");
        List<Map<String,Object>> genderList=reportService.getEmpGenderData();
        return Result.success(genderList);
    }
```

##### cn/dzj/service/ReportService.java

```java
	List<Map<String,Object>> countEmpGenderData();
```

##### cn/dzj/service/impl/ReportServiceImpl.java   添加下列方法

```java
    @Override
    public List<Map> getEmpGenderData() {
        return empMapper.countEmpGenderData();
    }
```

##### src/main/resources/cn/dzj/mapper/EmpMapper.xml     添加下列方法

```xml
	<!-- 统计员工的性别信息 -->
    <select id="countEmpGenderData" resultType="java.util.Map">
        select
        if(gender=1,'男性员工','女性员工') name,
        count(*) value
        from emp group by gender;
    </select>
```

#### 性别统计验证

<figure><img src="/AiJavaWeb/imgs/jwai10-34.png"><figcaption>图34 <mark>性别统计验证1</mark></figcaption></figure>



<figure><img src="/AiJavaWeb/imgs/jwai10-35.png"><figcaption>图35 <mark>性别统计验证2</mark></figcaption></figure>



#### 性别统计小结

<figure><img src="/AiJavaWeb/imgs/jwai10-36.png"><figcaption>图36 性别统计</figcaption></figure>



<figure><img src="/AiJavaWeb/imgs/jwai10-37.png"><figcaption>图37 小结</figcaption></figure>





①②③④⑤⑥⑦⑧⑨⑩