目录
1.wrapper介绍
2.QueryWrapper
①组装查询条件
查询用户名包含a，年龄在20到30之间，邮箱信息不为null的用户信息
测试类
@Test
public void test01() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "a")
.between("age", 20, 30)
.isNotNull("email");
List<User> list = userMapper.selectList(queryWrapper);
list.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)②组装排序条件
查询用户信息，按照年龄的降序排序，若年龄相同，则按照id升序排序
@Test
public void test02() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("age")
.orderByAsc("uid");
List<User> list = userMapper.selectList(queryWrapper);
list.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③组装删除条件
删除邮箱地址为null的用户信息
@Test
public void test03() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNull("email");
int result = userMapper.delete(queryWrapper);
System.out.println("result:" + result);
}执行的sql
UPDATE t_user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)④使用QueryWrapper实现修改功能
将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改
@Test
public void test04() {
//将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("age", 20)
.like("name", "a")
.or()
.isNull("email");
User user = new User();
user.setName("小明");
user.setEmail("test@tian.com");
int result = userMapper.update(user, queryWrapper);
System.out.println("result:" + result);
}执行的sql
UPDATE t_user SET name=?, email=? WHERE is_deleted=0 AND (age > ? AND name LIKE ? OR email IS NULL)⑤条件的优先级
将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
lambda中的条件优先执行
@Test
public void test05() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name","a")
.and(i->i.gt("age",20).or().isNull("email"));
User user = new User();
user.setName("小红");
user.setEmail("test@ttt.com");
int result = userMapper.update(user, queryWrapper);
System.out.println("result:" + result);
}执行的sql
UPDATE t_user SET name=?, email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))⑥组装select子句
查询用户的用户名，年龄，邮箱信息
@Test
public void test06() {
QueryWrapper<User> queryWrapper = new QueryWrapper();
queryWrapper.select("name","age","email");
List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
maps.forEach(System.out::println);
}执行的sql
SELECT name,age,email FROM t_user WHERE is_deleted=0⑦实现子查询
查询id小于等于100的用户信息
@Test
public void test07() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("uid","select uid from t_user where uid <= 100");
List<User> list = userMapper.selectList(queryWrapper);
list.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 <= 100))3.UpdateWrapper
将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
@Test
public void test08() {
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.like("name","a")
.and(i -> i.gt("age",20).or().isNull("email"));
updateWrapper.set("name","小黑").set("email","abc@xiaobai.com");
int result = userMapper.update(null, updateWrapper);
System.out.println("result：" + result);
}执行的sql
UPDATE t_user SET name=?,email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))4.condition
在真正开发的过程中，组装条件是常见的功能，而这些条件数据来源于用户输入，是可选的，因 此我们在组装这些条件时，必须先判断用户是否选择了这些条件，若选择则需要组装该条件，若 没有选择则一定不能组装，以免影响SQL执行的结果
思路一：
@Test
public void test09() {
String name = "";
Integer ageBegin = 20;
Integer ageEnd = 30;
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
if (StringUtils.isNotBlank(name)) {
//isNotBlank判断某个字符串是否不为空字符串，不为null，不为空白符
queryWrapper.like("name",name);
}
if (ageBegin != null) {
queryWrapper.ge("age",ageBegin);
}
if (ageEnd != null) {
queryWrapper.le("age",ageEnd);
}
List<User> list = userMapper.selectList(queryWrapper);
list.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 test10() {
String name = "a";
Integer ageBegin = null;
Integer ageEnd = 30;
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like(StringUtils.isNotBlank(name),"name",name)
.ge(ageBegin != null,"age",ageBegin)
.le(ageEnd != null,"age",ageEnd);
List<User> list = userMapper.selectList(queryWrapper);
list.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 <= ?)5.LambdaQueryWrapper
@Test
public void test11() {
String name = "a";
Integer ageBegin = null;
Integer ageEnd = 30;
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.like(StringUtils.isNotBlank(name),User::getName,name)
.ge(ageBegin != null,User::getAge,ageBegin)
.le(ageEnd != null,User::getAge,ageEnd);
List<User> list = userMapper.selectList(queryWrapper);
list.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 <= ?)6.LambdaUpdateWrapper
@Test
public void test12() {
//将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
updateWrapper.like(User::getName,"a")
.and(i -> i.gt(User::getAge,20).or().isNull(User::getEmail));
updateWrapper.set(User::getName,"小黑").set(User::getEmail,"abc@xiaobai.com");
int result = userMapper.update(null, updateWrapper);
System.out.println("result：" + result);
}执行的sql
UPDATE t_user SET name=?,email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
2420
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
