分页查询（带条件）

1.pagehelper

①导依赖

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

②分页工具类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class PageBean {
 private Long total; //总记录数
 private List rows; //当前页数据列表
}

③Controller

    @ApiOperation(value = "分页查询")
    @GetMapping("/list")
    public R<?> page(@RequestParam(defaultValue = "1") Integer page,
                     @RequestParam(defaultValue = "10") Integer pageSize,
                     String keyWordy) {
        //调用业务层分页查询
        PageBean pageBean = employeeService.pageQuey(page, pageSize,keyWordy);
        return R.success(pageBean);

    }

④Service

 @Override
    public PageBean pageQuey(Integer page, Integer pageSize, String keyWordy) {
        //设置分页参数
        PageHelper.startPage(page,pageSize);
        //执行分页条件查询
        List<Employee>employeeList=employeeMapper.List(keyWordy);
        //获取查询结果
        Page<Employee>p=(Page<Employee>) employeeList;
        //封装pageBen p.getTotal（）获得总条目数 p.getResult()获取当前页的条目数
        PageBean pageBean=new PageBean(p.getTotal(),p.getResult());
        return pageBean;
    }

⑤mapper

    <select id="List" resultType="com.test.entity.Employee">
        select *from db_test.employee
        <where>
            <if test="keyWordy !=null and keyWordy !=''">
                name like concat('%',#{keyWordy},'%') or
                username like concat('%',#{keyWordy},'%')
            </if>
        </where>
    </select>

2.mybatispuls

@ApiOperation(value = "员工信息分页查询")
    @GetMapping("/page")
    public R<Page> page(int page, int pageSize, String name, String username) {
        log.info("page = {},pageSize = {},name = {}", page, pageSize, name, username);

        //构造分页构造器
        Page pageInfo = new Page(page, pageSize);

        //构造条件构造器
        LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper();
        //添加过滤条件
        queryWrapper.like(StringUtils.isNotEmpty(name), Employee::getName, name);
        //添加排序条件
        queryWrapper.orderByDesc(Employee::getUpdateTime);

        //执行查询
        employeeService.page(pageInfo, queryWrapper);

        return R.success(pageInfo);
    }

@ApiOperation("分页")
    @GetMapping("/page")
    public R<EmployeePageDTO> page(EmployeeDTO queryDTO,
                                   @RequestParam(name = "page", defaultValue = "1") Integer page,
                                   @RequestParam(name = "pageSize", defaultValue = "3") Integer pageSize) {
        //Page page1=new Page(1,10);
        LambdaQueryWrapper<Employee> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper
                .like(StringUtils.isNotBlank(queryDTO.getUsername()), Employee::getUsername, queryDTO.getUsername())
                .or()
                .like(StringUtils.isNotBlank(queryDTO.getName()), Employee::getName, queryDTO.getName())
                .or()
                .like(StringUtils.isNotBlank(queryDTO.getPhone()), Employee::getPhone, queryDTO.getPhone())
                .or()
                .eq(ObjectUtils.isNotEmpty(queryDTO.getStatus()), Employee::getStatus, queryDTO.getStatus());

        // 时间范围筛选条件
        if (StringUtils.isNotBlank(queryDTO.getStartDate()) && StringUtils.isNotBlank(queryDTO.getEndDate())) {
            LocalDate startDate = LocalDate.parse(queryDTO.getStartDate());
            LocalDate endDate = LocalDate.parse(queryDTO.getEndDate());

            // 大于等于开始日期
            lambdaQueryWrapper.ge(Employee::getCreateTime, startDate.atStartOfDay());
            // 小于等于结束日期
            lambdaQueryWrapper.le(Employee::getCreateTime, endDate.atTime(LocalTime.MAX));
        }

        Page<Employee> employeePage = employeeService.page(new Page<>(page, pageSize), lambdaQueryWrapper);

        EmployeePageDTO pageDTO = new EmployeePageDTO();
        pageDTO.setRows(employeePage.getRecords());
        pageDTO.setTotal(employeePage.getTotal());

        return R.success(pageDTO);
    }

 @GetMapping("/list")
    public R<Page> pageQuery(EmployeeDTO employeeDTO) {
        //构造分页构造器
        Page pageInfo = new Page(employeeDTO.getPage(),employeeDTO.getPageSize());

        //构造条件构造器
        LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper();
        //添加过滤条件
        queryWrapper.like(StringUtils.isNotEmpty(employeeDTO.getName()), Employee::getName,employeeDTO.getName());
        //添加排序条件
        queryWrapper.orderByDesc(Employee::getUpdateTime);

        //执行查询
        employeeService.page(pageInfo, queryWrapper);
        return R.success(pageInfo);
    }