MyBatis-Plus的条件构造器和常用接口

一、wrapper介绍

在这里插入图片描述

Wrapper ：条件构造抽象类，最顶端父类

AbstractWrapper ：用于查询条件封装，生成 sql 的 where 条件

QueryWrapper ：查询条件封装

UpdateWrapper ： Update 条件封装

AbstractLambdaWrapper ：使用Lambda 语法

LambdaQueryWrapper ：用于Lambda语法使用的查询Wrapper

LambdaUpdateWrapper ： Lambda 更新封装Wrapper

二、QueryWrapper

组装条件查询

/*
* queryWrapper
* 1.组装条件查询
* */
@Test
public void test01(){//查询用户名包含szy，年龄在10到20之间，并且邮箱不为null的用户信息QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.like("name", "szy").between("age", 10, 20).isNotNull("email");//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)List<User> list = userMapper.selectList(queryWrapper);list.forEach(System.out::println);//[User(id=10, name=szy0, age=18, email=user@qcby.com, isDeleted=0), ...]
}

组装排序条件

/*
* queryWrapper
* 2.组装排序条件
* */
@Test
public void test02(){//按年龄降序查询用户，如果年龄相同则按id升序排列QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.orderByDesc("age").orderByAsc("id");//SELECT uid AS id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 ORDER BY age DESC,id ASCList<User> users = userMapper.selectList(queryWrapper);users.forEach(System.out::println);
}

组装删除条件

/*
* queryWrapper
* 3.组装删除条件
* */
@Test
public void test03(){//删除email为空的用户QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.isNull("email");//UPDATE t_user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)int result = userMapper.delete(queryWrapper);System.out.println("受影响的行数：" + result);
}

条件的优先级

/*
* queryWrapper
* 4.条件的优先级1
* */
@Test
public void test04() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();//将（年龄大于20并且用户名中包含有szy）或邮箱为null的用户信息修改queryWrapper.like("name", "szy").gt("age", 20).or().isNull("email");User user = new User();user.setAge(18);user.setEmail("user@qcby.com");//UPDATE t_user SET age=?, email=? WHERE is_deleted=0 AND (name LIKE ? AND age > ? OR email IS NULL)int result = userMapper.update(user, queryWrapper);System.out.println("受影响的行数：" + result);
}/** queryWrapper* 4.条件的优先级2* */
@Test
public void test041() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();//将用户名中包含有szy并且（年龄大于20或邮箱为null）的用户信息修改//lambda表达式内的逻辑优先运算queryWrapper.like("name", "szy").and(i -> i.gt("age", 20).or().isNull("email"));User user = new User();user.setAge(18);user.setEmail("szy@qcby.com");//UPDATE t_user SET age=?, email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))int result = userMapper.update(user, queryWrapper);System.out.println("受影响的行数：" + result);
}

组装select语句

/*
* queryWrapper
* 5.组装select语句
* */
@Test
public void test05() {//查询用户信息的username和age字段QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.select("name", "age");//selectMaps()返回Map集合列表，通常配合select()使用，避免User对象中没有被查询到的列值 为null//SELECT name,age FROM t_user WHERE is_deleted=0List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);System.out.println(maps);//[{name=qcby, age=18}, {name=张三, age=20}, {name=Tom, age=28}, ...]
}

实现子查询

/** queryWrapper* 6.实现子查询* */
@Test
public void test06() {//查询id小于等于3的用户信息QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.inSql("uid", "select uid from t_user where uid <= 3");//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))List<User> list = userMapper.selectList(queryWrapper);System.out.println(list);//[User(id=1, name=qcby, age=18, email=qcby@qq.com, isDeleted=0), User(id=2, ...), User(id=3,...)]
}

左模糊查询

/*
* queryWrapper
* 7.左模糊查询
* */
@Test
public void test071() {//将用户名中包含有a的用户信息修改QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.likeLeft("name", "三");//SELECT uid AS id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (name LIKE ?)---%三(String)List<User> users = userMapper.selectList(queryWrapper);//Total: 7
}

分组groupBy

/*
* queryWrapper
* 8.分组
* */
@Test
public void test081() {//查询出每个年龄段有多少个用户QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.select("age", "COUNT(*)").groupBy("age");//SELECT age,COUNT(*) FROM t_user WHERE is_deleted=0 GROUP BY ageList<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);System.out.println(maps);//[{COUNT(*)=1, age=2}, {COUNT(*)=2, age=18}, {COUNT(*)=6, age=20}, {COUNT(*)=1, age=21}, {COUNT(*)=1, age=23}, {COUNT(*)=1, age=24}, {COUNT(*)=4, age=28}, {COUNT(*)=2, age=30}]
}

分组后筛选having

