目录
概述
在前面的代码测试中我们都是实用的一些比较简单的方法,比如根据id查询对象或者是根据id删除对象,亦或是将条件封装成map进行查询等。
很多情况下我们需要加入的条件是复杂的且条件也是多变的那么我们该如何实现功能呢?这就需要用到我们的wrapper。
1、wapper介绍
Wrapper
: 条件构造抽象类,最顶端父类
AbstractWrapper
: 用于查询条件封装,生成
sql
的
where
条件
QueryWrapper
: 查询条件封装
UpdateWrapper
:
Update
条件封装
AbstractLambdaWrapper
: 使用
Lambda
语法
LambdaQueryWrapper
:用于
Lambda
语法使用的查询
Wrap
LambdaUpdateWrapper
:
Lambda
更新封装
Wrapper
2、QueryWrapper
a>例1:组装查询条件
@Test
public void test1() {
查询用户名包含a,年龄在20到30之间,并且邮箱不为null的用户信息
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "a")
.between("age", 20, 30)
.isNotNull("email");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
其运行后的sql语句为
SELECT uid AS id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
b>例2:组装排序条件
@Test
public void test2() {
//按年龄降序查询用户,如果年龄相同则按id升序排列
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("age").orderByAsc("uid");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
其sql语句为
SELECT uid AS id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 ORDER BY age DESC,uid ASC
c>例3:组装删除条件
@Test
public void test3() {
//删除email为空的用户
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNull("email");
userMapper.delete(queryWrapper);
}
由于我们之前设置了逻辑删除的字段,所以sql删除是
UPDATE t_user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)
d>例4:条件的优先级
@Test
public void test4() {
///将(年龄大于20并且用户名中包含有a)或邮箱为null的用户信息修改
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("age",20).like("name","a")
.or().isNull("email");
User user = new User();
user.setAge(18);
userMapper.update(user,queryWrapper);
}
sql语句
UPDATE t_user SET age=? WHERE is_deleted=0 AND (age > ? AND name LIKE ? OR email IS NULL)
@Test
public void test5() {
///将用户名中包含有a并且(年龄大于20或邮箱为null)的用户信息修改
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//lambda表达式内的逻辑优先运算
queryWrapper.like("name","a").and(i->i.gt("age",20).or().isNull("email"));
User user = new User();
user.setAge(18);
userMapper.update(user,queryWrapper);
}
sql语句为
UPDATE t_user SET age=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
e>例5:组装select子句
@Test
public void test6() {
///查询用户信息的username和age字段
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("name","age");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
SQL语句
SELECT name,age FROM t_user WHERE is_deleted=0
f>例6:实现子查询
@Test
public void test7() {
///查询 查询id小于等于3的用户信息
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("uid","select uid from t_user where uid <= 3");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
sql语句
SELECT uid AS id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (uid IN (select uid from t_user where uid <= 3))
3、UpdateWrapper
@Test
public void test8() {
//将(年龄大于20或邮箱为null)并且用户名中包含有a的用户信息修改
// 组装set子句以及修改条件
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.set("age",18).set("email","goodman@youxiang.com")
.like("name","a").and(i->i.gt("age",20).or().isNull("email"));
userMapper.update(null,updateWrapper);
}
SQL语句
UPDATE t_user SET age=?,email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
我们还可以这样做
@Test
public void test9() {
//将(年龄大于20或邮箱为null)并且用户名中包含有a的用户信息修改
// 组装set子句以及修改条件
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper
.like("name","a").and(i->i.gt("age",20).or().isNull("email"));
User user = new User();
user.setAge(28);
user.setEmail("morning@11.com");
userMapper.update(user,updateWrapper);
}
SQL
UPDATE t_user SET age=?, email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
4、condition
在真正开发的过程中,组装条件是常见的功能,而这些条件数据来源于用户输入,是可选的,因
此我们在组装这些条件时,必须先判断用户是否选择了这些条件,若选择则需要组装该条件,若
没有选择则一定不能组装,以免影响SQL执行的结果 。
思路一:
@Test
public void test10() {
//定义查询条件,有可能为null(用户未输入或未选择)
String username = null;
Integer ageBegin = 10;
Integer ageEnd = 24;
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace) 构成
//这个是由mp提供的
if (StringUtils.isNotBlank(username)) {
queryWrapper.like("name", username);
}
if (ageBegin != null) {
queryWrapper.gt("age", ageBegin);
}
if (ageEnd != null) {
queryWrapper.lt("age", ageEnd);
}
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
SQL语句为
SELECT uid AS id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (age > ? AND age < ?)
思路二:
上面的实现方案没有问题,但是代码比较复杂,我们可以使用带condition参数的重载方法构建查
询条件,简化代码的编写。
@Test
public void test11() {
//定义查询条件,有可能为null(用户未输入或未选择)
String username = null;
Integer ageBegin = 10;
Integer ageEnd = 24;
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace) 构成
//这个是由mp提供的
queryWrapper.like(StringUtils.isNotBlank(username), "name", username)
.gt(ageBegin != null, "age", ageBegin)
.lt(ageEnd != null, "age", ageEnd);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
SQL语句
SELECT uid AS id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (age > ? AND age < ?)
5、LambdaQueryWrapper
对于上面图片中的描述出现的问题,我们有什么好的办法吗?那就是我们的LambdaQueryWrapper了。
public void test12() {
//定义查询条件,有可能为null(用户未输入或未选择)
String username = null;
Integer ageBegin = 10;
Integer ageEnd = 24;
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
//StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace) 构成
//这个是由mp提供的
queryWrapper.like(StringUtils.isNotBlank(username),User::getName, username)
.gt(ageBegin != null, User::getAge, ageBegin)
.lt(ageEnd != null, User::getAge, ageEnd);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
6、LambdaUpdateWrapper
跟LambdaQueryWrapper作用一样,也是为了避免我们写错字段名
@Test
public void test13() {
LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
updateWrapper.set(User::getAge,20).set(User::getEmail,"nihao")
.gt(User::getAge,55);
userMapper.update(null,updateWrapper);
}
sql语句
UPDATE t_user SET age=?,email=? WHERE is_deleted=0 AND (age > ?)