Mybatis plus入门（一）：单表查询、分页

目录

• 根据id查询，使用BaseMapper接口的selectById方法，传入参数id
• 查询所有，使用BaseMapper接口的selectList方法，传入参数null
• 分页查询，调用BaseMapper的selectPage方法，传入参数IPage
• 按条件查询，使用BaseMapper的selectMaps方法，传入参数Wrappers，查询所有数据放到list
• 按条件查询，使用BaseMapper的selectList方法，传入参数QueryWrapper，并指定要查询的字段
• 按条件查询，使用BaseMapper的selectOne方法，传入参数QueryWrapper
• 按条件查询，使用BaseMapper接口的selectList方法，传入参数QueryWrapper
• 按条件查询，使用BaseMapper接口的selectList方法，传入参数QueryWrapper
• 按条件查询，使用BaseMapper接口的selectList方法传入参数QueryWrapper
• 分页条件查询，使用BaseMapper接口的selectPage方法，传入参数IPage和QueryWrapper
• 模糊、分页、统计总条数、使用BaseMapper的selectPage方法，传入参数Page和LambdaQueryWrapper
• 模糊、分页、不统计总条数、使用BaseMapper的selectPage方法，传入参数Page和LambdaQueryWrapper
• 模糊、分页、统计总条数、使用BaseMapper接口的selectMapsPage方法，传入参数Page和LambdaQueryWrapper
• 分页查询，使用BaseMapper接口的selectMapsPage方法，将结果集放到map集合传入参数Page和Wrappers
• 模糊、条件、使用BaseMapper接口的selectMapsPage方法，传入参数Page和LambdaQueryWrapper
• 模糊、条件、排序、使用BaseMapper接口的selectMapsPage方法，传入参数Page和LambdaQueryWrapper

代码案例

• 根据id查询，使用BaseMapper接口的selectById方法，传入参数id

    @GetMapping("/test2")
    @ResponseBody
    public String test2() {
        User user = userDao.selectById("1");
        System.out.println("user = " + user);
        return "success";
    }

• 查询所有，使用BaseMapper接口的selectList方法，传入参数null

    @GetMapping("/test1")
    @ResponseBody
    public String test1() {
        List<User> users = userDao.selectList(null);
        users.forEach(user-> System.out.println("user = " + user));
        return "success";
    }

• 分页查询，调用BaseMapper的selectPage方法，传入参数IPage

    @GetMapping("/test9")
    @ResponseBody
    public String test9() {
        // 创建page对象
        IPage<User> page = new Page<>(1,2);
        // 调用分页的方法
        page = userDao.selectPage(page, null);
        page.getRecords().forEach(user -> System.out.println("user = " + user));
        return "success";
    }

• 按条件查询，使用BaseMapper的selectMaps方法，传入参数Wrappers，查询所有数据放到list

    @RequestMapping(value = "/test5")
    @ResponseBody
    public String test5(){
        List<Map<String, Object>> mapList = mapper.selectMaps(Wrappers.<User>query().orderByAsc("age"));
        assertThat(mapList).isNotEmpty();
        assertThat(mapList.get(0)).isNotEmpty();
        System.out.println("mapList: " + mapList.get(0));
        return "success";
    }

# 控制台
SELECT id,name,age,email FROM user ORDER BY age ASC

• 按条件查询，使用BaseMapper的selectList方法，传入参数QueryWrapper，并指定要查询的字段

    @RequestMapping(value = "/test12")
    @ResponseBody
    public String test12(){
        mapper.selectList(new QueryWrapper<User>().select("id", "name"))
                .forEach(x -> {
                    System.out.println(x.getId() + ":" + x.getName() + ":" + x.getAge() + ":" + x.getEmail() );
                });
        return "success";
    }

1:Jone:null:null
2:Jack:null:null
3:Tom:null:null
5:Billie:null:null

