mybatis-plus中多条件查询使用and合or嵌套使用_mybatisplus or查询

MyBatisPlus-QueryWrapper有很多查询方法用于构建查询条件
1.存在的方法
gt、ge、lt、le、isNull、isNotNull
eq、ne
between、notBetween
allEq
like、notLike、likeLeft、likeRight
in、notIn、inSql、notinSql、exists、notExists
or、and
嵌套 or、嵌套 and
orderBy、orderByDesc、orderByAsc
last
指定要查询的列
set、setSql

2.ge、isNull使用

@Test
public void queryWrapperOne() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.
            isNull("name")
            .ge("age", 23)
            .isNotNull("email");
    // 逻辑删除
    int result = userMapper.delete(queryWrapper);
    System.out.println(result);
    // 最终的语句为：UPDATE user SET deleted=1 WHERE deleted=0 AND name IS NULL AND age >= ? AND email IS NOT NULL
}
1
2
3
4
5
6
7
8
9
10
11
12

3.eq、ne的使用

@Test
public void queryWrapperTwo() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("name", "BNTang");
    // selectOne：返回的是一条记录，当出现多条时会报错
    User user = userMapper.selectOne(queryWrapper);
    System.out.println(user);
}
1
2
3
4
5
6
7
8

4.between、notBetween的使用
BETWEEN 值1 AND 值2
例: between(“age”, 18, 30) → age between 18 and 30

NOT BETWEEN 值1 AND 值2
例: notBetween(“age”, 18, 30) → age not between 18 and 30

5.allEq的使用
例1: allEq({id:1,name:“老王”,age:null})→id = 1 and name = ‘老王’ and age is null
例2: allEq({id:1,name:“老王”,age:null}, false)→id = 1 and name = ‘老王’
构建的queryParamsMap的key 值必须是数据库中的字段，或查询临时表中的字段

@Test
public void queryWrapperFour() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    Map<String, Object> queryParamsMap = new HashMap<>();
    queryParamsMap.put("id", 1373266771933462530L);
    queryParamsMap.put("name", "BNTang");
    queryParamsMap.put("age", 23);
    queryWrapper.allEq(queryParamsMap);
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
    // sql为：SELECT　＊　FROM　user　WHERE　deleted=0　AND name = 'BNTang'　AND id = '1373266771933462530'　AND age = 23
}
1
2
3
4
5
6
7
8
9
10
11
12

filter: 过滤函数, 是否允许字段传入比对条件中
params 与 null、IsNull 同上

例1: allEq((k,v) -> k.indexOf(“a”) >= 0, {id:1,name:“老王”,age:null})→name = ‘老王’ and age is null
例2: allEq((k,v) -> k.indexOf(“i”) >= 0, {id:1,name:“老王”,age:null})→name = ‘老王’
例3: allEq((k,v) -> k.indexOf(“a”) >= 0, {id:1,name:“老王”,age:null}, false)→name = ‘老王’

6.in、notIn、inSql、notinSql、exists、notExists的使用
例: in(“age”,{1,2,3})→age in (1,2,3)
queryWrapper.in(“age”, (Object[]) “5,6”.split(“,”));
例: notIn(“age”,{1,2,3})→age not in (1,2,3)

例: 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)

例: exists(“select id from table where age = 1”)→exists (select id from table where age = 1)
例: notExists(“select id from table where age = 1”)→not exists (select id from table where age = 1)

7.and合or嵌套使用

@Test
public void queryWrapperSeven() {
    // 修改值
    User user = new User();
    user.setAge(99);
    user.setName("BNTang6666");
    // 修改条件
    UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
    userUpdateWrapper
            .like("name", "BNTang")
            .or()
            .between("age", 20, 30);
    int result = userMapper.update(user, userUpdateWrapper);
    System.out.println(result);
    // sql 为：
     /* UPDATE USER SET NAME = 'BNTang6666,age=99,update_time = '2021-03-27 00:40:27'
        WHERE deleted = 0 AND NAME LIKE '%BNTang%'  OR age BETWEEN 20 AND 30*/
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

例1: wrapper.eq(“id”,1).or().eq(“name”,“老王”)→id = 1 or name = ‘老王’

例2: wrapper.eq(“age”,30).or(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))→age=30 or (name = ‘李白’ and status <> ‘活着’)

例3: and(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))→and (name = ‘李白’ and status <> ‘活着’)

例4：wrapper.like(“name”, “BNTang”).or().between(“age”, 20, 30);
结果类似于例1

例5：
wrapper.and(wrapper->wrapper.eq(“SEND_USER_ID”,“001”).eq(“RECEIVE_USER_ID”,“002”))
.or(wrapper->wrapper.eq(“SEND_USER_ID”,“003”).eq(“RECEIVE_USER_ID”,“004”));
-> AND ((SEND USER ID = “001” AND RECEIVE USER ID = “002”) OR (SEND USER ID =“003” AND RECEIVE USER ID = “004”))

例6：
queryWrapper.eq(“name”, “wangsf”).nested(w->w.and(wp->wp.in(“contract_mode”, (Object[]) “5,6”.split(“,”)).ge(“actual_completion_time”, now.minusYears(2).format(fmt) + " 23:59:59")).or(wp->wp.notIn(“contract_mode”, (Object[]) “5,6”.split(“,”)).ge(“actual_completion_time”, now.minusYears(5).format(fmt) + " 23:59:59")));
}
->
name = ? AND ((contract_mode IN (?,?) AND actual_completion_time BETWEEN ? AND ?) OR (contract_mode NOT IN (?,?) AND actual_completion_time BETWEEN ? AND ?))

8.看图示例

9.注意分页查询
在实际开发过程中，一般会封装一个对象作为接收查询字段，这些条件一般都是对应单表A中的字段，直接查询;也可能是查询表A中的字段范围比如某个日期范围,需要构建新查询字段参数范围查询 ；也可能条件是关联其他表B中的字段，常用方法是先构建中间表，再带入分页参数查询（${ew.customSqlSegment} 是构建的分页及查询参数；t 相当于中间表）

参考https://www.zhuxianfei.com/java/58478.html