Mybatis-Plus条件构造器

逃离我推掉我的手 2024-03-04 02:02 39阅读 0赞

**目录**

Mybatis-Plus条件构造器

1、allEq：全部

2、eq：等于

3、ne：不等于

4、gt：大于

5、ge：大于等于

6、lt：小于

7、le：小于等于

8、between：两者之间

9、notBetween：两者之外

10、like：模糊查询

11、notLike：模糊查询

12、likeLeft：

13、likeRight

14、isNull

15、isNotNull

15、in

16、notIn

17、inSql

18、notInSql

19、groupBy

20、orderByAsc

21、orderByDesc

22、orderBy

23、having

24、or

25、and

26、nested

27、last

28、exists

29、notExists

QueryWrapper

30、select

UpdateWrapper

31、set

32、lambda

--------------------

### Mybatis-Plus条件构造器 ###

#### 1、allEq：全部 ####

allEq(Map<R, V> params)
    allEq(Map<R, V> params, boolean null2IsNull)
    allEq(boolean condition, Map<R, V> params, boolean null2IsNull)

全部[eq][](或个别[isNull][])

> 个别参数说明:
> 
> `params` : `key`为数据库字段名,`value`为字段值  
> `null2IsNull` : 为`true`则在`map`的`value`为`null`时调用 [isNull][] 方法,为`false`时则忽略`value`为`null`的

示例：

@Test
        public void AllEq() {
            //设置条件
            Map<String,Object> params = new HashMap<>();
            params.put("name", "坤坤");
            params.put("age", "18");
            params.put("password", null);
            QueryWrapper<Users> wrapper = new QueryWrapper<>();
            //SQL:SELECT id,user_name,password,name,age,email FROM tb_user WHERE password IS NULL AND name = ? AND age = ?
            //wrapper.allEq(params);
    
            //去掉为空的条件
            //SQL:SELECT id,user_name,password,name,age,email FROM tb_user WHERE name = ? AND age = ?
            //wrapper.allEq(params,false);
    
            //过滤条件
            //SQL:SELECT id,user_name,password,name,age,email FROM tb_user WHERE name = ?
            wrapper.allEq((k, v) -> (k.equals("name") || k.equals("id")),params);
            //SQL:SELECT id,user_name,password,name,age,email FROM tb_user WHERE name = ? AND age = ?
            wrapper.allEq((k, v) -> (k.equals("name") || k.equals("age")),params);
            
            List<Users> users = this.userMapper.selectList(wrapper);
            for (Users user : users) {
                System.out.println(user);
            }
    
        }

#### 2、eq：等于 ####

eq(R column, Object val)
    eq(boolean condition, R column, Object val)

*  等于 =
 *  例: `eq("name", "老王")`\--->`name = '老王'`

示例：

//查询一条数据
        @Test
        public void selectOne() {
    
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("user_name","11");
            //查询一条，如果多余1条会报异常
            Users user = this.userMapper.selectOne(queryWrapper);
            System.out.println("result = " + user);
        }

#### 3、ne：不等于 ####

ne(R column, Object val)
    ne(boolean condition, R column, Object val)

*  不等于 <>
 *  例: `ne("name", "老王")`\--->`name <> '老王'`

示例：

//不等于
        @Test
        public void selectListNe() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE name <> ? 
            queryWrapper.ne("name","王五");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 4、gt：大于 ####

gt(R column, Object val)
    gt(boolean condition, R column, Object val)

*  大于 >
 *  例: `gt("age", 18)`\--->`age > 18`

//大于
        @Test
        public void selectListGt() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE age > ? 
            queryWrapper.gt("age","20");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 5、ge：大于等于 ####

ge(R column, Object val)
    ge(boolean condition, R column, Object val)

*  大于等于 >=
 *  例: `ge("age", 18)`\--->`age >= 18`

//大于等于
        @Test
        public void selectListGe() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE age >= ? 
            queryWrapper.ge("age","20");//
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 6、lt：小于 ####

lt(R column, Object val)
    lt(boolean condition, R column, Object val)

*  小于 <
 *  例: `lt("age", 18)`\--->`age < 18`

