Day44(14)-F:\硕士阶段\Java\课程代码\后端\web-ai-code\web-ai-project02\tlias-web-management
分页查询
package com.itheima.controller;
import com.itheima.pojo.Emp;
import com.itheima.pojo.PageResult;
import com.itheima.pojo.Result;
import com.itheima.service.EmpService;
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;
/**
* 员工管理Controller
*/
@Slf4j
@RequestMapping("/emps")
@RestController
public class EmpController {
@Autowired
private EmpService empService;
/**
* 分页查询
* @return
*/
@GetMapping
public Result page(Integer page,Integer pageSize){
log.info("分页查询：{},{}",page,pageSize);
PageResult<Emp> pageResult = empService.page(page,pageSize);
return Result.success(pageResult);
}
}
package com.itheima.service;
import com.itheima.pojo.Emp;
import com.itheima.pojo.PageResult;
public interface EmpService {
/**
* 分页查询
* @param page 页码
* @param pageSize 每页记录数
* @return
*/
PageResult<Emp> page(Integer page, Integer pageSize);
}
package com.itheima.service.impl;
import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import com.itheima.pojo.PageResult;
import com.itheima.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class EmpServiceImpl implements EmpService {
@Autowired
private EmpMapper empMapper;
@Override
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);
}
}
package com.itheima.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.catalina.LifecycleState;
import java.util.List;
/**
* 分页结果封装类
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageResult<T> {
private Long total;
private List<T> rows;
}
package com.itheima.mapper;
import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* 员工信息
*/
@Mapper
public interface EmpMapper {
/**
* 查询总记录数
* @return
*/
@Select("select count(*) from emp e left join dept d on e.dept_id = d.id;")
public Long count();
/**
* 分页查询
* @return
*/
@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);
}
Controller不用改，其他两个要改
@Override
public PageResult<Emp> page(Integer page, Integer pageSize) {
//1.设置分页参数
PageHelper.startPage(page,pageSize);
//2.执行查询
List<Emp> rows = empMapper.list();
//3.解析结果，封装结果 PageResult
//page的父类是ArrayList，ArrayList的父类是List，可以父转子强转；
Page<Emp> p = (Page<Emp>) rows;
return new PageResult<Emp>(p.getTotal(),p.getResult());
}
@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();
PageHelper的两步改造
第一步，计数
第二步，limit
第三步，将这些数据全部封装到Page类中
/**
* 基于PageHelper查询
* @param page 页码
* @param pageSize 每页记录数
* 注意事项：
* 1.定义的SQL语句结尾不可以加分号，因为底层是SQL注入，改造
* 2.PageHeleper仅仅能够对紧跟在其后面的第一个查询语句进行分页处理
* @return
*/
@Override
public PageResult<Emp> page(Integer page, Integer pageSize) {
//1.设置分页参数
PageHelper.startPage(page,pageSize);
//2.执行查询
List<Emp> rows = empMapper.list();
//3.解析结果，封装结果 PageResult
//page的父类是ArrayList，ArrayList的父类是List，可以父转子强转；
Page<Emp> p = (Page<Emp>) rows;
return new PageResult<Emp>(p.getTotal(),p.getResult());
}
#{}不能出现在引号之内
需要改成${}(不推荐)或字符串拼接concat('%',#{name},'%')（推荐）
<?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.itheima.mapper.EmpMapper">
<select id="list" resultType="com.itheima.pojo.Emp">
select e.*,d.name deptName from emp e left join dept d on e.dept_id = d.id
where
e.name like concat('%',#{name},'%')
and e.gender = #{gender}
and entry_date between #{begin} and #{end}
order by e.update_time desc
</select>
</mapper>
package com.itheima.controller;
import com.itheima.pojo.Emp;
import com.itheima.pojo.PageResult;
import com.itheima.pojo.Result;
import com.itheima.service.EmpService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.time.LocalDate;
/**
* 员工管理Controller
*/
@Slf4j
@RequestMapping("/emps")
@RestController
public class EmpController {
@Autowired
private EmpService empService;
/**
* 分页查询
* @return
*/
@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);
}
/**
* 条件分页查询
*/
}
package com.itheima.service;
import com.itheima.pojo.Emp;
import com.itheima.pojo.PageResult;
import org.springframework.format.annotation.DateTimeFormat;
import java.time.LocalDate;
public interface EmpService {
/**
* 分页查询
* @param page 页码
* @param pageSize 每页记录数
* @return
*/
PageResult<Emp> page(Integer page, Integer pageSize,
String name, Integer gender,
LocalDate begin,
LocalDate end);
}
package com.itheima.service.impl;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import com.itheima.pojo.PageResult;
import com.itheima.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.time.LocalDate;
import java.util.List;
@Service
public class EmpServiceImpl implements EmpService {
@Autowired
private EmpMapper empMapper;
/**
* 原始的分页查询
* @param page 页码
* @param pageSize 每页记录数
* @return
*/
// @Override
// 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);
// }
/**
* 基于PageHelper查询
* @param page 页码
* @param pageSize 每页记录数
* 注意事项：
* 1.定义的SQL语句结尾不可以加分号，因为底层是SQL注入，改造
* 2.PageHeleper仅仅能够对紧跟在其后面的第一个查询语句进行分页处理
* @return
*/
@Override
public PageResult<Emp> page(Integer page, Integer pageSize,String name, Integer gender, LocalDate begin, LocalDate end) {
//1.设置分页参数
PageHelper.startPage(page,pageSize);
//2.执行查询
List<Emp> rows = empMapper.list(name,gender,begin,end);
//3.解析结果，封装结果 PageResult
//page的父类是ArrayList，ArrayList的父类是List，可以父转子强转；
Page<Emp> p = (Page<Emp>) rows;
return new PageResult<Emp>(p.getTotal(),p.getResult());
}
}
package com.itheima.mapper;
import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.time.LocalDate;
import java.util.List;
/**
* 员工信息
*/
@Mapper
public interface EmpMapper {
/**
* 查询总记录数
* @return
*/
@Select("select count(*) from emp e left join dept d on e.dept_id = d.id;")
public Long count();
/**
* 分页查询
* @return
*/
//@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(String name, Integer gender, LocalDate begin, LocalDate end);
}
-- 员工表
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 '员工表';
INSERT INTO emp VALUES
(1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/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 '工作经历';
-- 要查询所有的员工信息，以及该员工归属的部门名称
select e.*,d.name from emp e left join dept d on e.dept_id = d.id;
-- 分页查询
-- limit 起始索引，每页展示记录数
-- 查询第一页，每页展示五条数据
select e.*,d.name from emp e left join dept d on e.dept_id = d.id limit 0,5;
-- 查询第二页，每页展示五条数据
select e.*,d.name from emp e left join dept d on e.dept_id = d.id limit 5,5;
-- 查询第三页，每页展示五条数据
select e.*,d.name from emp e left join dept d on e.dept_id = d.id limit 10,5;
-- 起始索引 = （页码-1）*每页展示记录数
-- 查询总记录数
select count(*) from emp e left join dept d on e.dept_id = d.id;
-- 条件查询
select e.*,d.name deptName from emp e left join dept d on e.dept_id = d.id
where e.name like '%阮%' and e.gender = 1 and entry_date between '2010-01-01' and '2020-01-01'
order by e.update_time desc;
select e.*,d.name deptName from emp e left join dept d on e.dept_id = d.id
where e.name like concat('%','阮','%') and e.gender = 1 and entry_date between '2010-01-01' and '2020-01-01'
order by e.update_time desc;
-- 字符串拼接 --%阮%---->% 阮 %
select concat('%','阮','%');
浙公网安备 33010602011771号