转载自：https://blog.csdn.net/alan_liuyue/article/details/121162237
用到了wrapper，整理资料记录一下，以备后续复习。
目录------------（可点击相应目录直接跳转）
目录
2、MyBatis-Plus还提供了Wrapper条件构造器，具体使用看如下代码：
1、ge、gt、le、lt、isNull、isNotNull
5、like、notLike、likeLeft、likeRight
6、in、notIn、inSql、notinSql、exists、notExists
9、orderBy、orderByDesc、orderByAsc
一、条件构造器关系介绍
条件构造器关系介绍 ：
- 上图绿色框为抽象类abstract
- 蓝色框为正常class类，可new对象
- 黄色箭头指向为父子类关系，箭头指向为父类
wapper介绍 ：
- Wrapper ： 条件构造抽象类，最顶端父类
- AbstractWrapper ： 用于查询条件封装，生成 sql 的 where 条件
- QueryWrapper ： Entity 对象封装操作类，不是用lambda语法
- UpdateWrapper ： Update 条件封装，用于Entity对象更新操作
- AbstractLambdaWrapper ： Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column。
- LambdaQueryWrapper ：看名称也能明白就是用于Lambda语法使用的查询Wrapper
- LambdaUpdateWrapper ： Lambda 更新封装Wrapper
二、项目实例
1、根据主键或者简单的查询条件进行查询
-
/**
-
* 通过单个ID主键进行查询
-
*/
-
@Test
-
public
void
selectById(
) {
-
User user = userMapper.
selectById(1094592041087729666L);
-
System.
out.
println(user);
-
}
-
-
/**
-
* 通过多个ID主键查询
-
*/
-
@Test
-
public
void
selectByList(
) {
-
List<
Long> longs =
Arrays.
asList(1094592041087729666L, 1094590409767661570L);
-
List<
User> users = userMapper.
selectBatchIds(longs);
-
users.
forEach(
System.
out::println);
-
}
-
-
/**
-
* 通过Map参数进行查询
-
*/
-
@Test
-
public
void
selectByMap(
) {
-
Map<
String,
Object> params =
new
HashMap<>();
-
params.
put(
"name",
"张雨琪");
-
List<
User> users = userMapper.
selectByMap(params);
-
users.
forEach(
System.
out::println);
-
}
2、MyBatis-Plus还提供了Wrapper条件构造器，具体使用看如下代码：
-
/**
-
* 名字包含雨并且年龄小于40
-
* <p>
-
* WHERE name LIKE '%雨%' AND age < 40
-
*/
-
@Test
-
public void selectByWrapperOne() {
-
QueryWrapper<
User> wrapper
= new
QueryWrapper();
-
wrapper.like(
"name",
"雨").lt(
"age",
40);
-
List<
User> users
= userMapper.selectList(wrapper);
-
users.forEach(
System.out::println);
-
}
-
-
/**
-
* 名字包含雨
-
* 年龄大于20小于40
-
* 邮箱不能为空
-
* <p>
-
* WHERE name LIKE '%雨%' AND age BETWEEN 20 AND 40 AND email IS NOT NULL
-
*/
-
@Test
-
public void selectByWrapperTwo() {
-
QueryWrapper<
User> wrapper
=
Wrappers.query();
-
wrapper.like(
"name",
"雨").between(
"age",
20,
40).isNotNull(
"email");
-
List<
User> users
= userMapper.selectList(wrapper);
-
users.forEach(
System.out::println);
-
}
-
-
/**
-
* 名字为王性
-
* 或者年龄大于等于25
-
* 按照年龄降序排序，年龄相同按照id升序排序
-
* <p>
-
* WHERE name LIKE '王%' OR age >= 25 ORDER BY age DESC , id ASC
-
*/
-
@Test
-
public void selectByWrapperThree() {
-
QueryWrapper<
User> wrapper
=
Wrappers.query();
-
wrapper.likeRight(
"name",
"王").or()
-
.ge(
"age",
25).orderByDesc(
"age").orderByAsc(
"id");
-
List<
User> users
= userMapper.selectList(wrapper);
-
users.forEach(
System.out::println);
-
}
-
-
/**
-
* 查询创建时间为2019年2月14
-
* 并且上级领导姓王
-
* <p>
-
* WHERE date_format(create_time,'%Y-%m-%d') = '2019-02-14' AND manager_id IN (select id from user where name like '王%')
-
*/
-
@Test
-
public void selectByWrapperFour() {
-
QueryWrapper<
User> wrapper
=
Wrappers.query();
-
wrapper.apply(
"date_format(create_time,'%Y-%m-%d') = {0}",
"2019-02-14")
-
.inSql(
"manager_id",
"select id from user where name like '王%'");
-
List<
User> users
= userMapper.selectList(wrapper);
-
users.forEach(
System.out::println);
-
}
-
-
/**
-
* 查询王姓
-
* 并且年龄小于40或者邮箱不为空
-
* <p>
-
* WHERE name LIKE '王%' AND ( age < 40 OR email IS NOT NULL )
-
*/
-
@Test
-
public void selectByWrapperFive() {
-
QueryWrapper<
User> wrapper
=
Wrappers.query();
-
wrapper.likeRight(
"name",
"王").and(qw -> qw.lt(
"age",
40).or().isNotNull(
"email"));
-
List<
User> users
= userMapper.selectList(wrapper);
-
users.forEach(
System.out::println);
-
}
-
-
/**
-
* 查询王姓
-
* 并且年龄大于20 、年龄小于40、邮箱不能为空
-
* <p>
-
* WHERE name LIKE ? OR ( age BETWEEN ? AND ? AND email IS NOT NULL )
-
*/
-
@Test
-
public void selectByWrapperSix() {
-
QueryWrapper<
User> wrapper
=
Wrappers.query();
-
wrapper.likeRight(
"name",
"王").or(
-
qw -> qw.between(
"age",
20,
40).isNotNull(
"email")
-
);
-
List<
User> users
= userMapper.selectList(wrapper);
-
users.forEach(
System.out::println);
-
}
-
-
/**
-
* (年龄小于40或者邮箱不为空) 并且名字姓王
-
* WHERE ( age < 40 OR email IS NOT NULL ) AND name LIKE '王%'
-
*/
-
@Test
-
public void selectByWrapperSeven() {
-
QueryWrapper<
User> wrapper
=
Wrappers.query();
-
wrapper.nested(qw -> qw.lt(
"age",
40).or().isNotNull(
"email"))
-
.likeRight(
"name",
"王");
-
List<
User> users
= userMapper.selectList(wrapper);
-
users.forEach(
System.out::println);
-
}
-
-
/**
-
* 查询年龄为30、31、32
-
* WHERE age IN (?,?,?)
-
*/
-
@Test
-
public void selectByWrapperEight() {
-
QueryWrapper<
User> wrapper
=
Wrappers.query();
-
wrapper.in(
"age",
Arrays.asList(
30,
31,
32));
-
List<
User> users
= userMapper.selectList(wrapper);
-
users.forEach(
System.out::println);
-
}
-
-
/**
-
* 查询一条数据
-
* limit 1
-
*/
-
@Test
-
public void selectByWrapperNine() {
-
QueryWrapper<
User> wrapper
=
Wrappers.query();
-
wrapper.in(
"age",
Arrays.asList(
30,
31,
32)).last(
"limit 1");
-
List<
User> users
= userMapper.selectList(wrapper);
-
users.forEach(
System.out::println);
-
}
三、具体使用操作
注意：以下条件构造器的方法入参中的 column 均表示数据库字段
1、ge、gt、le、lt、isNull、isNotNull
-
@Test
-
public void testDelete() {
-
-
QueryWrapper<User> queryWrapper =
new QueryWrapper<>();
-
queryWrapper
-
.isNull(
"name")
-
.ge(
"age",
12)
-
.isNotNull(
"email");
-
int result = userMapper.delete(queryWrapper);
-
System.
out.println(
"delete return count = " + result);
-
}
SQL：UPDATE user SET deleted=1 WHERE deleted=0 AND name IS NULL AND age >= ? AND email IS NOT NULL
2、eq、ne
-
注意：seletOne返回的是一条实体记录，当出现多条时会报错
-
-
@Test
-
public
void
testSelectOne(
) {
-
QueryWrapper<
User> queryWrapper =
new
QueryWrapper<>();
-
queryWrapper.
eq(
"name",
"Tom");
-
-
User user = userMapper.
selectOne(queryWrapper);
-
System.
out.
println(user);
-
}
3、between、notBetween
-
包含大小边界
-
-
@Test
-
public
void
testSelectCount(
) {
-
-
QueryWrapper<
User> queryWrapper =
new
QueryWrapper<>();
-
queryWrapper.
between(
"age",
20,
30);
-
-
Integer count = userMapper.
selectCount(queryWrapper);
-
System.
out.
println(count);
-
}
SELECT COUNT(1) FROM user WHERE deleted=0 AND age BETWEEN ? AND ?
4、allEq
-
@Test
-
public
void
testSelectList(
) {
-
-
QueryWrapper<
User> queryWrapper =
new
QueryWrapper<>();
-
Map<
String,
Object> map =
new
HashMap<>();
-
map.
put(
"id",
2);
-
map.
put(
"name",
"Jack");
-
map.
put(
"age",
20);
9
-
-
queryWrapper.
allEq(map);
-
List<
User> users = userMapper.
selectList(queryWrapper);
-
-
users.
forEach(
System.
out::println);
-
}
SELECT id,name,age,email,create_time,update_time,deleted,version FROM user WHERE deleted=0 AND name = ? AND id = ? AND age = ?
5、like、notLike、likeLeft、likeRight
-
selectMaps返回
Map集合列表
-
-
@Test
-
public
void
testSelectMaps(
) {
-
-
QueryWrapper<
User> queryWrapper =
new
QueryWrapper<>();
-
queryWrapper
-
.
notLike(
"name",
"e")
-
.
likeRight(
"email",
"t");
-
-
List<
Map<
String,
Object>> maps = userMapper.
selectMaps(queryWrapper);
//返回值是Map列表
-
maps.
forEach(
System.
out::println);
-
}
SELECT id,name,age,email,create_time,update_time,deleted,version FROM user WHERE deleted=0 AND name NOT LIKE ? AND email LIKE ?
6、in、notIn、inSql、notinSql、exists、notExists
-
in、notIn：
-
-
notIn(
"age",{
1,
2,
3})--->age not
in
(1,2,3)
-
notIn(
"age",
1,
2,
3)--->age not
in
(1,2,3)
-
inSql、notinSql：可以实现子查询
-
-
例: inSql(
"age",
"1,2,3,4,5,6")--->age
in
(1,2,3,4,5,6)
-
例: inSql(
"id",
"select id from table where id < 3")--->id
in
(select id from table where id < 3)
-
@Test
-
public
void
testSelectObjs
() {
-
-
QueryWrapper<User> queryWrapper =
new
QueryWrapper<>();
-
//queryWrapper.in("id", 1, 2, 3);
-
queryWrapper.inSql(
"id",
"select id from user where id < 3");
-
-
List<Object> objects = userMapper.selectObjs(queryWrapper);
//返回值是Object列表
-
objects.forEach(System.out::println);
-
}
SELECT id,name,age,email,create_time,update_time,deleted,version FROM user WHERE deleted=0 AND id IN (select id from user where id < 3)
7、or、and
-
注意：这里使用的是 UpdateWrapper 不调用
or则默认为使用
and 连
-
-
@Test
-
public void testUpdate1() {
-
-
//修改值
-
User user =
new User();
-
user.setAge(
99);
-
user.setName(
"Andy");
-
-
//修改条件
-
UpdateWrapper<User> userUpdateWrapper =
new UpdateWrapper<>();
-
userUpdateWrapper
-
.like(
"name",
"h")
-
.
or()
-
.between(
"age",
20,
30);
-
-
int result = userMapper.update(user, userUpdateWrapper);
-
System.
out.println(result);
-
}
UPDATE user SET name=?, age=?, update_time=? WHERE deleted=0 AND name LIKE ? OR age BETWEEN ? AND ?
8、嵌套or、嵌套and
-
这里使用了lambda表达式，
or中的表达式最后翻译成sql时会被加上圆括号
-
-
@Test
-
public void testUpdate2() {
-
-
//修改值
-
User user =
new User();
-
user.setAge(
99);
-
user.setName(
"Andy");
-
-
//修改条件
-
UpdateWrapper<User> userUpdateWrapper =
new UpdateWrapper<>();
-
userUpdateWrapper
-
.like(
"name",
"h")
-
.
or(i -> i.eq(
"name",
"李白").ne(
"age",
20));
-
-
int result = userMapper.update(user, userUpdateWrapper);
-
System.
out.println(result);
-
}
UPDATE user SET name=?, age=?, update_time=?
WHERE deleted=0 AND name LIKE ?
OR ( name = ? AND age <> ? )
9、orderBy、orderByDesc、orderByAsc
-
@Test
-
public
void
testSelectListOrderBy(
) {
-
-
QueryWrapper<
User> queryWrapper =
new
QueryWrapper<>();
-
queryWrapper.
orderByDesc(
"id");
-
-
List<
User> users = userMapper.
selectList(queryWrapper);
-
users.
forEach(
System.
out::println);
-
}
SELECT id,name,age,email,create_time,update_time,deleted,version
FROM user WHERE deleted=0 ORDER BY id DESC
10、last
-
直接拼接到 sql 的最后
-
-
注意：只能调用一次,多次调用以最后一次为准 有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);
-
}
-
SELECT id,name,age,email,create_time,update_time,deleted,version
FROM user WHERE deleted=0 limit 1
11、指定要查询的列
-
@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);
-
}
SELECT id,name,age FROM user WHERE deleted=0
12、set、setSql
-
最终的sql会合并 user.setAge()，以及 userUpdateWrapper.
set() 和 setSql() 中 的字段
-
-
@Test
-
public void testUpdateSet() {
-
//修改值
-
User user =
new User();
-
user.setAge(
99);
-
-
//修改条件
-
UpdateWrapper<User> userUpdateWrapper =
new UpdateWrapper<>();
-
userUpdateWrapper
-
.like(
"name",
"h")
-
.
set(
"name",
"老李头")
//除了可以查询还可以使用set设置修改的字段
-
.setSql(
" email = '123@qq.com'");
//可以有子查询
-
int result = userMapper.update(user, userUpdateWrapper);
-
}
UPDATE user SET age=?, update_time=?, name=?, email = '123@qq.com' WHERE deleted=0 AND name LIKE ?
-------------------------------------------------------------------------------------以下无正文-------------------
参考文档
1、https://blog.csdn.net/m0_37034294/article/details/82917234
2、https://blog.csdn.net/kepengs/article/details/112345870
3、https://blog.csdn.net/weixin_39615889/article/details/107086931
4、https://blog.csdn.net/weixin_38111957/article/details/91447509
283
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
