【JavaWeb学习Day16】

Tlias智能学习系统 员工管理： 员工列表查询（Tlias）

需求:查询所有员工信息，并查询出部门名称。（涉及到的表：emp、dept）

准备工作：

1.准备数据库表emp、emp_expr。

-- 员工表 create table emp(   id int unsigned primary key auto_increment comment 'ID,主键',   username varchar(20) not null unique comment '用户名',    password varchar(50) 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(300) comment '头像',   entry_date date comment '入职日期',   dept_id int unsigned comment '部门ID',   create_time datetime comment '创建时间',   update_time datetime comment '修改时间' ) comment '员工表'; ​ ​ INSERT INTO emp VALUES    (1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2000-01-01',2,'2023-10-20 16:35:33','2023-11-16 16:11:26'),    (2,'songjiang','123456','宋江',1,'13309090002',2,8600,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2015-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:37'),    (3,'lujunyi','123456','卢俊义',1,'13309090003',2,8900,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2008-05-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:39'),    (4,'wuyong','123456','吴用',1,'13309090004',2,9200,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2007-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:41'),    (5,'gongsunsheng','123456','公孙胜',1,'13309090005',2,9500,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2012-12-05',2,'2023-10-20 16:35:33','2023-10-20 16:35:43'),    (6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2013-09-05',1,'2023-10-20 16:35:33','2023-10-20 16:35:45'),    (7,'chaijin','123456','柴进',1,'13309090007',1,4700,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2005-08-01',1,'2023-10-20 16:35:33','2023-10-20 16:35:47'),    (8,'likui','123456','李逵',1,'13309090008',1,4800,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2014-11-09',1,'2023-10-20 16:35:33','2023-10-20 16:35:49'),    (9,'wusong','123456','武松',1,'13309090009',1,4900,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2011-03-11',1,'2023-10-20 16:35:33','2023-10-20 16:35:51'),    (10,'linchong','123456','林冲',1,'13309090010',1,5000,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2013-09-05',1,'2023-10-20 16:35:33','2023-10-20 16:35:53'),    (11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2007-02-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:55'),    (12,'xiaoliguang','123456','小李广',1,'13309090012',2,10000,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2008-08-18',2,'2023-10-20 16:35:33','2023-10-20 16:35:57'),    (13,'yangzhi','123456','杨志',1,'13309090013',1,5300,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2012-11-01',1,'2023-10-20 16:35:33','2023-10-20 16:35:59'),    (14,'shijin','123456','史进',1,'13309090014',2,10600,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2002-08-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:01'),    (15,'sunerniang','123456','孙二娘',2,'13309090015',2,10900,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2011-05-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:03'),    (16,'luzhishen','123456','鲁智深',1,'13309090016',2,9600,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2010-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:05'),    (17,'liying','12345678','李应',1,'13309090017',1,5800,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2015-03-21',1,'2023-10-20 16:35:33','2023-10-20 16:36:07'),    (18,'shiqian','123456','时迁',1,'13309090018',2,10200,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2015-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:09'),    (19,'gudasao','123456','顾大嫂',2,'13309090019',2,10500,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2008-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:11'),    (20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2018-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:13'),    (21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2015-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:15'),    (22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2016-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:17'),    (23,'ruanji','123456','阮籍',1,'13309090023',5,5800,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2012-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:19'),    (24,'tongwei','123456','童威',1,'13309090024',5,5000,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2006-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:21'),    (25,'tongmeng','123456','童猛',1,'13309090025',5,4800,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2002-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:23'),    (26,'yanshun','123456','燕顺',1,'13309090026',5,5400,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2011-01-01',3,'2023-10-20 16:35:33','2023-11-08 22:12:46'),    (27,'lijun','123456','李俊',1,'13309090027',2,6600,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2004-01-01',2,'2023-10-20 16:35:33','2023-11-16 17:56:59'),    (28,'lizhong','123456','李忠',1,'13309090028',5,5000,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2007-01-01',3,'2023-10-20 16:35:33','2023-11-17 16:34:22'),    (30,'liyun','123456','李云',1,'13309090030',NULL,NULL,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2020-03-01',NULL,'2023-10-20 16:35:33','2023-10-20 16:36:31'),    (36,'linghuchong','123456','令狐冲',1,'18809091212',2,6800,' web-framework.oss-cn-hangzhou.aliyuncs /2023/1.jpg','2023-10-19',2,'2023-10-20 20:44:54','2023-11-09 09:41:04');     ​ -- 员工工作经历信息 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 '工作经历';

2.准备实体类Emp、EmpExpr

package com.itheima.pojo; ​ import lombok.Data; ​ import java.time.LocalDate; import java.time.LocalDateTime; import java.util.List; ​ @Data public class Emp {    private Integer id; //ID,主键    private String username; //用户名    private String password; //密码    private String name; //姓名    private Integer gender; //性别, 1:男, 2:女    private String phone; //手机号    private Integer job; //职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师    private Integer salary; //薪资    private String image; //头像    private LocalDate entryDate; //入职日期    private Integer deptId; //关联的部门ID    private LocalDateTime createTime; //创建时间    private LocalDateTime updateTime; //修改时间 ​    //封装部门名称数    private String deptName; //部门名称 } package com.itheima.pojo; ​ import lombok.Data; ​ import java.time.LocalDate; ​ /** * 工作经历 */ @Data public class EmpExpr {    private Integer id; //ID    private Integer empId; //员工ID    private LocalDate begin; //开始时间    private LocalDate end; //结束时间    private String company; //公司名称    private String job; //职位 }

3.准备三层架构的基本代码结构：EmpController、EmpService/EmpServiceImpl、EmpMapper。

EmpMapper：

package com.itheima.mapper; ​ import com.itheima.pojo.Emp; import org.apache.ibatis.annotations.Mapper; import java.util.List; ​ @Mapper public interface EmpMapper { ​ }

EmpService:

package com.itheima.service; ​ public interface EmpService { }

EmpServiceImpl:

package com.itheima.service.impl; ​ import com.itheima.mapper.EmpMapper; import com.itheima.service.EmpService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; ​ /** * 员工管理 */ @Service public class EmpServiceImpl implements EmpService { ​    @Autowired    private EmpMapper empMapper; ​ }

EmpController:

package com.itheima.controller; ​ import com.itheima.service.EmpService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RestController; ​ /** * 员工管理 */ @Slf4j @RestController public class EmpController { ​    @Autowired    private EmpService empService; ​ }

分页查询：

1.原始方式：

分析：

三层架构中，每一层的职责如下：

1.Controller：接收参数（分页）；调用service，进行分页查询，获取PageResult；响应结果。

具体实现：

/** * 分页查询 */ @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); }

