MybatisPlus 条件构造器(mybatisplus条件构造器多表查询)
条件构造器
条件构造器主要用来写一些复杂的sql
注意事项
不支持以及不赞成在 RPC 调用中把 Wrapper 进行传输
wrapper很重传输
wrapper可以类比为你的controller用map接收值(开发一时爽,维护火葬场)正确的
RPC调用姿势是写一个DTO进行传输,被调用方再根据DTO执行相应的操作
拼接方法
| 查询方法 | 说明 | 示例 |
|---|---|---|
eq | 等于 = | eq("username", "admin") ---> username = 'admin' |
ne | 等于 = | ne("username", "admin") ---> username <> 'admin' |
gt | 大于 > | gt("age", "18") ---> age > 18 |
ge | 大于等于 >= | ge("age", "18") ---> age >= 18 |
lt | 小于 < | lt("age", 18) ---> age < 18 |
le | 小于等于 <= | le("age", 18) ---> age <= 18 |
between | 在值1和值2之间 | between("age", 18, 24) ---> age BETWEEN 18 AND 24 |
notBetween | 不在值1和值2之间 | notBetween("age", 18, 24) ---> age NOT BETWEEN 18 AND 24 |
like | 模糊查询 | like("name", "张") ---> name LIKE '%张%' |
notLike | 不模糊查询内 | notLike("name", "张") ---> name NOT LIKE '%张%' |
likeLeft | 左模糊查询 | likeLeft("name", "三") ---> name LIKE '%三' |
likeRight | 右模糊查询内 | likeRight("name", "张") ---> name LIKE '张%' |
isNull | 字段为空 | isNull("name") ---> name IS NULL |
isNotNull | 字段不为空 | isNotNull("name") ---> name IS NOT NULL |
in | 在集合内 | in("age", 16,17,18) ---> age IN (16,17,18) |
notIn | 在集合内 | notIn("age", 16,17,18) ---> age NOT IN (16,17,18) |
inSql | 子查询 | in("id", "SELECT id FROM table WHERE id < 3") ---> id IN (SELECT id FROM table WHERE id < 3) |
notInSql | 子查询 | notInSql("id", "SELECT id FROM table WHERE id < 3") ---> id ONT IN (SELECT id FROM table WHERE id < 3) |
groupBy | 分组 | groupBy("age", "id") ---> GROUP BY id, name |
orderByAsc | 字段正序 | orderByAsc("age") ---> ORDER BY age ASC |
orderByDesc | 字段倒序 | orderByDesc("age") ---> ORDER BY age DESC |
having | 分组筛选 | having("sum(age) > 10")--->HAVING SUM(age) > 10 |
or | 或 | eq("id", 1).or().eq("name", "张三")--->id = 1 OR name = '张三' |
and | 且 | eq("id", 1).and().eq("name", "张三")--->id = 1 AND name = '张三' |
exists | 存在 | exists("SELECT id FROM table WHERE age = 1")--->EXISTS (SELECT id FROM table WHERE age = 1) |
notExists | 存在 | notExists("SELECT id FROM table WHERE age = 1")--->NOT EXISTS (SELECT id FROM table WHERE age = 1) |
isNotnull和ge演示
@Test void test1() { //查询name不为空并且邮箱不为空，年龄大于15 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper. isNotNull("name"). isNotNull("email"). ge("age", 15); userMapper.selectList(wrapper).forEach(System.out::println); } 复制代码eq和selectOne演示
@Test void test2() { //查询name=test2 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.eq("name", "test2"); //selectOne 只能查询一个，假如出现多个结果会报错 User user = userMapper.selectOne(wrapper); System.out.println(user); } 复制代码between和selectCount演示
@Test void test3() { //查询年龄在[20,30]之间的用户 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.between("age", 20, 30); long count = userMapper.selectCount(wrapper); System.out.println(count); } 复制代码notLike和likeRight和selectMaps演示
@Test void test4() { //name不包含bc，并且email以t开头（t%） QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper. notLike("name", "bc"). likeRight("email", "t"); List<Map<String, Object>> maps = userMapper.selectMaps(wrapper); maps.forEach(System.out::println); } 复制代码inSql和selectObjs和子查询演示
@Test void test5() { QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.inSql("id", "SELECT id FROM user WHERE id < 10"); List<Object> objects = userMapper.selectObjs(wrapper); objects.forEach(System.out::println); } 复制代码orderByDesc演示
@Test void test6() { QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.orderByDesc("id"); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
作者：万恶的沫白
链接：https://juejin.cn/post/7029178067163545636