一、条件构造器关系介绍
![](https://i-blog.csdnimg.cn/direct/41cbbb2e61144273a75484f6ed9f44f6.png)
条件构造器关系介绍 :
上图绿色框为抽象类abstract
蓝色框为正常class类,可new对象
黄色箭头指向为父子类关系,箭头指向为父类
wapper介绍 :
Wrapper : 条件构造抽象类,最顶端父类
AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
QueryWrapper : Entity 对象封装操作类,不是用lambda语法
UpdateWrapper : Update 条件封装,用于Entity对象更新操作
AbstractLambdaWrapper : Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column。
LambdaQueryWrapper :看名称也能明白就是用于Lambda语法使用的查询Wrapper
LambdaUpdateWrapper : Lambda 更新封装Wrapper
二、实例说明
1、MyBatis-Plus还提供了Wrapper条件构造器,具体使用看如下代码:
/**
* 名字包含雨并且年龄小于40
* <p>
* WHERE name LIKE '%雨%' AND age < 40
*/
@Test
public void selectByWrapperOne() {
QueryWrapper<User> wrapper = new QueryWrapper();
wrapper.like("name", "雨").lt("age", 40);
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 名字包含雨
* 年龄大于20小于40
* 邮箱不能为空
* <p>
* WHERE name LIKE '%雨%' AND age BETWEEN 20 AND 40 AND email IS NOT NULL
*/
@Test
public void selectByWrapperTwo() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 名字为王性
* 或者年龄大于等于25
* 按照年龄降序排序,年龄相同按照id升序排序
* <p>
* WHERE name LIKE '王%' OR age >= 25 ORDER BY age DESC , id ASC
*/
@Test
public void selectByWrapperThree() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.likeRight("name", "王").or()
.ge("age", 25).orderByDesc("age").orderByAsc("id");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询创建时间为2019年2月14
* 并且上级领导姓王
* <p>
* WHERE date_format(create_time,'%Y-%m-%d') = '2019-02-14' AND manager_id IN (select id from user where name like '王%')
*/
@Test
public void selectByWrapperFour() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2019-02-14")
.inSql("manager_id", "select id from user where name like '王%'");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询王姓
* 并且年龄小于40或者邮箱不为空
* <p>
* WHERE name LIKE '王%' AND ( age < 40 OR email IS NOT NULL )
*/
@Test
public void selectByWrapperFive() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.likeRight("name", "王").and(qw -> qw.lt("age", 40).or().isNotNull("email"));
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询王姓
* 并且年龄大于20 、年龄小于40、邮箱不能为空
* <p>
* WHERE name LIKE ? OR ( age BETWEEN ? AND ? AND email IS NOT NULL )
*/
@Test
public void selectByWrapperSix() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.likeRight("name", "王").or(
qw -> qw.between("age", 20, 40).isNotNull("email")
);
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* (年龄小于40或者邮箱不为空) 并且名字姓王
* WHERE ( age < 40 OR email IS NOT NULL ) AND name LIKE '王%'
*/
@Test
public void selectByWrapperSeven() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.nested(qw -> qw.lt("age", 40).or().isNotNull("email"))
.likeRight("name", "王");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询年龄为30、31、32
* WHERE age IN (?,?,?)
*/
@Test
public void selectByWrapperEight() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.in("age", Arrays.asList(30, 31, 32));
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询一条数据
* limit 1
*/
@Test
public void selectByWrapperNine() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.in("age", Arrays.asList(30, 31, 32)).last("limit 1");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
三、具体使用操作
注意:以下条件构造器的方法入参中的 column
均表示数据库字段
1、ge、gt、le、lt、isNull、isNotNull
@Test
public void testDelete() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.isNull("name")
.ge("age", 12)
.isNotNull("email");
int result = userMapper.delete(queryWrapper);
System.out.println("delete return count = " + result);
}
2、eq、ne
注意:seletOne返回的是一条实体记录,当出现多条时会报错
@Test
public void testSelectOne() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "Tom");
User user = userMapper.selectOne(queryWrapper);
System.out.println(user);
}
3、between、notBetween
@Test
public void testSelectCount() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.between("age", 20, 30);
Integer count = userMapper.selectCount(queryWrapper);
System.out.println(count);
}
4、allEq
@Test
public void testSelectList() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String, Object> map = new HashMap<>();
map.put("id", 2);
map.put("name", "Jack");
map.put("age", 20);9
queryWrapper.allEq(map);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
5、like、notLike、likeLeft、likeRight
@Test
public void testSelectMaps() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.notLike("name", "e")
.likeRight("email", "t");
List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//返回值是Map列表
maps.forEach(System.out::println);
}