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);
    }

posted @ 2022-04-27 13:22 DogLeftover 阅读(2133) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· Mybatis plus入门（二）：单表查询，自定义sql

· mybatis plus 总结

· mybatis-plus分页查询

· spring boot集成mybatis-plus——Mybatis Plus 分页查询数据（图解）

· spring boot集成mybatis-plus——Mybatis Plus 多表联查（包含分页关联查询,图文讲解）

阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？

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

目录

代码案例

我的标签

合集 (4)

随笔分类 (1350)

阅读排行榜

推荐排行榜

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

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

	@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";
	}

	@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

	@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

	@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";
	}

	@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";
	}

	@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";
	}

	@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";
	}

	@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";
	}

	@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";
	}

	@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";
	}

	@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;
	}

	@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);
	}