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

落日映苍穹つ 2024-03-23 00:34 101阅读 0赞

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
    }

**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);
    }

**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
    }

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: 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.看图示例**  
![在这里插入图片描述][868422e97ac64147951736389c4eeabf.png]

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

![在这里插入图片描述][c09a13ff32974a38be094997ef45c2c2.png]

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

[868422e97ac64147951736389c4eeabf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/a9d4a7db797c4da48136dca5e7a9543d.png
[c09a13ff32974a38be094997ef45c2c2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/e3c9f2580bcc49b88fdb51b389078e8c.png