Mybatis-Plus学习04-条件构造器
在Mybatis-Plus中为我们提供了条件构造器来帮助我们解决一些复杂的查询、删除和修改等操作
- Wrapper : 条件构造抽象类,最顶端父类
- AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
- QueryWrapper : Entity 对象封装操作类,不是用lambda语法
- UpdateWrapper : Update 条件封装,用于Entity对象更新操作
- AbstractLambdaWrapper : Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column。
- LambdaQueryWrapper :看名称也能明白就是用于Lambda语法使用的查询Wrapper
- LambdaUpdateWrapper : Lambda 更新封装Wrapper
AbstractWrapper
QueryWrapper(LambdaQueryWrapper) 和 UpdateWrapper(LambdaUpdateWrapper) 的父类
用于生成 sql 的 where 条件, entity 属性也用于生成 sql 的 where 条件
创建user表并初始化
CREATE TABLE user
(
id BIGINT(20) NOT NULL COMMENT '主键ID',
name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
age INT(11) NULL DEFAULT NULL COMMENT '年龄',
email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
create_time datetime COMMENT '创建时间',
update_time datetime COMMENT '更新时间',
PRIMARY KEY (id)
);
DELETE FROM user;
INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');
1、ge、gt、le、lt、isNull、isNotNull
-
ge:大于等于 >=
-
gt:大于 >
-
le:小于等于 <=
-
lt: 小于 <
-
isNull:字段IS Null
-
isNotNull:字段IS NOT NULL
@Test
public void testDelete(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNotNull("name") //名字不为空
.gt("age",18) //年龄大于18
.isNull("email"); //email为空
int result = userMapper.delete(queryWrapper);
System.out.println("删除了"+result+"行");
}
控制台中执行的SQL语句:DELETE FROM user WHERE name IS NOT NULL AND age > ? AND email IS NULL
2、eq、ne
- eq:等于 =
- ne:不等于 <>
@Test
public void testSelectOne() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "Jack") //名字为jack
.ne("age",18); //年龄不等于18岁
User user = userMapper.selectOne(queryWrapper);
System.out.println(user);
}
控制台中执行的SQL语句:SELECT id,name,age,email,create_time,update_time FROM user WHERE name = ? AND age <> ?
seletOne返回的是一条实体记录,当出现多条时会报错
3、between、notBetween
- between: between A and B
- notBetween:not between A and B
@Test
public void testSelectCount(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.between("age",18,22);//查询年龄在18和22之间的用户
Integer result = userMapper.selectCount(queryWrapper);
System.out.println("年龄在18岁与22岁之间共有:"+ result+"人");
}
控制台中执行的SQL语句:SELECT COUNT(1) FROM user WHERE age BETWEEN ? AND ?
4、allEq
- allEq:基于map封装的eq条件
@Test
public void testSelectList(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String,Object> map = new HashMap<>();
map.put("age",20); //年龄为20
map.put("name","jack");//姓名为jack
queryWrapper.allEq(map);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
控制台中执行的SQL语句:SELECT id,name,age,email,create_time,update_time FROM user WHERE name = ? AND age = ?
5、like、notLike、likeLeft、likeRight
- like:LIKE ‘%值%’
- notLike:NOT LIKE ‘%值%’
- likeLeft:LIKE ‘%值’
- likeRight:LIKE ‘值%’
@Test
public void testSelectMaps(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name","j") //like j% 名字为j开头的
.notLike("email","w"); // not like %w% 邮箱中间没有w字母
List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
maps.forEach(System.out::println);
}
控制台中执行的SQL语句:SELECT id,name,age,email,create_time,update_time FROM user WHERE name LIKE ? AND email NOT LIKE ?
6、in、notIn、inSql、notinSql、exists、notExists
- in: 字段 IN (v0, v1, …)
- notIn: 字段 NOT IN (v0, v1, …)
- isSql: 字段 IN ( sql语句 )
- notinSql: 字段 NOT IN ( sql语句 )
- exists: 拼接 EXISTS ( sql语句 )
- notExists:拼接 NOT EXISTS ( sql语句 )
@Test
public void testSelectObjs() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//年龄在select age from user where age < 20这条sql语句结果集中
queryWrapper.inSql("age", "select age from user where age < 20")
.in("id", Arrays.asList(1,2,3));//id在(1,2,3)其中
List<Object> objects = userMapper.selectObjs(queryWrapper);//返回值是Object列表
objects.forEach(System.out::println);
}
控制台中执行的SQL语句:SELECT id,name,age,email,create_time,update_time FROM user WHERE age IN (select age from user where age < 20) AND id IN (?,?,?)
7、or、and
- **or:**拼接 OR 若不适用or默认为and连接
- and:AND 嵌套
@Test
public void testUpdateWrapper() {
// 修改值
User user = new User();
user.setAge(18);
user.setName("Tom");
// 修改条件
UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
userUpdateWrapper.like("name", "j") //修改名字中有J的
.or()
.between("age", 20, 30);//或者年龄在20到30之间的user
int result = userMapper.update(user, userUpdateWrapper);
System.out.println(result);
}
/***************or and的嵌套***********************************************************/
@Test
public void testUpdate2() {
//修改值
User user = new User();
user.setAge(18);
user.setName("Tom");
//修改条件
UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
userUpdateWrapper
.like("name", "h")
.or(i -> i.eq("name","jack").ne("age", 18));//嵌套条件名字为jack 年龄不为18
int result = userMapper.update(user, userUpdateWrapper);
System.out.println(result);
}
}
控制台中执行的SQL语句:UPDATE user SET name=?, age=?, update_time=? WHERE name LIKE ? OR age BETWEEN ? AND ?
控制台中执行的SQL语句:UPDATE user SET name=?, age=?, update_time=? WHERE name LIKE ? OR (name = ?AND age <> ? )
8、orderBy、orderByDesc、orderByAsc
- orderBy: 排序:ORDER BY 字段, …
- orderByDesc: 排序:ORDER BY 字段, … Asc
- orderByAsc: 排序:ORDER BY 字段, … Desc
@Test
public void testSelectListOrderBy() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("id");//根据id若大到小排序
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
控制台中执行的SQL语句:SELECT id,name,age,email,create_time,update_time FROM user ORDER BY id DESC
9、last
- last:无视优化规则直接拼接到 sql 的最后
@Test
public void testSelectListLast() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.last("limit 1");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
控制台中执行的SQL语句:SELECT id,name,age,email,create_time,update_time FROM user limit 1
10、指定要查询的列
@Test
public void testSelectListColumn() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name", "age");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
控制台中执行的SQL语句:SELECT id,name,age FROM user
11、set、setSql
- set: SET 字段sql
- setSql:设置 SET 部分 SQL
@Test
public void testWrapperSet() {
// 修改值
User user = new User();
user.setAge(18);
// 修改条件
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.likeRight("name", "j")
.set("name", "Tom")//满足条件的将name都设置为Tom
.setSql("email = 'Tom@123.com'"); //直接将email设置为Tom@123.com
int result = userMapper.update(user, updateWrapper);
}
控制台中执行的SQL语句:UPDATE user SET age=?, update_time=?, name=?,email = 'Tom@123.com' WHERE name LIKE ?