基本分页查询

1 要求

1.1 请求

请求路径：/emps

请求方式：GET

接口描述：该接口用于员工列表数据的条件分页查询

样例: /emps?name=张&gender=1&begin=2007-09-01&end=2022-09-01&page=1&pageSize=10

1.2 响应

参数格式：application/json

{
  "code": 1,
  "msg": "success",
    //这里的data是个由页数和内容组成的bean
  "data": {
    "total": //总页数,
    "rows": [
      //具体分页内容
    ]
  }
}

2 实现

2.1 构建框架

2.2 编写配置文件

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mySQL://localhost:3306/webapp
    username: root
    password: root
mybatis:
  configuration:
  //开启log
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    //自动驼峰
    map-underscore-to-camel-case: true

2.3 在pom中添加pageHelper依赖

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.2</version>
        </dependency>

2.4 添加Emp类 (省略)

2.5 添加Response格式

//lombok
@Data
@AllArgsConstructor
@NoArgsConstructor

public class Result {
    private Integer code;
    private String msg;
    private Object data;

    public static Result success(){
        return new Result(1,"success",null);
    }

    public static Result success(Object obj){
        return new Result(1,"success",obj);
    }

    public static Result failed(){
        return new Result(2,"failed",null);
    }
}

2.6 添加pageBean类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class PageBean {
    private long total;
    private Object rows;
}

2.7 添加Controller

​ 此处Integer用@RequestParam() 需在括号中添加 Required = false, 否则出现 Integer cannot be null错

//控制反转IOC Inversion of Control
@RestController
@RequestMapping("emps")
public class EmpController {
    //自动依赖注入 Dependence Injection
    @Autowired
    private EmpService empService;

    //添加映射, 读取请求数据
    @GetMapping("")
    public Result retrieveAllEmp(
        //此处Integer用@RequestParam() 需在括号中添加 Required = false, 否则出现 Integer cannot be null错
        							String name, 
        							Integer gender, 
        							LocalDate begin, LocalDate end,
                                 @RequestParam(defaultValue = "1") Integer page,
                                 @RequestParam(defaultValue = "10") Integer pageSize){

        PageBean pageBean = empService.retrieveAllEmp(name, gender, begin, end, page , pageSize);
        return Result.success(pageBean);
    }
}

2.8 添加Service

​ 此处如果不传数据给mapper, 则mapper会把所有数据读取并response给pages由pageHeper显示

@Inversion of Control
@Service
public class EmpServiceImpl implements EmpService {
    //Dependence Injection
    @Autowired
    private EmpMapper empMapper;

    @Override
    public PageBean retrieveAllEmp(String name, Integer gender, LocalDate begin, LocalDate end, Integer page, Integer pageSize) {
		//先利用pageHelper创立Page, 输入初始页面和page大小
        PageHelper.startPage(page,pageSize);
        //由mapper读取页面数据, 生成list
        //此处如果不传数据给mapper, 则pageHelper会把所有数据读取并response
        List<Emp> list = empMapper.retrieveAllEmp(name, gender, begin, end);
        //强转list为Page类型
        Page pages = (Page) list;
        //pages.getTotal()读取总数
        //pages.getResult()读取页面数据
        PageBean pageBean = new PageBean(pages.getTotal(),pages.getResult());
        return pageBean;
    }
}

2.9 添加Mapper

@Mapper
public interface EmpMapper {

    List<Emp> retrieveAllEmp(String name, Integer gender, LocalDate begin, LocalDate end);
}

2.10 为Mapper编写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.itheima.mapper.EmpMapper">
    <select id="retrieveAllEmp">
        select * from emp
        <where>
            <if test="name!=null">
                and name like concat("%",#{name},"%")
            </if>
            <if test="gender!=null">
                and gender = #{gender}
            </if>
            <if test="begin!=null and end!=null">
                and create_time between begin and end
            </if>
        </where>
    </select>
</mapper>