发布于 

MybatisPlus常用操作

其他
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
if (ObjectUtil.isNotEmpty(condition.getState())) {
            if (condition.getState().equals(UserListByCondition.STATE_DISABLE)) {
                queryWrapperSysUser.eq(SysUser::getStatus, CommonConstant.DISABLE);
            } else if (condition.getState().equals(UserListByCondition.STATE_DIMISSION)) {
                queryWrapperSysUser.eq(SysUser::getDimission, CommonConstant.DIMISSION);
            }
        } else {
            queryWrapperSysUser.isNull(SysUser::getDimission).or().ne(SysUser::getDimission, CommonConstant.DIMISSION);
        }

        if (StringUtils.isNotBlank(condition.getValue()))
            queryWrapperSysUser.like(SysUser::getUserName, condition.getValue())
                    .or()
                    .like(SysUser::getEmail, condition.getValue())
                    .or()
                    .like(SysUser::getPhoneNumber, condition.getValue());

        if (ObjectUtil.isNotEmpty(condition.getDeptId())) {
            queryWrapperSysUser.eq(SysUser::getDeptId, condition.getDeptId());
        }
//生成的sql-> SELECT * FROM sys_user WHERE (dimission IS NULL OR dimission <> 1 AND dept_id = 282) 

if (ObjectUtil.isNotEmpty(condition.getState())) {
            if (condition.getState().equals(UserListByCondition.STATE_DISABLE)) {
                queryWrapperSysUser.eq(SysUser::getStatus, CommonConstant.DISABLE);
            } else if (condition.getState().equals(UserListByCondition.STATE_DIMISSION)) {
                queryWrapperSysUser.eq(SysUser::getDimission, CommonConstant.DIMISSION);
            }
        } else {
            queryWrapperSysUser.nested(q -> q.isNull(SysUser::getDimission).or().ne(SysUser::getDimission, CommonConstant.DIMISSION));
        }

        if (StringUtils.isNotBlank(condition.getValue()))
            queryWrapperSysUser.like(SysUser::getUserName, condition.getValue())
                    .or()
                    .like(SysUser::getEmail, condition.getValue())
                    .or()
                    .like(SysUser::getPhoneNumber, condition.getValue());

        if (ObjectUtil.isNotEmpty(condition.getDeptId())) {
            queryWrapperSysUser.eq(SysUser::getDeptId, condition.getDeptId());
        }
//生成的sql->select * FROM sys_user WHERE ((dimission IS NULL OR dimission <> ?) AND dept_id = ?)

//使用 nested() 方法将 (dimission IS NULL OR dimission <> 1) 这部分条件括起来,形成一个独立的查询块

 if (ObjectUtil.isNotEmpty(condition.getState())) {
            if (condition.getState().equals(UserListByCondition.STATE_DISABLE)) {
                queryWrapperSysUser.eq(SysUser::getStatus, CommonConstant.DISABLE);
            } else if (condition.getState().equals(UserListByCondition.STATE_DIMISSION)) {
                queryWrapperSysUser.eq(SysUser::getDimission, CommonConstant.DIMISSION);
            }
        } else {
            queryWrapperSysUser.nested(q -> q.isNull(SysUser::getDimission).or().ne(SysUser::getDimission, CommonConstant.DIMISSION));
        }

        if (StringUtils.isNotBlank(condition.getValue()))
            queryWrapperSysUser.nested(q -> q.like(SysUser::getUserName, condition.getValue())
                    .or()
                    .like(SysUser::getEmail, condition.getValue())
                    .or()
                    .like(SysUser::getPhoneNumber, condition.getValue()));

        if (ObjectUtil.isNotEmpty(condition.getDeptId())) {
            queryWrapperSysUser.eq(SysUser::getDeptId, condition.getDeptId());
        }
//生成的sql->select * FROM sys_user WHERE ((dimission IS NULL OR dimission <> ?) AND (user_name LIKE ? OR email LIKE ? OR phone_number LIKE ?) AND dept_id = ?)
组合条件
1
2
3
4
5
6
7
8
9
10
LambdaQueryWrapper<SysUser> queryWrapperSysUser = new LambdaQueryWrapper<>();
queryWrapperSysUser.isNull(SysUser::getDimission).or().ne(SysUser::getDimission, CommonConstant.DIMISSION);


if (StringUtils.isNotBlank(condition.getValue()))
            queryWrapperSysUser.like(SysUser::getUserName, condition.getValue())
                    .or()
                    .like(SysUser::getEmail, condition.getValue())
                    .or()
                    .like(SysUser::getPhoneNumber, condition.getValue());
排序
1
queryWrapperSysUser.orderByAsc(SysUser::getUserName);
循环
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
//单体
@Override
    public List<SysUser> selectAllNormalUserByDeptAndChildId(Long deptId) {
        LambdaQueryWrapper<SysDept> queryWrapperSysDept = new LambdaQueryWrapper<>();
        queryWrapperSysDept.eq(SysDept::getDelFlag, CommonConstant.NOT_DELETED);
        queryWrapperSysDept.like(SysDept::getAncestors, deptId);
        List<SysDept> dList = sysDeptPService.list(queryWrapperSysDept);
        List<Long> ds = new ArrayList<>();
        if (CollectionUtil.isNotEmpty(dList))
            ds = dList.stream().map(SysDept::getDeptId).collect(Collectors.toList());
        ds.add(deptId);

        LambdaQueryWrapper<SysUser> queryWrapperSysUser = new LambdaQueryWrapper<>();
        queryWrapperSysUser.eq(SysUser::getDelFlag, CommonConstant.NOT_DELETED);
        queryWrapperSysUser.eq(SysUser::getStatus, CommonConstant.NOT_DISABLE);
        queryWrapperSysUser.nested(q -> q.isNull(SysUser::getDimission).or().ne(SysUser::getDimission, CommonConstant.DIMISSION));
        queryWrapperSysUser.in(SysUser::getDeptId, ds);
        return list(queryWrapperSysUser);
    }

