目录
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))