MybatisPlus 条件构造器

这是我参与11月更文挑战的第11天，活动详情查看：2021最后一次更文挑战

条件构造器

条件构造器主要用来写一些复杂的sql

注意事项

不支持以及不赞成在 RPC 调用中把 Wrapper 进行传输

1. wrapper 很重
2. 传输 wrapper 可以类比为你的 controller 用 map 接收值(开发一时爽,维护火葬场)
3. 正确的 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);  
}