mybatis-plus联表查询实践

Bill11234

已于 2023-10-09 18:01:35 修改

阅读量399

点赞数

文章标签： mybatis java

于 2023-10-08 11:45:23 首次发布

本文链接：https://blog.csdn.net/Bill11234/article/details/133675370

版权

代码联表前提条件：引入依赖 com.github.yulichang.base;

@MapperBean("smart")
public interface SysAuthMapper extends MPJBaseMapper<SysAuth> {}

示例一：三表联表

MPJLambdaWrapper<SysUser> mpjWrapper = new MPJLambdaWrapper<>();
        mpjWrapper.selectAll(SysUser.class)
                .leftJoin(SysUserRole.class, SysUserRole::getUserId, SysUser::getGuid)
                .leftJoin(SysRoleAuth.class,SysRoleAuth::getRoleId,SysUserRole::getRoleId)
                .eq(SysUser::getEnable,1)
                .eq(SysUser::getState, AuditStateEnum.AUDITED.getValue())
                .and(m->m.eq(SysRoleAuth::getAuthId,authId)
                        .or()
                        .eq(SysUser::getUserType, UserTypeEnum.PMU.name()))
                .distinct();
        List<SysUser> sysUsers = sysUserMapper.selectJoinList(SysUser.class, mpjWrapper);```

示例二：自定义联表查询结果

MPJLambdaWrapper<SysAuth> mpjWrapper = new MPJLambdaWrapper<>();
        mpjWrapper.selectAs(SysAuth::getGuid,SysAuthOperationDTO::getAuthId)
                .selectAs(SysOperation::getGuid,SysAuthOperationDTO::getOperationId)
                .selectAs(SysOperation::getOperationId,SysAuthOperationDTO::getOperationSign)
                .leftJoin(SysAuthOperation.class,SysAuthOperation::getAuthId,SysAuth::getGuid)
                .leftJoin(SysOperation.class,SysOperation::getGuid,SysAuthOperation::getOperationId);
        List<SysAuthOperationDTO> sysAuthOperations = sysAuthMapper.selectJoinList(SysAuthOperationDTO.class, mpjWrapper);

示例三：groupBy和having的使用（副表in的数据都要包含在查询结果）

 MPJLambdaWrapper<SysUser> wrapper = new MPJLambdaWrapper<>();
 if(!CollectionUtil.isEmpty(userVo.getRoleIds())){
            //根据角色id条件查询
            wrapper.leftJoin(SysUserRole.class, SysUserRole::getUserId, SysUser::getGuid)
                    .in(SysUserRole::getRoleId,userVo.getRoleIds())
                    .groupBy(SysUser::getAccount)
                    .having("count(*) >="+userVo.getRoleIds().size());
        }
        wrapper.selectAll(SysUser.class)
                .ne(StrUtil.isNotEmpty(guid), SysUser::getGuid, guid)
                .ne(StrUtil.isNotEmpty(adminGuid), SysUser::getGuid, adminGuid)
                .like(StrUtil.isNotEmpty(userVo.getDeptId()), SysUser::getDeptId, userVo.getDeptId())
                .like(StrUtil.isNotEmpty(userVo.getAccount()), SysUser::getAccount, userVo.getAccount())
                .like(StrUtil.isNotEmpty(userVo.getUserName()), SysUser::getUserName, userVo.getUserName())
                .eq(StrUtil.isNotEmpty(userVo.getUserType()), SysUser::getUserType, userVo.getUserType())
                .eq(StrUtil.isNotEmpty(userVo.getState()), SysUser::getState, userVo.getState())
                .like(StrUtil.isNotEmpty(userVo.getEmail()), SysUser::getEmail, userVo.getEmail())
                .like(StrUtil.isNotEmpty(userVo.getPhoneNumber()), SysUser::getPhoneNumber, userVo.getPhoneNumber())
                .eq(!StringUtils.isNullOrEmpty(userVo.getSex()), SysUser::getSex, userVo.getSex())
                .eq(userVo.getEnable() != null, SysUser::getEnable, userVo.getEnable())
                .eq(SysUser::getDeleted, 0)
                .orderByDesc(SysUser::getCreateTime);
        return wrapper;