参考表

本章内容基于该张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的几个源码便大家理解，感兴趣的点开这里看看