Mybatis-Plus05-条件构造器和常用接口
1、wapper介绍
- Wrapper:条件构造器抽象类,最顶端父类
- AbstractWrapper:用于查询条件封装,生成sql的where条件
- QueryWrapper:查询条件封装
- UpdateWrapper:Update条件封装
- AbstractLambdaWrapper:使用Lambda语法
- LambdaQueryWrapper:用于Lambda语法使用的查询Wrapper
- LambdaUpdateWrapper:Lambda更新封装Wrapper
- AbstractWrapper:用于查询条件封装,生成sql的where条件
2、QueryWrapper
a> 例1:组装查询条件
@Test
public void test01() {
// 查询用户名包含b,年龄在20到30岁之间,并且邮箱信息不为null的用户信息
// SELECT uid AS id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (username LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("username", "b")
.between("age", 20, 30)
.isNotNull("email");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out :: println);
}
b> 例2:组装排序条件
@Test
public void test02() {
// 按年龄降序查询用户,如果年龄相同则按id升序排序
// SELECT uid AS id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 ORDER BY age DESC,id ASC
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("age")
.orderByAsc("id");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out :: println);
}
;
c> 例3:组装删除条件
@Test
public void test03() {
// 删除email为空的用户
// UPDATE t_user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNull("email");
// 条件构造器也可以构建删除语句的条件
int result = userMapper.delete(queryWrapper);
System.out.println("受影响的行数:" + result);
}
d> 例4:条件的优先级
@Test
public void test04() {
// 将(年龄大于20并且用户名中包含有a)或者邮箱为null的用户信息修改
// UPDATE t_user SET age=?, email=? WHERE is_deleted=0 AND (username LIKE ? AND age > ? OR email IS NULL)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("username", "a")
.gt("age", 20)
.or()
.isNull("email");
User user = new User();
user.setAge(18);
user.setEmail("user@email.com");
int result = userMapper.update(user, queryWrapper);
System.out.println("受影响的行数:" + result);
}
@Test
public void test04() {
// 将 用户名中包含有a并且(年龄大于20或邮箱为null)的用户信息修改
// UPDATE t_user SET age=?, email=? WHERE is_deleted=0 AND (username LIKE ? AND (age > ? OR email IS NULL))
// lambda表达式内的逻辑优先运算
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("username", "a")
.and(i -> i.gt("age", 20).or().isNull("email"));
User user = new User();
user.setAge(18);
user.setEmail("user@email.com");
int result = userMapper.update(user, queryWrapper);
System.out.println("受影响的行数:" + result);
}
e> 例5:组装select子句
@Test
public void test05() {
// 查询用户的信息的username和age字段
// SELECT username,age FROM t_user WHERE is_deleted=0
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("username", "age");
// selectMaps() 返回Map集合列表,通常配合select()使用,避免User对象中没有被查询到的列值为null
List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
maps.forEach(System.out :: println);
}
f> 例6:实现子查询
@Test
public void test06() {
// 查询小于等于3的用户信息
// SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (id IN (select id from t_user where id <= 3))
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("id", "select id from t_user where id <= 3");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
3、UpdateWrapper
@Test
public void test07() {
// 将(年龄大于20或邮箱为null)并且用户名中包含有a的用户信息修改
// 组装set子句以及修改条件
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
// lambda表达式内的逻辑优先运算
updateWrapper
.set("age", 18)
.set("email", "user@email.com")
.like("username", "a")
.and(i -> i.gt("age", 20).or().isNull("email"));
// 这里必须要创建user对象,否则无法应用自动填充,如果没有自动填充,可以设置为null
// UPDATE t_user SET username=?, age=?,email=? WHERE is_deleted=0 AND (username LIKE ? AND (age > ? OR email IS NULL))
//User user = new User();
//user.setName("张三");
//int result = userMapper.update(user, updateWrapper);
// UPDATE t_user SET age=?,email=? WHERE is_deleted=0 AND (username LIKE ? AND (age > ? OR email IS NULL))
int result = userMapper.update(null, updateWrapper);
System.out.println(result);
}
4、condition
在真正开发的过程中,组装条件是常用的功能,而这些条件数据来源于用户输入,是可选的,因此我们在组装这些条件时,必须先判断用户是否选择了这些条件,若选择则需要组装该条件,若没有选择则一定不能组装,以免影响SQL执行的结果
思路一:
@Test
public void test08() {
// 定义查询条件,有可能成为null(用户未输入或未选择)
String username = null;
Integer ageBegin = 10;
Integer ageEnd = 24;
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// StringUtils.isNotBlank() 判断某字符串是否不为空且长度不为0且不为空白符(whitespace)构成
if (StringUtils.isNotBlank(username)) {
queryWrapper.like("username", username);
}
if (ageBegin != null) {
queryWrapper.ge("age", ageBegin);
}
if (ageEnd != null) {
queryWrapper.lt("age", ageEnd);
}
// SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (age >= ? AND age < ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
思路二:
上面的实现方案没有问题,但是代码比较复杂,我们可以使用带condition参数的重载方法构建查询条件,简化代码的编写
@Test
public void test09() {
// 定义查询条件,有可能成为null(用户未输入或未选择)
String username = null;
Integer ageBegin = 10;
Integer ageEnd = 24;
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// StringUtils.isNotBlank() 判断某字符串是否不为空且长度不为0且不为空白符(whitespace)构成
queryWrapper
.like(StringUtils.isNotBlank(username), "username", username)
.gt(ageBegin != null, "age", ageBegin)
.lt(ageEnd != null, "age", ageEnd);
// SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (age > ? AND age < ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
5、LambdaQueryWrapper
@Test
public void test09() {
// 定义查询条件,有可能成为null(用户未输入)
String username = "a";
Integer ageBegin = 10;
Integer ageEnd = 24;
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
// 避免使用字符串表示字段,防止运行时错误
queryWrapper
.like(StringUtils.isNotBlank(username), User::getName, username)
.gt(ageBegin != null, User::getAge, ageBegin)
.lt(ageEnd != null, User::getAge, ageEnd);
// SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (username LIKE ? AND age > ? AND age < ?)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
6、LambdaUpdateWrapper
@Test
public void test10() {
// 组装子句
LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
updateWrapper
.set(User::getAge, 18)
.set(User::getEmail, "user@atguigu.com")
.like(User::getName, "a")
.and(i -> i.lt(User::getAge, 24).or().isNull(User::getEmail)); // lambda表达式内的逻辑优先运算
User user = new User();
// UPDATE t_user SET age=?,email=? WHERE is_deleted=0 AND (username LIKE ? AND (age < ? OR email IS NULL))
int result = userMapper.update(user, updateWrapper);
System.out.println("受影响的行数:" + result);
}