Day10案例分页查询,条件查询

 对要求进行逻辑分析,传递固定参数{page,pagesize}+任意参数{name,gender,begin,end},返回总记录数以及当前页码的记录

不使用pagehelper插件,首先完成SQL语句

SQL语句

//固定头
<?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.yhw.mapper.EmpMapper">

    //定义一个sql语句等效于这个select,from,多次使用的时候会使页面更加简洁
    <sql id="commonSelect">
        select id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_time
        from emp
    </sql>

    //第一个select语句,对应函数名是page,返回类型是Emp
    <select id="page" resultType="com.yhw.pojo.Emp">
        
        <include refid="commonSelect"/>
        <where>//where完成自动and判重,if完成自动变量判空
            <if test="name != null">
                name like concat('%',#{name},'%')//这是上节课jiangly的模糊查询,查询带张的名
            </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
        limit #{start},#{pageSize}//如果你使用了mybatisX插件,这里会爆红,不用管它是正常的
    </select>

    //完成总记录数的查询
    <select id="count" resultType="Long">
        select count(*)
        from emp
        <where>
            <if test="name != null">
                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>
    </select>
</mapper>

Contoller

接下来完成Conroller层

@Slf4j
@RestController
public class EmpController {
    @Autowired
    private EmpService empService;
    @GetMapping("/emps")
    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){
        log.info("分页查询,参数:{},{},{},{},{},{}",page,pageSize,name,gender,begin,end);
        PageBean pageBean=empService.page(page,pageSize, name, gender, begin, end);
        return Result.success(pageBean);
    }
}

根据接口文档,我们使用Get方法,接收6个参数,page和pageSize使用@RequestParam设置默认值,这个PageBean要根据文档定义,是个类来的

@Data
@NoArgsConstructor
@AllArgsConstructor
public class PageBean {
    private Long total;
    private List rows;
}

使用注解简化书写,总记录数和总记录集合

下面完成EmpService

EmpService

@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) {
        Long count=empMapper.count(name,gender, begin, end);
        List<Emp> list= empMapper.page((page-1)*pageSize,pageSize,name,gender, begin, end);
        PageBean pageBean=new PageBean(count,list);
        return pageBean;
    }
}

这是实现,接口自己cv一下就好了,调用count方法拿到总记录数,page方法拿到集合

EmpMapper就很简单了

EmpMapper

Long count(String name, Short gender, LocalDate begin, LocalDate end);

List<Emp> page(Integer start, Integer pageSize,String name, Short gender, LocalDate begin, LocalDate end);

我们在xml里面都写好了,已经自动映射了.

点击查询按钮,查询成功.