• 按条件查询，使用BaseMapper的selectOne方法，传入参数QueryWrapper

    @RequestMapping(value = "/test12")
    @ResponseBody
    public String test12(){
        User user = mapper.selectOne(new QueryWrapper<User>().lambda().eq(User::getId, 1));
        System.out.println(user);
        return "success";
    }

• 按条件查询，使用BaseMapper接口的selectList方法，传入参数QueryWrapper

    @GetMapping("/test3")
    @ResponseBody
    public String test3() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        // 设置等值查询，即查询age=23的对象
        queryWrapper.eq("age",23);
        // 设置小于查询
        //queryWrapper.lt("age",23);
        // 小于等于查询
        //queryWrapper.ge("age",23);
        // gt 大于
        // ge 大于等于
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }

• 按条件查询，使用BaseMapper接口的selectList方法，传入参数QueryWrapper

    @RequestMapping("/test5")
    @ResponseBody
    public String test5() {
        // 查询role_id为2的对象
        List<User> plainUsers = userMapper.selectList(new QueryWrapper<User>().eq("role_id", 1L));
        List<User> lambdaUsers = userMapper.selectList(new QueryWrapper<User>().lambda().eq(User::getRoleId, 2L));
        System.out.println(plainUsers);
        System.out.println(lambdaUsers);
        return "success";
    }

• 按条件查询，使用BaseMapper接口的selectList方法传入参数QueryWrapper

    @RequestMapping("/test5")
    @ResponseBody
    public String test5() {
        List<User> plainUsers4 = userMapper.selectList(new QueryWrapper<User>()
                .apply("role_id = 2"));
        // 方式二
        List<User> plainUsers5 = userMapper.selectList(new QueryWrapper<User>()
                .apply("role_id = {0}",2));
        return "success";
    }

# 控制台
SELECT id,name,age,email,role_id FROM user WHERE (role_id = 2)
SELECT id,name,age,email,role_id FROM user WHERE (role_id = ?)

• 分页条件查询，使用BaseMapper接口的selectPage方法，传入参数IPage和QueryWrapper

    @GetMapping("/test10")
    @ResponseBody
    public String test10() {
        // new一个条件对象，例如age=99
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("age",99);
        // new一个page对象
        IPage<User> page = new Page<>(1,2);
        // 调用分页的方法，传入2个参数
        page = userDao.selectPage(page, queryWrapper);
        page.getRecords().forEach(user-> System.out.println("user = " + user));
        return "success";
    }

• 模糊、分页、统计总条数、使用BaseMapper的selectPage方法，传入参数Page和LambdaQueryWrapper

    @RequestMapping("/test1")
    @ResponseBody
    public String test1(){
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        // 模糊查询
        userLambdaQueryWrapper.like(User::getUsername , "a");
        // 分页查询
        Page<User> userPage = new Page<>(1, 3);
        IPage<User> userIPage = userMapper.selectPage(userPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+userIPage.getPages());
        System.out.println("总记录数： "+userIPage.getTotal());
        // 打印查询出的所有数据
        userIPage.getRecords().forEach(System.out::println);
        return "success";
    }

• 模糊、分页、不统计总条数、使用BaseMapper的selectPage方法，传入参数Page和LambdaQueryWrapper

    @RequestMapping("/test2")
    @ResponseBody
    public String test2(){
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        userLambdaQueryWrapper.like(User::getUsername , "a");
        Page<User> userPage = new Page<>(1, 2, false);
        IPage<User> userIPage = userMapper.selectPage(userPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+userIPage.getPages());
        System.out.println("总记录数： "+userIPage.getTotal());
        userIPage.getRecords().forEach(System.out::println);
        return "success";
    }

