MyBatis-Plus分页以及常用条件构造器

书接上回: MyBatis-Plus的CRUD
会包含一些Java8新特性的使用
在上一篇的配置下继续进行分页以及条件构造器的学习测试

条件构造器

Wrapper：条件构造抽象类，最顶端父类

• AbstractWrapper：用于查询条件封装，生成sql的where条件
  • QueryWrapper：查询条件封装
  • UpdateWrapper：Update条件封装
    • LambdaQueryWrapper：用于Lambda语法使用的查询Wrapper
    • LambdaUpdateWrapper：Lambda更新封装Wrapper

昨天的CRUD测试已经用到过QueryWrapper这种方式，今天就使用一些其他的方法

1.QueryWrapper

 /**
     * 测试子查询
     * 
     * SELECT sid,sname,sex,age,birthday,hobby,province FROM student WHERE (sid IN (select sid from student where sex = '男'))
     */
    @Test
    void testSonSelect(){
        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
        queryWrapper.inSql("sid","select  sid from student where sex = '男'");
        List<Student> students = studentMapper.selectList(queryWrapper);
        students.forEach(System.out::println);
    }

/**
     * 测试组装条件
     * SELECT sid,sname,sex,age,birthday,hobby,province FROM student WHERE (hobby LIKE ?)
     */
    @Test
    void testAssembleCondition(){
        String sname = "";
        String hobby = "皮卡";
        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
        if (StringUtils.isNotBlank(sname)) {
            //isNotBlank判断字符串是否不为null，不为空字符串，不为空白符
            queryWrapper.like("sname",sname);
        }
        if (StringUtils.isNotBlank(hobby)) {
            queryWrapper.like("hobby", hobby);
        }
        List<Student> students = studentMapper.selectList(queryWrapper);
        students.forEach(System.out::println);
    }

/**
     * boolean condition,条件符合则组装条件
     * SELECT sid,sname,sex,age,birthday,hobby,province FROM student WHERE (hobby LIKE ?)
     */
    @Test
    void testCondition(){
        String sname = "";
        String hobby = "皮卡";
        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(sname),"sname",sname)
                .like(StringUtils.isNotBlank(hobby),"hobby",hobby);
        List<Student> students = studentMapper.selectList(queryWrapper);
        students.forEach(System.out::println);
    }

2.UpdateWrapper

/**
     * 测试updateWrapper
     * 将用户名包含张且sex=男或者为null的用户信息修改
     * 其实使用QueryWrapper效果一样（目前看来）
     * UPDATE student SET sname=?,sex=? WHERE (sname LIKE ? AND (sex LIKE ? OR sex IS NULL))
     */
    @Test
    void testUpdateWrapper(){
        UpdateWrapper<Student> updateWrapper = new UpdateWrapper<>();
        updateWrapper.like("sname","张")
        //i指代一个条件构造器， -> 是lambda表达式
                .and(i -> i.like("sex","男").or().isNull("sex"));
        updateWrapper.set("sname","李四").set("sex","女");
        //因为使用了修改条件构造器，所以实体可以设置为null
        int update = studentMapper.update(null, updateWrapper);
        Assert.assertEquals(update,8);
    }

3.LambdaQueryWrapper

/**
     * LambdaQueryWrapper
     *模糊查询hobby中含有皮卡的，年龄在10到20之间
     * SELECT sid,sname,sex,age,birthday,hobby,province FROM student WHERE (hobby LIKE ? AND age >= ? AND age <= ?)
     */
    @Test
    void testLambdaQuery(){
        String sname = "";
        String hobby = "皮卡";
        Integer start = 10;
        Integer end = 20;
        LambdaQueryWrapper<Student> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(sname),Student::getSname,sname)
                .like(StringUtils.isNotBlank(hobby),Student::getHobby,hobby)
                //ge-----greater than 大于
                .ge(Student::getAge,start)
                //le ----- less then  小于
                .le(Student::getAge,end);
        List<Student> students = studentMapper.selectList(queryWrapper);
        students.forEach(System.out::println);
    }

4.LambdaUpdateWrapper

