MyBatis-Plus——条件构造器——QueryWrapper查询条件封装

1. Wrapper 条件构造抽象类，最顶端的父类

AbstractWrapper: 用于查询条件封装，生成sql的where条件
  QueryWrapper: 查询条件封装
  UpdateWrapper: Update条件封装

2. 条件构造器——mp实现复杂的查询操作

官方文档链接：https://baomidou.com/pages/10c804/#abstractwrapper

2.1 ge、gt、le、lt，isNull、isNotNull

1. 查询所有年龄大于等于18的数据

@Test
public void testSelete(){
    QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
    userQueryWrapper
            .ge("age",18);    //age的值大于等于18
    List<User> users = userMapper.selectList(userQueryWrapper);
    System.out.println(users);
}
1
2
3
4
5
6
7
8

程序执行SQL日志打印：

==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM t_user WHERE deleted=0 AND (age >= ?)
==> Parameters: 18(Integer)
<==    Columns: id, name, age, email, create_time, update_time, version, deleted
<==        Row: 4, 铁蛋儿, 18, 20000502, 2022-09-11 20:24:56, 2022-09-11 20:25:08, 1, 0
<==        Row: 7, 铁蛋儿, 20, 848698119@qq.com, 2022-09-11 21:24:55, 2022-09-11 21:24:55, 1, 0
<==      Total: 2
1
2
3
4
5
6

2.2 eq、ne

查询所有name等于“铁蛋儿”的数据

@Test
public void testSelete(){
    QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
    userQueryWrapper
            .eq("name","铁蛋儿"); 
    List<User> users = userMapper.selectList(userQueryWrapper);
    System.out.println(users);
}
1
2
3
4
5
6
7
8

程序执行SQL日志打印：

==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM t_user WHERE deleted=0 AND (name = ?)
==> Parameters: 铁蛋儿(String)
<==    Columns: id, name, age, email, create_time, update_time, version, deleted
<==        Row: 4, 铁蛋儿, 18, 20000502, 2022-09-11 20:24:56, 2022-09-11 20:25:08, 1, 0
<==        Row: 7, 铁蛋儿, 20, 848698119@qq.com, 2022-09-11 21:24:55, 2022-09-11 21:24:55, 1, 0
<==      Total: 2
1
2
3
4
5
6

2.3 between、notBetween

包含大小边界

查询age在【18,20】的数据

@Test
public void testSelete(){
    QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
    userQueryWrapper
            .between("age",18,20);
    List<User> users = userMapper.selectList(userQueryWrapper);
    System.out.println(users);
}
1
2
3
4
5
6
7
8

程序执行SQL日志打印：

==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM t_user WHERE deleted=0 AND (age BETWEEN ? AND ?)
==> Parameters: 18(Integer), 20(Integer)
<==    Columns: id, name, age, email, create_time, update_time, version, deleted
<==        Row: 4, 铁蛋儿, 18, 20000502, 2022-09-11 20:24:56, 2022-09-11 20:25:08, 1, 0
<==        Row: 7, 铁蛋儿, 20, 848698119@qq.com, 2022-09-11 21:24:55, 2022-09-11 21:24:55, 1, 0
<==      Total: 2
1
2
3
4
5
6

2.4 like、notLike、likeLeft、likeRight

selectMaps()返回Map集合列表，通常配合select（）使用

• LIKE ‘%值%’
  例: like(“name”, “王”)—>name like ‘%王%’
• NOT LIKE ‘%值%’
  例: notLike(“name”, “王”)—>name not like ‘%王%’
• likeLeft = LIKE ‘%值’
  例: likeLeft(“name”, “王”)—>name like ‘%王’
• likeRight = LIKE ‘值%’
  例: likeRight(“name”, “王”)—>name like ‘王%’

@Test
public void testSelete(){
    QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
    userQueryWrapper
            .like("name","铁");
    List<User> users = userMapper.selectList(userQueryWrapper);
    System.out.println(users);
}
1
2
3
4
5
6
7
8

程序执行SQL日志打印：

==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM t_user WHERE deleted=0 AND (name LIKE ?)
==> Parameters: %铁%(String)
<==    Columns: id, name, age, email, create_time, update_time, version, deleted
<==        Row: 4, 铁蛋儿, 18, 20000502, 2022-09-11 20:24:56, 2022-09-11 20:25:08, 1, 0
<==        Row: 7, 铁蛋儿, 20, 848698119@qq.com, 2022-09-11 21:24:55, 2022-09-11 21:24:55, 1, 0
<==      Total: 2
1
2
3
4
5
6

2.5 orderBy、orderBydesc、orderByAsc

排序操作

• orderBy排序：ORDER BY 字段, … 默认升序
  例: orderBy(true, true, “id”, “name”)—>order by id ASC,name ASC
  程序执行SQL日志打印：
  SELECT id,name,age,email,create_time,update_time,version,deleted FROM t_user WHERE deleted=0 ORDER BY id ASC,age ASC

• orderBydesc排序：ORDER BY 字段, … DESC 降序
  例: orderByDesc(“id”, “name”)—>order by id DESC,name DESC
  

• orderByAsc排序：ORDER BY 字段, … ASC 升序
  例: orderByAsc(“id”, “name”)—>order by id ASC,name ASC