• 模糊、分页、统计总条数、使用BaseMapper接口的selectMapsPage方法，传入参数Page和LambdaQueryWrapper

    @RequestMapping("/test3")
    @ResponseBody
    public String test3(){
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        // 模糊查询
        userLambdaQueryWrapper.select(User::getUsername).like(User::getUsername , "a");
        // 分页
        Page<Map<String , Object>> mapPage = new Page<>(1, 3, true);
        IPage<Map<String , Object>> mapIPage = userMapper.selectMapsPage(mapPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+mapIPage.getPages());
        System.out.println("总记录数： "+mapIPage.getTotal());
        // 打印查询到的数据
        mapIPage.getRecords().forEach(System.out::println);
        return "success";
    }

• 分页查询，使用BaseMapper接口的selectMapsPage方法，将结果集放到map集合传入参数Page和Wrappers

    @RequestMapping(value = "/test6")
    @ResponseBody
    public String test6(){
        IPage<Map<String, Object>> page = mapper.selectMapsPage(
                new Page<>(1, 5, true), Wrappers.<User>query().orderByAsc("age")
        );
        System.out.println("total: " + page.getTotal());
        System.out.println("pages: " + page.getPages());
        System.out.println("pageList: " + page.getRecords());
        return "success";
    }

• 模糊、条件、使用BaseMapper接口的selectMapsPage方法，传入参数Page和LambdaQueryWrapper

    @Override
    public IPage<PlateSet> getPlateSetPag(PlateSetPagDTO plateSetPagDTO) {
        LambdaQueryWrapper<PlateSet> plateSetLambdaQueryWrapper = Wrappers.lambdaQuery();
        // 模糊
        plateSetLambdaQueryWrapper.like(plateSetPagDTO.getId() != null, PlateSet::getId, plateSetPagDTO.getId());
        plateSetLambdaQueryWrapper.like(plateSetPagDTO.getName() != null, PlateSet::getName, plateSetPagDTO.getName());
        // 条件查询
        plateSetLambdaQueryWrapper.eq(plateSetPagDTO.getZid() != null, PlateSet::getZid, plateSetPagDTO.getZid());
        plateSetLambdaQueryWrapper.eq(plateSetPagDTO.getGoverning() != null, PlateSet::getGoverning, plateSetPagDTO.getGoverning());
        plateSetLambdaQueryWrapper.eq(plateSetPagDTO.getAffiliation() != null, PlateSet::getAffiliation, plateSetPagDTO.getAffiliation());
        // 分页
        Page<PlateSet> plateSetPage = new Page<>(plateSetPagDTO.getPageNo(), plateSetPagDTO.getPageSize());
        IPage<PlateSet> plateSetIPage = plateSetMapper.selectPage(plateSetPage, plateSetLambdaQueryWrapper);
        return plateSetIPage;
    }

• 模糊、条件、排序、使用BaseMapper接口的selectMapsPage方法，传入参数Page和LambdaQueryWrapper

    @Override
    public IPage<Vendor> getAllVendorPage(VendorPageDTO vendorPageDTO) {
        LambdaQueryWrapper<Vendor> vendorPageLambdaQueryWrapper = new LambdaQueryWrapper<Vendor>();
            vendorPageLambdaQueryWrapper
                .like(vendorPageDTO.getVendorCode() != null, Vendor::getVendorCode, vendorPageDTO.getVendorCode())
                .like(vendorPageDTO.getVendorLocality() != null, Vendor::getVendorLocality, vendorPageDTO.getVendorLocality())
                .like(vendorPageDTO.getVendorName() != null, Vendor::getVendorName, vendorPageDTO.getVendorName())
                .like(vendorPageDTO.getRemark() != null, Vendor::getRemark, vendorPageDTO.getRemark())
                .eq(vendorPageDTO.getId() != null, Vendor::getId, vendorPageDTO.getId());
            // 按更新时间降序
            vendorPageLambdaQueryWrapper.orderByDesc(Vendor::getUpdated);
        Page<Vendor> vendorPage = new Page<>(vendorPageDTO.getPageNo(), vendorPageDTO.getPageSize());
        return vendorMapper.selectPage(vendorPage, vendorPageLambdaQueryWrapper);
    }