MybatisPlus较全常用复杂查询引例（limit、orderby、groupby、having、like...）

最新推荐文章于 2025-02-26 00:29:57 发布

亚伯拉罕·冯

最新推荐文章于 2025-02-26 00:29:57 发布

阅读量1.7w

点赞数 9

分类专栏：笔记文章标签： mybatis spring boot

本文链接：https://blog.youkuaiyun.com/FHlang/article/details/111053977

版权

笔记专栏收录该内容

9 篇文章

订阅专栏

本文通过实际示例介绍了MybatisPlus在Spring Boot项目中如何进行分页、求和、排序、分组、模糊查询等复杂操作，并建议深入源码以理解更复杂的查询需求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考表

本章内容基于该张data表进行复杂查询的引例参考
在这里插入图片描述

SQL语句对应的引例

分页limit

SELECT * FROM DATA LIMIT 6
SELECT * FROM DATA LIMIT 5,6
SELECT * FROM DATA WHERE sales <= 25 LIMIT 6

dataMapper.selectPage(new Page<data>(1,6),null);
dataMapper.selectPage(new Page<data>(2,6),null);
dataMapper.selectPage(new Page<data>(1,6),new QueryWrapper<data>().le("sales",25));

求和sum与count

SELECT COUNT(*) FROM DATA
SELECT COUNT(*) FROM DATA WHERE profit >=200
SELECT SUM(sales) FROM DATA

dataMapper.selectCount(null);
dataMapper.selectCount(new QueryWrapper<data>().ge("profit",200));
dataMapper.selectList(null).stream().mapToDouble(data::getSales).sum();

查询单个与多个字段

SELECT NAME FROM DATA WHERE profit >= 200
SELECT NAME,sales FROM DATA WHERE profit >= 200

dataMapper.selectList(new QueryWrapper<data>(null).select("name"));
dataMapper.selectList(new QueryWrapper<data>(null).select("name","sales"));

排序order by

SELECT * FROM DATA ORDER BY profit DESC
SELECT * FROM DATA ORDER BY profit ASC

dataMapper.selectList(new QueryWrapper<data>(null).orderByDesc("profit"));
dataMapper.selectList(new QueryWrapper<data>(null).orderByAsc("profit"));

分组group by与having筛选

SELECT * FROM DATA GROUP BY sales HAVING price > 1600
SELECT * FROM DATA GROUP BY sales HAVING MIN(sales) > 15 AND MAX(sales) < 30

dataMapper.selectList(new QueryWrapper<data>(null).groupBy("sales").having("price > 1600"));
dataMapper.selectList(new QueryWrapper<data>(null).groupBy("sales").having("MIN(sales) > {0} AND MAX(sales) < {1}",15,30));

模糊查询like

SELECT * FROM DATA WHERE NAME LIKE '%单人%'

dataMapper.selectList(new QueryWrapper<data>(null).like("name","%单人%"));

存在exist与in

SELECT * FROM DATA WHERE id IN (3,6,9)
SELECT * FROM DATA WHERE EXISTS(SELECT * FROM DATA WHERE id = 12)

dataMapper.selectList(new QueryWrapper<data>(null).in("id",3,6,9));
dataMapper.selectList(new QueryWrapper<data>(null).exists("SELECT * FROM DATA WHERE id = 12"));

寻空is null

SELECT * FROM DATA WHERE inventory IS NULL

dataMapper.selectList(new QueryWrapper<data>(null).isNull("inventory"));

看源码

当然以上引例也许还不远够全，要想写更加复杂的就一定得去读源码了，以下分享QueryWrapper的几个源码便大家理解，感兴趣的点开这里看看