//小于
        @Test
        public void selectListLt() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE age < ?
            queryWrapper.lt("age","20");//
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 7、le：小于等于 ####

le(R column, Object val)
    le(boolean condition, R column, Object val)

*  小于等于 <=
 *  例: `le("age", 18)`\--->`age <= 18`

//小于等于
        @Test
        public void selectListLe() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE age <= ?
            queryWrapper.le("age","20");//
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 8、between：两者之间 ####

between(R column, Object val1, Object val2)
    between(boolean condition, R column, Object val1, Object val2)

*  BETWEEN 值1 AND 值2
 *  例: `between("age", 18, 30)`\--->`age between 18 and 30`

//两者之间
        @Test
        public void selectListBetween() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE age BETWEEN ? AND ?
            queryWrapper.between("age",15,20);//
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### **9、notBetween：两者之外** ####

notBetween(R column, Object val1, Object val2)
    notBetween(boolean condition, R column, Object val1, Object val2)

*  NOT BETWEEN 值1 AND 值2
 *  例: `notBetween("age", 18, 30)`\--->`age not between 18 and 30`

//两者之外
        @Test
        public void selectListNotBetween() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE age NOT BETWEEN ? AND ? 
            queryWrapper.notBetween("age",15,20);//
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 10、like：模糊查询 ####

like(R column, Object val)
    like(boolean condition, R column, Object val)

*  LIKE '%值%'
 *  例: `like("name", "王")`\--->`name like '%王%'`

//模糊查询
        @Test
        public void selectListLike() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE name LIKE ?
            queryWrapper.like("name","王");//
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 11、notLike：模糊查询 ####

notLike(R column, Object val)
    notLike(boolean condition, R column, Object val)

*  NOT LIKE '%值%'
 *  例: `notLike("name", "王")`\--->`name not like '%王%'`

//模糊查询
        @Test
        public void selectListNotLike() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE name NOT LIKE ? 
            queryWrapper.notLike("name","王");//
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 12、likeLeft： ####

likeLeft(R column, Object val)
    likeLeft(boolean condition, R column, Object val)

*  LIKE '%值'
 *  例: `likeLeft("name", "王")`\--->`name like '%王'`

@Test
        public void selectListLikeLeft() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE name LIKE ? Parameters: %王(String)
            queryWrapper.likeLeft("name","王");//查询年龄大于20的条数
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 13、likeRight ####

likeRight(R column, Object val)
    likeRight(boolean condition, R column, Object val)

*  LIKE '值%'
 *  例: `likeRight("name", "王")`\--->`name like '王%'`

@Test
        public void selectListLikeRight() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE name LIKE ?   王%(String)
            queryWrapper.likeRight("name","王");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 14、isNull ####

isNull(R column)
    isNull(boolean condition, R column)

*  字段 IS NULL
 *  例: `isNull("name")`\--->`name is null`

@Test
        public void selectListIsNull() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE name IS NULL 
            queryWrapper.isNull("name");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 15、isNotNull ####

isNotNull(R column)
    isNotNull(boolean condition, R column)

*  字段 IS NOT NULL
 *  例: `isNotNull("name")`\--->`name is not null`

@Test
        public void selectListIsNotNull() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE name IS NOT NULL 
            queryWrapper.isNotNull("name");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 15、in ####

in(R column, Collection<?> value)
    in(boolean condition, R column, Collection<?> value)

*  字段 IN (value.get(0), value.get(1), ...)
 *  例: `in("age",{1,2,3})`\--->`age in (1,2,3)`

in(R column, Object... values)
    in(boolean condition, R column, Object... values)

*  字段 IN (v0, v1, ...)
 *  例: `in("age", 1, 2, 3)`\--->`age in (1,2,3)`

@Test
        public void selectListIn() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE age IN (?,?,?)
            queryWrapper.in("age",15,18,19);
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 16、notIn ####

notIn(R column, Collection<?> value)
    notIn(boolean condition, R column, Collection<?> value)

*  字段 NOT IN (value.get(0), value.get(1), ...)
 *  例: `notIn("age",{1,2,3})`\--->`age not in (1,2,3)`

notIn(R column, Object... values)
    notIn(boolean condition, R column, Object... values)