/**
     * LambdaUpdate
     * UPDATE student SET age=? WHERE ((hobby LIKE ?))
     */
    @Test
    void testLambdaUpdate(){
        String sname = "";
        String hobby = "打";
        LambdaUpdateWrapper<Student> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.like(StringUtils.isNotBlank(sname),Student::getSname,sname)
                .and(i-> i.like(Student::getHobby,hobby));
        updateWrapper.set(Student::getAge,15);
        studentMapper.update(null,updateWrapper);

    }

Page分页

1.配置与上篇一致

2.编写mp的插件配置

@Configuration
@MapperScan("com/lqz/springboot11mybatisplus/mapper")
public class MyBatisPlusConfig {

    /**
     * 配置mp拦截器,分页插件
     * @return
     */
    @Bean
    public MybatisPlusInterceptor getMybatisPlusInterceptor(){
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        //添加分页插件，指定数据库类型MYSQL
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return mybatisPlusInterceptor;
    }
}

3. 自定义分页方法

 public interface StudentMapper extends BaseMapper<Student> {

    /**
     * 通过年龄查询学生信息并分页
     * @param page MyBatis-Plus提供的分页对象，必须位于第一个参数的位置
     * @param age
     * @return
     */
    Page<Student> selectPageVo(@Param("page") Page<Student> page,@Param("age") Integer age);

}

4. 编写service

    Page<Student> selectPageVo(@Param("page") Page<Student> page, @Param("age") Integer age);

5.编写serviceImpl

@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student>
    implements StudentService{

    @Resource
    private StudentMapper mapper;

    @Override
    public Page<Student> selectPageVo(Page<Student> page, Integer age) {
        return mapper.selectPageVo(page, age);
    }
}

6. 测试

@SpringBootTest
public class MyBatisPlusPluginsTest {

    @Resource
    private StudentMapper mapper;

    /**
     * 分页
     * 第一页
     * SELECT sid,sname,sex,age,birthday,hobby,province FROM student LIMIT ?
     * 第二页
     * SELECT sid,sname,sex,age,birthday,hobby,province FROM student LIMIT ?,?
     */
    @Test
    public void testPage(){
        //了解Page  API使用
        Page<Student> page = new Page<>(3,5);
        mapper.selectPage(page, null);
        System.out.println("获取当前页数据" + page.getRecords());
        System.out.println("获取当前页"+page.getCurrent());
        System.out.println("获取显示多少记录"+page.getSize());
        System.out.println("共多少页" + page.getPages());
        System.out.println("获取总记录" + page.getTotal());
        System.out.println("是否有下一页" + page.hasNext());
        System.out.println("是否有上一页" + page.hasPrevious());
    }

    /**
     * 测试自定义的分页方法
     */
    @Test
    public void testPage2(){
        Page<Student> page = new Page<>(3,3);
        mapper.selectPageVo(page,3);
        System.out.println(page.hasNext());
    }
}

7. 编写controller，模拟分页参数由用户传入

@RestController
public class StudentController {

    @Autowired
    private StudentService studentService;

    @GetMapping("/stu")
    public String getAllStudent(){
        List<Student> list = studentService.list();
        return list.toString();
    }

    /**
     * 按年龄分页
     * @param curr 当前页
     * @param size 每页显示多少条
     * @param age 年龄
     * @return
     */
    @GetMapping("/pages")
    public Result queryPage(Integer curr, Integer size, Integer age){
        //todo 数据校验
        Page<Student> studentPage = new Page<>(curr,size);
        studentService.selectPageVo(studentPage, age);
        return new Result(studentPage);
    }
}

8.测试

请求地址：http://localhost:9000/pages?curr=1&size=3&age=16
{
    "records": [
        {
            "sid": 1,
            "sname": "孙小鸟",
            "sex": "男",
            "age": 19,
            "birthday": "2020-09-16T16:00:00.000+00:00",
            "hobby": "平时喜欢看影",
            "province": "湖南省"
        },
        {
            "sid": 6,
            "sname": "曹老板",
            "sex": "女",
            "age": 50,
            "birthday": "2022-05-18T16:00:00.000+00:00",
            "hobby": "打豆豆",
            "province": "湖北省"
        }
    ],
    "total": 2,
    "size": 3,
    "current": 1,
    "orders": [],
    "optimizeCountSql": true,
    "searchCount": true,
    "countId": null,
    "maxLimit": null,
    "pages": 1
}