员工管理
分页查询
请求参数:页码、每页展示记录数
响应结果:总记录数、结果列表 (PageBean)
@RequestParam 的属性defaultValue可以来设置参数的默认值
PageBean分页查询结果封装类
package com.example.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
/**
* 分页查询结果的封装类
* @author hyk~
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class PageBean {
private Long total; //总记录数
private List rows; //数据列表
}
EmpController类
package com.example.controller;
import com.example.mapper.EmpMapper;
import com.example.pojo.Emp;
import com.example.pojo.PageBean;
import com.example.pojo.Result;
import com.example.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.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
/**
* @author hyk~
*/
@Slf4j
@RestController
@RequestMapping("/emps")
public class EmpController {
@Autowired
private EmpService empService;
@GetMapping
public Result page(@RequestParam(defaultValue = "1") Integer page,
@RequestParam(defaultValue = "10")Integer pageSize){
//page:分页查询的页码,如果未指定,默认为1
//pageSize:分页查询的每页记录数,如果未指定,默认为10
/* 较为繁琐 通过 @RequestParam(defaultValue = " ") 来设置默认值
if (page == null){
page = 1;
}
if (pageSize == null){
pageSize = 10;
}
*/
log.info("分页查询参数:{},{}",page,pageSize);
//调用service分页查询
PageBean pageBean = empService.page(page,pageSize);
return Result.success(pageBean);
}
}
EmpService 接口
package com.example.service;
import com.example.pojo.Emp;
import com.example.pojo.PageBean;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author hyk~
*/
@Service
public interface EmpService {
//分页查询
PageBean page(Integer page, Integer pageSize);
}
EmpServiceImpl实现类
package com.example.service.impl;
import com.example.mapper.EmpMapper;
import com.example.pojo.Emp;
import com.example.pojo.PageBean;
import com.example.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author hyk~
*/
@Service
public class EmpServiceImpl implements EmpService {
@Autowired
private EmpMapper empMapper;
@Override
public PageBean page(Integer page, Integer pageSize) {
//1.获取总记录数
Long count = empMapper.count();
//2.获取分页查询的结果列表
//page:页码 不是起始索引 pageSize:每页记录数
//计算起始索引: 起始索引 = {页码-1} * 每页展示记录数
Integer start = (page-1)*pageSize;
List<Emp> empList = empMapper.page(start,pageSize);
//3.分装PageBean对象
PageBean pageBean = new PageBean(count,empList);
return pageBean;
}
}
EmpMapper接口
package com.example.mapper;
import com.example.pojo.Emp;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* @author hyk~
*/
@Mapper
public interface EmpMapper {
//查询总记录数
@Select("select count(*) from emp")
Long count();
//分页查询 获取列表数据
@Select("select * from emp limit #{start},#{pageSize}")
List<Emp> page(Integer start,Integer pageSize);
}
步骤固定 代码繁琐
分页插件PageHelper
在pom.xml中引入依赖
<!--PageHelper分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.2</version>
</dependency>
修改EmpMapper接口中的代码 只需正常查询全部数据
package com.example.mapper;
import com.example.pojo.Emp;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* @author hyk~
*/
@Mapper
public interface EmpMapper {
//查询总记录数
//@Select("select count(*) from emp")
//Long count();
//分页查询 获取列表数据
//@Select("select * from emp limit #{start},#{pageSize}")
//List<Emp> page(Integer start,Integer pageSize);
//员工信息查询
@Select("select * from emp")
List<Emp> list();
}
修改EmpServiceImpl实现类中的代码
package com.example.service.impl;
import com.example.mapper.EmpMapper;
import com.example.pojo.Emp;
import com.example.pojo.PageBean;
import com.example.service.EmpService;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author hyk~
*/
@Service
public class EmpServiceImpl implements EmpService {
@Autowired
private EmpMapper empMapper;
// @Override
// public PageBean page(Integer page, Integer pageSize) {
// //1.获取总记录数
// Long count = empMapper.count();
//
// //2.获取分页查询的结果列表
// //page:页码 不是起始索引 pageSize:每页记录数
// //计算起始索引: 起始索引 = {页码-1} * 每页展示记录数
// Integer start = (page-1)*pageSize;
// List<Emp> empList = empMapper.page(start,pageSize);
//
// //3.分装PageBean对象
// PageBean pageBean = new PageBean(count,empList);
//
// return pageBean;
// }
@Override
public PageBean page(Integer page, Integer pageSize) {
//1.设置分页参数
PageHelper.startPage(page,pageSize);
//2.执行查询
List<Emp> empList = empMapper.list();
Page<Emp> p = (Page<Emp>) empList;
//3.分装PageBean对象
PageBean pageBean = new PageBean(p.getTotal(),p.getResult());
return pageBean;
}
}
小结
1.PageHelper分页插件
引入依赖:
pagehelper-spring-boot-starter
使用:
PageHelper.startPage(pageNum, pageSize);
List<Emp> list = empMapper.list();
Page<Emp> page =(Page<Emp>)list;
条件分页查询
-- 条件查询员工 -姓名 性别 入职时间
select *
from emp
where name like concat('%','张','%')
and gender = 1
and entrydate between '2000-01-01' and '2010-01-01'
order by update_time desc;
EmpController类
@Slf4j
@RestController
@RequestMapping("/emps")
public class EmpController {
@Autowired
private EmpService empService;
@GetMapping
public Result page(@RequestParam(defaultValue = "1") Integer page,
@RequestParam(defaultValue = "10")Integer pageSize,
String name, Short gender,
@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){
//page:分页查询的页码,如果未指定,默认为1
//pageSize:分页查询的每页记录数,如果未指定,默认为10
/* 较为繁琐 通过 @RequestParam(defaultValue = " ") 来设置默认值
if (page == null){
page = 1;
}
if (pageSize == null){
pageSize = 10;
}
*/
log.info("分页查询参数:{},{},{},{},{},{}",page,pageSize,name,gender,begin,end);
//调用service分页查询
PageBean pageBean = empService.page(page,pageSize,name,gender,begin,end);
return Result.success(pageBean);
}
}
EmpService接口
@Service
public interface EmpService {
//分页查询
PageBean page(Integer page, Integer pageSize, String name, Short gender,LocalDate begin,LocalDate end);
}
EmpServiceImpl类
@Service
public class EmpServiceImpl implements EmpService {
@Autowired
private EmpMapper empMapper;
@Override
public PageBean page(Integer page, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end) {
//1.设置分页参数
PageHelper.startPage(page,pageSize);
//2.执行查询
List<Emp> empList = empMapper.list(name,gender,begin,end);
//强转成Page类型
Page<Emp> p = (Page<Emp>) empList;
//3.分装PageBean对象
PageBean pageBean = new PageBean(p.getTotal(),p.getResult());
return pageBean;
}
}
EmpMapper接口
@Mapper
public interface EmpMapper {
//员工信息查询
List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);
}
EmpMapper.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.example.mapper.EmpMapper">
<!--条件查询-->
<select id="list" resultType="com.example.pojo.Emp">
select *
from emp
<where>
<if test="name != null and name != ''">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate BETWEEN #{begin} AND #{end}
</if>
</where>
order by update_time desc
</select>
</mapper>
小结
1.条件分页查询
条件查询:动态SQL- XML映射文件
分页查询: PageHelper分页插件
删除员工
EmpController类
@DeleteMapping("/{ids}")
public Result delete(@PathVariable List<Integer> ids){
log.info("批量删除操作,ids:{}",ids);
empService.delete(ids);
return Result.success();
}
EmpService接口
@Service
public interface EmpService {
//分页查询
PageBean page(Integer page, Integer pageSize, String name, Short gender,LocalDate begin,LocalDate end);
//批量删除操作
void delete(List<Integer> ids);
}
实现方法
@Override
public void delete(List<Integer> ids) {
empMapper.deleteByIds(ids);
}
EmpMapper接口
//批量删除员工
void deleteByIds(List<Integer> ids);
EmpMapper.xml文件
<!--批量删除员工-->
<delete id="deleteByIds">
delete from emp where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>