Java笔记-22、Web后端实战-员工管理-列表查询

员工表有字段：所属部门。员工与部门有关系。

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构。由于业务之间相互关联，所以各个表结构之间也存在着各种联系。

多表关系分为三种：

1. 一对多（多对一）
2. 一对一
3. 多对多

一对多

场景：部门与员工的关系（一个部门下有多个员工，1-n）。

部门表（一，父表）----员工（多，子表）

只需要在多的一方增加字段，值为一的主键即可。

问题：部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整、不一致问题。

目前上述的两张表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的。

解决方案：外键约束。

外键约束

物理外键

概念：使用 foreign key 定义外键关联另外一张表。

缺点：

1. 影响增、删、改的效率（需要检查外键关系）。
2. 仅用于单节点数据库，不适用于分布式、集群场景。
3. 容易引发数据库的死锁问题，消耗性能。

在创建表时或表结构创建完成后，为字段添加外键约束。具体语法如下：

---创建表时指定

create table 表名(
  字段名 数据类型,
  ...
  [constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);

---建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

逻辑外键

概念：在业务层逻辑中，解决外键关联。

通过逻辑外键，就可以很方便的解决上述问题。而数据库中是没有物理的强关联关系的。

一对一

案例：用户与身份证信息的关系。

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。

用户基本信息表和用户身份信息表。

一对一就是特殊的一对多。也用外键进行约束，外键加到哪个表都行，同时不能重复！

即在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）。

多对多

案例：学生与课程的关系。

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择。

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

多表查询

多表查询：指从多张表中查询数据。

笛卡尔积：指在数学中，两个集合（A集合 和 B集合）的所有组合情况。

在多表查询时，需要消除无效的笛卡尔积。

分类

连接查询

• 内连接：相当于查询A、B交集部分数据
• 外连接：

左外连接：查询左表所有数据（包括两张表交集部分数据）

右外连接：查询右表所有数据（包括两张表交集部分数据）

内连接

内连接查询的是两张表交集部分的数据。

-- 1.隐式内连接

select 字段列表 from 表1, 表2 where 连接条件...;

-- 2.显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

-- 给表起别名，来简化书写；一旦使用了别名，之后都要使用别名了
select 字段列表 from 表1 [as] 别名1, 表2 [as] 别名2 where 条件...;

内连接

外连接分为左外连接和右外连接。具体语法为：

-- 1.左外连接
select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
-- 2.右外连接
select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;

对于外连接，常用的是左外连接，因为右外连接的SQL也可以改造成为左外连接（两张表调换个顺序）。

子查询（嵌套查询）

介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

形式：select * from t1 where column1 = (select columnl from t2 ...);

说明：子查询外部的语句可以是insert /update / delete / select 的任何一个，最常见的是 select。

分类：

• 标量子查询：子查询返回的结果为单个值。

常用的操作符： = <> > >= < <=

-- 1. 查询最早的入职时间
select min(entry_date) from emp;  -- 结果: 2000-01-01

-- 2. 查询入职时间 = 最早入职时间的员工信息
select * from emp where entry_date = '2000-01-01';

-- 3. 合并为一条SQL
select * from emp where entry_date = (select min(entry_date) from emp);

• 列子查询：子查询返回的结果为一列。

操作符	描述
in	在指定的集合范围之内，多选一
not in	不在指定的集合范围之内

-- 1. 查询 "教研部" 和 "咨询部" 的部门ID
select id from dept where name = '教研部' or name = '咨询部'; -- 结果: 3,2

-- 2. 根据上面查询出来的部门ID, 查询员工信息
select * from emp where dept_id in(3,2);

-- 3. 合并SQL为一条SQL语句
select * from emp where dept_id in (select id from dept where name = '教研部' or name = '咨询部');

• 行子查询：子查询返回的结果为一行。嵌套太多性能较低。

常用的操作符：= 、<> 、IN 、NOT IN

-- 1. 查询 "李忠" 的薪资和职位
select salary , job from emp where name = '李忠'; -- 结果: 5000, 5

-- 2. 根据上述查询到的薪资和职位 , 查询对应员工的信息
select * from emp where (salary, job) = (5000,5);

-- 3. 将两条SQL合并为一条SQL
select * from emp where (salary, job) = (select salary , job from emp where name = '李忠');

• 表子查询：子查询返回的结果为多行多列

-- a. 获取每个部门的最高薪资
select dept_id, max(salary) from emp group by dept_id;

-- b. 查询每个部门中薪资最高的员工信息
select * from emp e , (select dept_id, max(salary) max_sal from emp group by dept_id) a
    where e.dept_id = a.dept_id and e.salary = a.max_sal;

员工列表查询

准备工作

1. 准备数据库表emp、emp_expr。
2. 准备实体类 Emp、EmpExpr。
3. 准备三层架构的基本代码结构：EmpController、EmpService/EmpServiceImpl、EmpMapper。

数据库表

-- 员工表
create table emp(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) not null unique comment '用户名',
    password varchar(32) default '123456' comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1:男, 2:女',
    phone char(11) not null unique comment '手机号',
    job tinyint unsigned comment '职位, 1 班主任, 2 讲师 , 3 学工主管, 4 教研主管, 5 咨询师',
    salary int unsigned comment '薪资',
    image varchar(255) comment '头像',
    entry_date date comment '入职日期',
    dept_id int unsigned comment '部门ID',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '员工表';

-- 员工工作经历信息
create table emp_expr(
    id int unsigned primary key auto_increment comment 'ID, 主键',
    emp_id int unsigned comment '员工ID',
    begin date comment '开始时间',
    end  date comment '结束时间',
    company varchar(50) comment '公司名称',
    job varchar(50) comment '职位'
)comment '工作经历';

分页查询

原始方式

前端传递给后端的分页参数？

1. 页码：page
2. 每页展示记录数：pageSize

-- 查询第1页，每页5条
select e.*, d.name from emp e left join dept d on e.dept_id = d.id limit 0,5;

-- 查询总数据量
select count(*) from emp e left join dept d on e.dept_id = d.id;

后端给前端返回的数据？

1. 数据列表：List rows;
2. 总记录数：Long total;

通常分页结果会封装到一个分页结果类中。

@Data
@NoArgsConstructor
@AllArgsConstructor
public class PageResult<T> {
    private Long total;
    private List<T> rows;
}

为参数设置参数值，在接收前端参数时就设置，即在controller中设置，使用之前提到的一个注解@RequestParam

@GetMapping
    public Result page(@RequestParam(defaultValue = "1") Integer page, 
                       @RequestParam(defaultValue = "10") Integer pageSize){
        log.info("分页查询：{},{}:" ,page, pageSize);
        PageResult<Emp> pageResult = empService.page(page, pageSize);
        return Result.success(pageResult);
    }

@Slf4j
@RequestMapping("/emps")
@RestController
public class EmpController {

    @Autowired
    private EmpService empService;

    @GetMapping
    public Result page(@RequestParam(defaultValue = "1") Integer page,
                       @RequestParam(defaultValue = "10") Integer pageSize){
        log.info("分页查询：{},{}:" ,page, pageSize);
        PageResult<Emp> pageResult = empService.page(page, pageSize);
        return Result.success(pageResult);
    }

}

@Service
public class EmpServiceImpl implements EmpService {
    @Autowired
    private EmpMapper empMapper;
    @Override
    public PageResult<Emp> page(Integer page, Integer pageSize) {
        Long total = empMapper.count();
        Integer start = (page - 1) * pageSize;
        List<Emp> rows = empMapper.list(start, pageSize);
        return new PageResult<Emp>(total, rows);
    }
}

public interface EmpMapper {

    @Select("select count(*) from emp e left join dept d on e.dept_id = d.id")
    public Long count();

    @Select("select e.*, d.name deptName from emp e left join dept d on e.dept_id = d.id " +
            "order by e.update_time desc limit #{start},#{pageSize}")
    public List<Emp> list(Integer start, Integer pageSize);

}

在获取员工列表时，为了能获取到对应的部门名称，在Emp实体类中添加了deptName属性(与接口文档定义的一致)，同时为了能将mapper获取到的dept.name数据封装到Emp实体类的deptName上，需要在SQL语句中给d.name添加别名deptName。

PageHelper分页插件

分页查询的主流解决方案。

PageHelper是第三方提供的在Mybatis框架中用来实现分页的插件，用来简化分页操作，提高开发效率。

对原始方式的mapper层和service层进行封装。

使用步骤

1. 引入PageHelper的依赖
2. 定义Mapper接口的查询方法（无需考虑分页）
3. 在Service方法中实现分页查询

@Mapper
public interface EmpMapper {
  // 去掉了limit的分页操作
  @Select("select e.*, d.name deptName from emp e left join dept d on e.dept_id = d.id " +
            "order by e.update_time desc")
  public List<Emp> list();
}

@Service
public class EmpServiceImpl implements EmpService {
    @Autowired
    private EmpMapper empMapper;
    @Override
    public PageResult<Emp> page(Integer page, Integer pageSize) {
        // 设置分页参数(传递的是页码，不用自己算start了)
        PageHelper.startPage(page, pageSize);
        // 执行查询
        List<Emp> empList = empMapper.list();
        // PageHelper将结果封装到了Page类中，将empList强转为Page(Page继承了ArrayList)
        // 封装结果
        Page<Emp> p = (Page<Emp>)empList;
        return new PageResult<Emp>(p.getTotal(), p.getResult());
    }
}

实现机制

PageHelper将没有limit的SQL语句拦截，并改造为下面的两条SQL语句。

select count(0) from emp e ...

select ... from emp e ... limit ?,?

注意事项

1. SQL语句结尾不要加分号（；），否则上面第二条就会拼接错误。
2. PageHelper只会对紧跟在其后的第一条SQL语句进行分页处理。

PageHelper.startPage(page, pageSize);

// 执行紧跟在其后的第一条SQL语句
List<Emp> empList = empMapper.list();

// 这条PageHelper就不会改造了
List<Emp> empList2 = empMapper.list();

条件分页查询

• 接收前端传递的时间格式的参数。

使用LocalDate或者LocalDateTime的变量接收，使用注解@DateTimeFormat来规定接收的时间格式

@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin

• 根据XML映射文件编写语句

select e.*, d.name from emp e left join dept d on e.dept_id = d.id 
where e.name like '%阮%' and e.gender = 1 and e.entry_date between '2010-01-01' and '2020-01-01' order by e.update_time desc;

在resources中创建目录com/study/mapper，创建EmpMapper.xml文件，满足同包同名。namespace与EmpMapper的全类名保持一致，右键复制reference，id与方法名保持一致，在mapper中自动生成select标签，并书写SQL语句。

注意：占位符的问号不能出现在引号中，在书写SQL语句接收name进行模糊查询时like '%#{}%'是错误的。这时候应该使用MySQL的字符串拼接函数：

select e.*, d.name from emp e left join dept d on e.dept_id = d.id 
where e.name like concat('%',#{name},'%') 
and e.gender = #{gender} 
and e.entry_date between #{begin} and #{end} 
order by e.update_time desc

@Slf4j
@RequestMapping("/emps")
@RestController
public class EmpController {

    @Autowired
    private EmpService empService;

    @GetMapping
    public Result page(@RequestParam(defaultValue = "1") Integer page,
                       @RequestParam(defaultValue = "10") Integer pageSize,
                       String name, Integer gender,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){
        log.info("分页查询：{},{},{},{},{},{}" ,page, pageSize,name,gender, begin, end);
        PageResult<Emp> pageResult = empService.page(page, pageSize, name, gender, begin, end);
        return Result.success(pageResult);
    }

}

public interface EmpService {

    PageResult<Emp> page(Integer page, Integer pageSize, String name, Integer gender, LocalDate begin, LocalDate end);
}

@Service
public class EmpServiceImpl implements EmpService {
    @Autowired
    private EmpMapper empMapper;
    @Override
    public PageResult<Emp> page(Integer page, Integer pageSize, String name, Integer gender, LocalDate begin, LocalDate end) {
        // 设置分页参数(传递的是页码，不用自己算start了)
        PageHelper.startPage(page, pageSize);
        // 执行查询
        List<Emp> empList = empMapper.list(name, gender, begin, end);
        // PageHelper将结果封装到了Page类中，将empList强转为Page(Page继承了ArrayList)
        // 封装结果
        Page<Emp> p = (Page<Emp>)empList;
        return new PageResult<Emp>(p.getTotal(), p.getResult());
    }
}

@Mapper
public interface EmpMapper {
    public List<Emp> list(String name, Integer gender, LocalDate begin, LocalDate end);
    
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.study.mapper.EmpMapper">
    <select id="list" resultType="com.study.pojo.Emp">
        select e.*, d.name from emp e left join dept d on e.dept_id = d.id
        where e.name like concat('%',#{name},'%')
        and e.gender = #{gender}
        and e.entry_date between #{begin} and #{end}
        order by e.update_time desc
    </select>
</mapper>

程序优化

请求参数的封装

/emps?name=张&gender=1&begin=2010-01-01&end=2020-01-01&page=1&pageSize=5

传递的参数多->方法形参多->不易维护

如何解决？

此时可考虑将多个请求参数封装为一个对象，保证对应类的属性名与请求参数的参数名一致。如果之后请求参数增多，controller代码不用动，只需要改实体类即可。

动态SQL(MyBatis)-可选参数的SQL书写

有些请求参数可以不发送的，但书写的SQL语句却写死了。

比如不发送begin 和 end，根据上述SQL，会查不到预期结果。

==>  Preparing: SELECT count(0) FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.name LIKE concat('%', ?, '%') AND e.gender = ? AND e.entry_date BETWEEN ? AND ?
==> Parameters: 阮(String), 1(Integer), null, null

查询条件应随着用户输入的条件变化而变化-动态SQL。

<if>：判断条件是否成立，如果条件为true，则拼接SQL。

：根据查询条件，来生成where关键字，并会自动去除条件前面多余的and或or。

@Data
public class EmpQueryParam {
    private Integer page = 1; // 注意默认值的指定
    private Integer pageSize = 10;
    private String name;
    private Integer gender;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private LocalDate begin;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    LocalDate end;
}

@Slf4j
@RequestMapping("/emps")
@RestController
public class EmpController {

    @Autowired
    private EmpService empService;

    @GetMapping
    public Result page(EmpQueryParam empQueryParam){
        log.info("分页查询：{}" ,empQueryParam);
        PageResult<Emp> pageResult = empService.page(empQueryParam);
        return Result.success(pageResult);
    }

}

public interface EmpService {
    PageResult<Emp> page(EmpQueryParam empQueryParam);
}

@Service
public class EmpServiceImpl implements EmpService {
    @Autowired
    private EmpMapper empMapper;
    @Override
    public PageResult<Emp> page(EmpQueryParam empQueryParam) {
        // 设置分页参数(传递的是页码，不用自己算start了)
        PageHelper.startPage(empQueryParam.getPage(), empQueryParam.getPageSize());
        // 执行查询
        List<Emp> empList = empMapper.list(empQueryParam); //直接传递对象，对应的SQL语句不用变，#{}可获取对象中对应属性名的值
        // PageHelper将结果封装到了Page类中，将empList强转为Page(Page继承了ArrayList)
        // 封装结果
        Page<Emp> p = (Page<Emp>)empList;
        return new PageResult<Emp>(p.getTotal(), p.getResult());
    }
}

@Mapper
public interface EmpMapper {
    public List<Emp> list(EmpQueryParam empQueryParam);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.study.mapper.EmpMapper">
    <select id="list" resultType="com.study.pojo.Emp">
        select e.*, d.name from emp e left join dept d on e.dept_id = d.id
        <where>
            <if test="name != null and name != ''">
                e.name like concat('%',#{name},'%')
            </if>
            <if test="gender != null">
                and e.gender = #{gender}
            </if>
            <if test="begin != null and end != null">
                and e.entry_date between #{begin} and #{end}
            </if>
        </where>
        order by e.update_time desc
    </select>
</mapper>

众所周知，在sql中如果需要条件在where后跟随就可，可在mapper.xml文件中有些特殊，where的>号虽然可以正常使用，但是<号会被识别为前一个标签的结束语句，那么在输入<时会被识别为这个语句的结尾，这时需要使用"<"来代替<号进行条件判断。