//循环
  @Override
   public List<SysUser> selectAllNormalUserByDeptAndChildId(List<Long> deptIds) {
        LambdaQueryWrapper<SysDept> queryWrapperSysDept = new LambdaQueryWrapper<>();
        queryWrapperSysDept.eq(SysDept::getDelFlag, CommonConstant.NOT_DELETED);

        // 使用nested方法添加条件，避免影响其他查询
        queryWrapperSysDept.nested(i -> {

            // 这里可以添加其他条件，例如：
//            i.eq(SysDept::getStatus, CommonConstant.NOT_DISABLE); // 假设我们只关心状态为0的部门

            // 循环添加deptId条件
            deptIds.forEach(deptId -> i.or().like(SysDept::getAncestors, deptId.toString()));

//这段代码创建了一个条件分组，对于 deptIds 列表中的每个 deptId，都添加了一个 OR 条件。这意味着，如果任何一个 deptId 的条件满足，那么整个分组条件就满足
//不使用 OR,如果 deptIds 列表中有多个值，它们将被添加为 AND 条件。这意味着，只有当所有这些值都满足时，整个分组条件才满足
        });

        // 从数组创建流并构建查询条件,没有其他条件
//        Stream.of(deptIds).forEach(deptId -> {
//            queryWrapperSysDept.or(wrapper -> wrapper.eq(SysDept::getAncestors, deptId));
//        });

        List<SysDept> dList = sysDeptPService.list(queryWrapperSysDept);
        List<Long> ds = new ArrayList<>();
        if (CollectionUtil.isNotEmpty(dList))
            ds = dList.stream().map(SysDept::getDeptId).collect(Collectors.toList());
        ds.addAll(deptIds);

        LambdaQueryWrapper<SysUser> queryWrapperSysUser = new LambdaQueryWrapper<>();
        queryWrapperSysUser.eq(SysUser::getDelFlag, CommonConstant.NOT_DELETED);
        queryWrapperSysUser.eq(SysUser::getStatus, CommonConstant.NOT_DISABLE);
        queryWrapperSysUser.nested(q -> q.isNull(SysUser::getDimission).or().ne(SysUser::getDimission, CommonConstant.DIMISSION));
        queryWrapperSysUser.in(SysUser::getDeptId, ds);
        return list(queryWrapperSysUser);
    }

like和likeRight

like 方法用于模糊匹配字段中包含指定字符串的情况。
例如，如果使用 queryWrapperSysDept.like(SysDept::getAncestors, “123”)，它将生成 SQL 条件，查找 ancestors 字段中包含子字符串 “123” 的记录。

likeRight 方法用于模糊匹配字段值以指定字符串结尾的情况。
例如，如果使用 queryWrapperSysDept.likeRight(SysDept::getAncestors, “123”)，它将生成 SQL 条件，查找 ancestors 字段的值以 “123” 结尾的记录。

设置空值
1
2
3
4
5
6
7
public Boolean cancelPrincipal(Long deptId) {
        LambdaUpdateWrapper<SysDept> updateWrapperSysDept = new LambdaUpdateWrapper<>();
        updateWrapperSysDept.eq(SysDept::getDeptId, deptId);
        updateWrapperSysDept.set(SysDept::getLeaderUserId, null);
        updateWrapperSysDept.set(SysDept::getLeader, "");
        return update(updateWrapperSysDept);
    }
find_in_set
1
2
3
4
5
6
7
public Boolean checkDeptChild(Long deptId) {
       LambdaQueryWrapper<SysDept> queryWrapperSysDept = new LambdaQueryWrapper<>();
       queryWrapperSysDept.eq(SysDept::getStatus, CommonConstant.NOT_DISABLE);
       queryWrapperSysDept.eq(SysDept::getDelFlag, CommonConstant.NOT_DELETED);
       queryWrapperSysDept.apply("find_in_set({0}, ancestors)", deptId);
       return count(queryWrapperSysDept) > 0L;
   }
last
1
2
3
4
5
6
if (Strings.isNotBlank(queryDTO.getSortField())) {
            if (ObjectUtil.isEmpty(queryDTO.getAsc()))
                queryDTO.setAsc(false);
            queryWrapper.last("order by " + StrUtil.toUnderlineCase(queryDTO.getSortField()) + " " + (queryDTO.getAsc() ? "asc" : "desc") + ",id DESC");
        } else
            queryWrapper.orderByAsc(ProjectRefactorBug::getState).orderByDesc(ProjectRefactorBug::getId); //排序 激活——已解决——已关闭 + ID从大到小
入库的时候忽略某个字段
1
2
@TableField(exist = false)
private Long[] menuIds;
分享文章
快来参与讨论吧