*  字段 NOT IN (v0, v1, ...)
 *  例: `notIn("age", 1, 2, 3)`\--->`age not in (1,2,3)`

@Test
        public void selectListNotIn() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE age NOT IN (?,?,?)
            queryWrapper.notIn("age",15,18,19);
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 17、inSql ####

inSql(R column, String inValue)
    inSql(boolean condition, R column, String inValue)

*  字段 IN ( sql语句 )
 *  例: `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 selectListInSql() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE id IN (select id from tb_user where id < 3)
            queryWrapper.inSql("id", "select id from tb_user where id < 3");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 18、notInSql ####

notInSql(R column, String inValue)
    notInSql(boolean condition, R column, String inValue)

*  字段 NOT IN ( sql语句 )
 *  例: `notInSql("age", "1,2,3,4,5,6")`\--->`age not in (1,2,3,4,5,6)`
 *  例: `notInSql("id", "select id from table where id < 3")`\--->`id not in (select id from table where id < 3)`

@Test
        public void selectListNotInSql() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE id NOT IN (select id from tb_user where id < 3) 
            queryWrapper.notInSql("id", "select id from tb_user where id < 3");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 19、groupBy ####

groupBy(R... columns)
    groupBy(boolean condition, R... columns)

*  分组：GROUP BY 字段, ...
 *  例: `groupBy("id", "name")`\--->`group by id,name`

@Test
        public void selectListGroupBy() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user GROUP BY age
            queryWrapper.groupBy("age");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 20、orderByAsc ####

orderByAsc(R... columns)
    orderByAsc(boolean condition, R... columns)

*  排序：ORDER BY 字段, ... ASC
 *  例: `orderByAsc("id", "name")`\--->`order by id ASC,name ASC`

@Test
        public void selectListOrderByAsc() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user ORDER BY age ASC 
            queryWrapper.orderByAsc("age");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 21、orderByDesc ####

orderByDesc(R... columns)
    orderByDesc(boolean condition, R... columns)

*  排序：ORDER BY 字段, ... DESC
 *  例: `orderByDesc("id", "name")`\--->`order by id DESC,name DESC`

@Test
        public void selectListOrderByDesc() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user ORDER BY age DESC
            queryWrapper.orderByDesc("age");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 22、orderBy ####

orderBy(boolean condition, boolean isAsc, R... columns)

*  排序：ORDER BY 字段, ...
 *  例: `orderBy(true, true, "id", "name")`\--->`order by id ASC,name ASC`

@Test
        public void selectListOrderByDesc() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user ORDER BY age ASC
            /*Params:
            condition – 执行条件
            isAsc – 是否是 ASC 排序
            columns – 字段数组
            */
            queryWrapper.orderBy(true,true,"age");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 23、having ####

having(String sqlHaving, Object... params)
    having(boolean condition, String sqlHaving, Object... params)

*  HAVING ( sql语句 )
 *  例: `having("sum(age) > 10")`\--->`having sum(age) > 10`
 *  例: `having("sum(age) > {0}", 11)`\--->`having sum(age) > 11`

@Test
        public void selectListHaving() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE name = ? HAVING sum(age) > 10
            queryWrapper
                    .eq("name","王五")
                    .having("sum(age) > 10");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 24、or ####

or()
    or(boolean condition)

注意事项:

主动调用`or`表示紧接着下一个**方法**不是用`and`连接!(不调用`or`则默认为使用`and`连接)

`eq("id",1).or().eq("name","老王")`\--->`id = 1 or name = '老王'`

@Test
        public void selectListOr() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE name = ? OR age = ? 
            queryWrapper
                    .eq("name","王五")
                    .or()
                    .eq("age","20");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

or(Consumer<Param> consumer)
    or(boolean condition, Consumer<Param> consumer)

*  OR 嵌套
 *  例: `or(i -> i.eq("name", "李白").ne("status", "活着"))`\--->`or (name = '李白' and status <> '活着')`

#### 25、and ####

and(Consumer<Param> consumer)
    and(boolean condition, Consumer<Param> consumer)

*  AND 嵌套
 *  例: `and(i -> i.eq("name", "李白").ne("status", "活着"))`\--->`and (name = '李白' and status <> '活着')`

@Test
        public void selectListAnd() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,user_name,password,name,age,email FROM tb_user WHERE name = ? AND ( age = ? ) 
            queryWrapper
                    .eq("name","王五")
                    .and(i -> i.eq("age", "18"));
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

#### 26、nested ####

nested(Consumer<Param> consumer)
    nested(boolean condition, Consumer<Param> consumer)

*  正常嵌套 不带 AND 或者 OR
 *  例: `nested(i -> i.eq("name", "李白").ne("status", "活着"))`\--->`(name = '李白' and status <> '活着')`

#### 27、last ####

last(String lastSql)
    last(boolean condition, String lastSql)

无视优化规则直接拼接到 sql 的最后

注意事项:

只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用

last("limit 1")

#### 28、exists ####

exists(String existsSql)
    exists(boolean condition, String existsSql)

*  拼接 EXISTS ( sql语句 )
 *  例: `exists("select id from table where age = 1")`\--->`exists (select id from table where age = 1)`

#### 29、notExists ####

notExists(String notExistsSql)
    notExists(boolean condition, String notExistsSql)

*  拼接 NOT EXISTS ( sql语句 )
 *  例: `notExists("select id from table where age = 1")`\--->`not exists (select id from table where age = 1)`

### QueryWrapper ###

> 说明:
> 
> 继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件  
> 及 LambdaQueryWrapper, 可以通过 new QueryWrapper().lambda() 方法获取

#### 30、select ####

select(String... sqlSelect)
    select(Predicate<TableFieldInfo> predicate)
    select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)

设置查询字段

> 说明:
> 
> 以上方法分为两类.  
> 第二类方法为:过滤查询字段(主键除外),入参不包含 class 的调用前需要`wrapper`内的`entity`属性有值! 这两类方法重复调用以最后一次为准

*  例: `select("id", "name", "age")`
 *  例: `select(i -> i.getProperty().startsWith("test"))`

@Test
        public void selectListExists() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,name,age FROM tb_user
            queryWrapper
                    .exists("select id from tb_user where age = 18");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

### UpdateWrapper ###

> 说明:
> 
> 继承自 `AbstractWrapper` ,自身的内部属性 `entity` 也用于生成 where 条件  
> 及 `LambdaUpdateWrapper`, 可以通过 `new UpdateWrapper().lambda()` 方法获取!

#### 31、set ####

set(String column, Object val)
    set(boolean condition, String column, Object val)

*  SQL SET 字段
 *  例: `set("name", "老李头")`
 *  例: `set("name", "")`\--->数据库字段值变为**空字符串**
 *  例: `set("name", null)`\--->数据库字段值变为`null`

@Test
        public void selectListSet() {
            UpdateWrapper<Users> updateWrapper = new UpdateWrapper<>();
            //SQL: UPDATE tb_user SET name=?
            //修改全部
            updateWrapper
                    .set("name", "老李头");
            //根据条件查询集合
            int update = this.userMapper.update(null, updateWrapper);
            System.out.println(update);
        }

@Test
        public void selectListSetSql() {
            UpdateWrapper<Users> updateWrapper = new UpdateWrapper<>();
            //SQL: UPDATE tb_user SET age = age + 1 WHERE name = ? 
            //根据条件修改
            updateWrapper
                    .setSql("age = age + 1").eq("name","老李头");
            //根据条件查询集合
            int update = this.userMapper.update(null, updateWrapper);
            System.out.println(update);
        }

#### 32、lambda ####

>  *  获取 `LambdaWrapper`  
>     在`QueryWrapper`中是获取`LambdaQueryWrapper`  
>     在`UpdateWrapper`中是获取`LambdaUpdateWrapper`

@Test
        public void selectListLambda() {
            QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
            //SQL: SELECT id,name,age FROM tb_user
            queryWrapper
                    .lambda()
                    .select(Users::getId,Users::getAge,Users::getName)
                    .like(Users::getName,"老");
            //根据条件查询集合
            List<Users> userList = this.userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }

[eq]: https://baomidou.com/pages/10c804/#eq
[isNull]: https://baomidou.com/pages/10c804/#isnull