** queryWrapper* 9.分组后筛选having* */
@Test
public void test082() {//查询出每个年龄段有多少个用户QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.select("age", "COUNT(*)").groupBy("age").having("COUNT(*)>2");//SELECT age,COUNT(*) FROM t_user WHERE is_deleted=0 GROUP BY age HAVING COUNT(*)>2List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);System.out.println(maps);//[{COUNT(*)=6, age=20}, {COUNT(*)=4, age=28}]
}

三、UpdateWrapper

组装set子句以及修改条件

/*
* updateWrapper
* */
@Test
public void test07() {//将（年龄小于20或邮箱为null）并且用户名中包含有a的用户信息修改//组装set子句以及修改条件UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();//lambda表达式内的逻辑优先运算updateWrapper.set("age", 18).set("email", "user@qcby.com").like("name", "张三").and(i -> i.lt("age", 20).or().isNull("email"));//UPDATE t_user SET age=?,email=? WHERE is_deleted=0 AND (name LIKE ? AND (age < ? OR email IS NULL))int result = userMapper.update(null, updateWrapper);System.out.println(result);
}

setSql

/*
* updateWrapper2
* setSql
* */
@Test
public void test083() {List<Integer> ids = Arrays.asList(34,35);// 1.生成SQLUpdateWrapper<User> updateWrapper = new UpdateWrapper<>();updateWrapper.setSql("age = age - 5") // SET age = age - 5.in("uid", ids); // WHERE uid in (34,35)// 2.更新，注意第一个参数可以给null，也就是不填更新字段和数据，// 而是基于UpdateWrapper中的setSQL来更新//UPDATE t_user SET age = age - 5 WHERE is_deleted=0 AND (uid IN (?,?))userMapper.update(null, updateWrapper);
}

四、condition

在真正开发的过程中，组装条件是常见的功能，而这些条件数据来源于用户输入，是可选的，因此我们在组装这些条件时，必须先判断用户是否选择了这些条件，若选择则需要组装该条件，若没有选择则一定不能组装，以免影响SQL执行的结果

思路1

/*
* condition1
* */
@Test
public void test08() {//定义查询条件，有可能为null（用户未输入或未选择）String name = null;Integer ageBegin = 10;Integer ageEnd = 24;QueryWrapper<User> queryWrapper = new QueryWrapper<>();//StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace) 构成if(StringUtils.isNotBlank(name)){queryWrapper.like("name","张三");}if(ageBegin != null){queryWrapper.ge("age", ageBegin);}if(ageEnd != null){queryWrapper.le("age", ageEnd);}//SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE (age >= ? AND age <= ?)List<User> users = userMapper.selectList(queryWrapper);System.out.println(users);//[User(id=1, name=qcby, age=18, email=qcby@qq.com, isDeleted=0),...]
}

思路2

上面的实现方案没有问题，但是代码比较复杂，可以使用带condition参数的重载方法构建查询条件，简化代码的编写

/*
* condition2
* */
@Test
public void test08UseCondition() {//定义查询条件，有可能为null（用户未输入或未选择）String name = null;Integer ageBegin = 10;Integer ageEnd = 24;QueryWrapper<User> queryWrapper = new QueryWrapper<>();//StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace) 构成queryWrapper.like(StringUtils.isNotBlank(name), "name", "张三").ge(ageBegin != null, "age", ageBegin).le(ageEnd != null, "age", ageEnd);//SELECT uid AS id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (age >= ? AND age <= ?)List<User> users = userMapper.selectList(queryWrapper);users.forEach(System.out::println);
}

五、LambdaQueryWrapper

/*
* LambdaQueryWrapper
* */
@Test
public void test09() {//定义查询条件，有可能为null（用户未输入）String name = "张三";Integer ageBegin = 10;Integer ageEnd = 24;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);//SELECT uid AS id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (name LIKE ? AND age >= ? AND age <= ?)List<User> users = userMapper.selectList(queryWrapper);users.forEach(System.out::println);
}

last 方法添加额外的 SQL 语句

/*
* LambdaQueryWrapper
* last 方法添加额外的 SQL 语句
* */
@Test
public void test091() {LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();//避免使用字符串表示字段，防止运行时错误queryWrapper.select(User::getName, User::getAge).last("LIMIT 5");//SELECT name,age FROM t_user WHERE is_deleted=0 LIMIT 5List<User> users = userMapper.selectList(queryWrapper);users.forEach(System.out::println);//User(id=null, name=qcby, age=18, email=null, isDeleted=null)  ...
}

六、LambdaUpdateWrapper

/*
*  LambdaUpdateWrapper
* */
@Test
public void test10() {//组装set子句LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();updateWrapper.set(User::getAge, 28).set(User::getEmail, "user111@qcby.com").like(User::getName, "szy").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 (name LIKE ? AND (age < ? OR email IS NULL))int result = userMapper.update(user, updateWrapper);System.out.println("受影响的行数：" + result);
}