1:wapper介绍
- Wrapper : 条件构造抽象类,最顶端父类
- AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
- QueryWrapper : 查询条件封装
- UpdateWrapper : Update 条件封装
- AbstractLambdaWrapper : 使用Lambda 语法
- LambdaQueryWrapper :用于Lambda语法使用的查询Wrapper
- LambdaUpdateWrapper : Lambda 更新封装Wrapper
2:Querywrapper
1:查询条件
//wrapper的泛型为实体类
QueryWrapper<User> queryWrapper=new QueryWrapper<>();
//数据库字段名 column
queryWrapper.like("name", "%a%")
.between("age", 20,30)
.isNotNull("email");
生成的SQL语句: SELECT id,name,age,email,is_deleted AS Deleted FROM t_users WHERE is_deleted=0 AND (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
使用此Wrapper: List<User> users = userMapper.selectList(queryWrapper);
2:排序条件
QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.orderByDesc("age")
.orderByAsc("id");
生成的SQL语句:SELECT id,name,age,email,is_deleted AS Deleted FROM t_users WHERE is_deleted=0 ORDER BY age DESC,id ASC
使用此Wrapper: List<User> users = userMapper.selectList(queryWrapper);
3:删除条件
QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.between("age", 10, 25)
.like("name","%a%");
生成的SQL:UPDATE t_users SET is_deleted=1 WHERE is_deleted=0 AND (age BETWEEN ? AND ? AND name LIKE ?)
使用此Wrapper: userMapper.delete(queryWrapper);
4:修改条件
QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.like("name", "%a")
.gt("age", 16)//年纪大于16
.or()//或
.isNotNull("email");
生成的SQL:UPDATE t_users SET name=? WHERE is_deleted=0 AND (name LIKE ? AND age > ? OR email IS NOT NULL)
使用此Wrapper:
//设置修改的字段,修改为小明
User user=new User(null,"小明",null,null);
//传入两个参数
userMapper.update(user,queryWrapper);`
5:优先级查询
//i 代表前面的queryWrapper的lambda里面的条件的优先级高
QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.like("name", "a")
.and(i->i.gt("age", 20).or().isNull("email"));
生成的SQL:UPDATE t_users SET is_deleted=1 WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
使用此Wrapper: int delete = userMapper.delete(queryWrapper);
6:指定字段查询
QueryWrapper<User> queryWrapper=new QueryWrapper<>();
//字段列表
queryWrapper.select("name","age");
生成的SQL:SELECT name,age FROM t_users WHERE is_deleted=0
7:子查询
QueryWrapper<User> queryWrapper=new QueryWrapper<>();
//子查询字段 子查询sql语句
queryWrapper.inSql("id", "select id from t_users where id <=100");
生成的SQL:SELECT id,name,age,email,is_deleted AS Deleted FROM t_users WHERE is_deleted=0 AND (id IN (select id from t_users where id <=100))
使用此Warpper: List<User> users = userMapper.selectList(queryWrapper);
3:UpdateWrapper
UpdateWrapper<User> updateWrapper=new UpdateWrapper<>();
//用户名包含a并(年纪大于20或邮箱为空)的用户信息修改
updateWrapper.like("name", "a")
.and(i->i.gt("age", 20).or().isNull("email"));
//设置修改的字段
updateWrapper.set("name", "小黑").set("email", "1111");
userMapper.update(null, updateWrapper);
生成的SQL:UPDATE t_users SET name=?,email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
4:组装条件
4.1:原生组装
//组装的条件
String name="a";
Integer ageBegin=null;
Integer ageEnd=30;
QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.select("name");
if(StringUtils.isNotBlank(name)){
//isNotBlank:判断字符串是否不为空,不为null,不为空白符
queryWrapper.like("name", name);
}
if (ageBegin!=null){
queryWrapper.ge("age", ageBegin);
}
if(ageEnd!=null){
queryWrapper.le("age", ageEnd);
}
}
生成的SQL:SELECT name FROM t_users WHERE is_deleted=0 AND (name LIKE ? AND age <= ?)
4.2:使用condition组装条件
String name="a";
Integer ageBegin=null;
Integer ageEnd=30;
QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.select("name");
//是否存在,字段,值
queryWrapper.like(StringUtils.isNotBlank(name), "name", name);
queryWrapper.ge(ageBegin!=null,"age", ageBegin);
queryWrapper.le(ageEnd!=null,"age",ageEnd);
}
生成的SQL: SELECT name FROM t_users WHERE is_deleted=0 AND (name LIKE ? AND age <= ?)
5:LambdaQuery/Update
组装条件:
String name="a";
Integer ageBegin=null;
Integer ageEnd=30;
LambdaQueryWrapper<User> queryWrapper=new LambdaQueryWrapper<>();
//查询字段为name字段 对应实体类为User,调用getName方法确认查询的字段
queryWrapper.select(User::getName);
//字段为name
queryWrapper.like(StringUtils.isNotBlank(name),User::getName,name);
//字段为age
queryWrapper.ge(ageBegin!=null,User::getAge,ageBegin);
//字段为age
queryWrapper.le(ageEnd!=null,User::getAge,ageEnd);
List<User> users = userMapper.selectList(queryWrapper);
}
生成的SQL: SELECT name FROM t_users WHERE is_deleted=0 AND (name LIKE ? AND age <= ?)
优先级:
LambdaUpdateWrapper<User> updateWrapper=new LambdaUpdateWrapper<>();
//模糊查询字段为name 且优先级高, 后面不为空的为email age<=21
updateWrapper.like(User::getName,"a")
.and(i->i.isNull(User::getEmail).le(User::getAge, 21));
//更新的字段为name 更新为小黑 年纪更新为100
updateWrapper.set(User::getName,"小黑").set(User::getAge,100);
userMapper.update(null,updateWrapper);
}
生成的SQL: UPDATE t_users SET name=?,age=? WHERE is_deleted=0 AND (name LIKE ? AND (email IS NULL AND age <= ?))