2.Service：调用Mapper接口，查询总记录数；调用Mapper接口，查询结果列表；封装PageResult对象，返回。

public PageResult<Emp> page(Integer page, Integer pageSize) {    //1.调用Mapper接口，查询总记录数    Long total = empMapper.count(); ​    //2.调用Mapper接口，查询结果列表    Integer start = (page-1)*pageSize;    List<Emp> rows = empMapper.list(start, pageSize); ​    //3.封装结果PageResult    return new PageResult<Emp>(total,rows); }

3.Mapper：SQL：

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;

具体实现：

@Mapper 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 limit #{start},#{pageSize};")    public List<Emp> list(Integer start,Integer pageSize); }

2.PageHelper分页插件：

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

使用步骤：

1.引入PageHelper的依赖

<!--分页插件PageHelper--> <dependency>    <groupId>com.github.pagehelper</groupId>    <artifactId>pagehelper-spring-boot-starter</artifactId>    <version>1.4.7</version> </dependency>

2.定义Mapper接口接口的查询方法（无需考虑分页）

@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();

3.在service方法中实现分页查询

/** * PageHelper分页查询 * @param page 页码 * @param pageSize 每页的记录数 */ @Override public PageResult<Emp> page(Integer page, Integer pageSize) {    //1.设置分页参数（PageHelper）    PageHelper.startPage(page,pageSize); ​    //2.执行查询    List<Emp> empList = empMapper.list(); ​    //3.解析查询结果，并封装    Page<Emp> p = (Page<Emp>) empList;    return new PageResult<Emp>(p.getTotal(),p.getResult()); ​ }

（为什么empList可以强转为Page<emp>）

PageHelper实现机制：

PageHelper注意事项：

1.定义的SQL语句结尾不能加分号；

2.PageHelper仅仅能对紧跟其后的第一个查询语句进行分页处理