# 如何进行多表联查

# 方式一 在Mybatis XML中定义查询SQL 推荐

与传统mybatis使用方式一样

  • 在XML中定义查询SQL,进行inner join / left join / right join 即可
  • 自定义多表查询,返回的查询结果,可自定义VO类与之对应,亦可直接使用对应entity接收
  • 优点:简单方便直接,开发快,易于维护
  • 缺点:多表数据量太大时,可能会影响查询效率,需进行SQL优化处理

# 根据用户ID获取权限菜单分页列表示例

system/src/main/resources/mapper/system/SysPermissionMapper.xml

# Service

@Override
public Paging<SysPermissionQueryVo> getSysPermissionPageList(SysPermissionPageParam sysPermissionPageParam) throws Exception {
    Page<SysPermissionQueryVo> page = new PageInfo<>(sysPermissionPageParam, OrderItem.desc(getLambdaColumn(SysPermission::getCreateTime)));
    IPage<SysPermissionQueryVo> iPage = sysPermissionMapper.getSysPermissionPageList(page, sysPermissionPageParam);
    return new Paging(iPage);
}

# XML

<select id="getMenuListByUserId" resultType="io.geekidea.boot.system.entity.SysPermission">
    select p.*
    from sys_user u
             inner join sys_role r
                        on u.role_id = r.id
             inner join sys_role_permission rp
                        on r.id = rp.role_id
             inner join sys_permission p
                        on rp.permission_id = p.id
    where u.state = 1
      and u.deleted = 0
      and r.state = 1
      and rp.state = 1
      and p.state = 1
      and u.id = #{userId}
</select>
  • resultType="io.geekidea.boot.system.entity.SysPermission"
  • 实际开发中,最好避免使用*号 😁

# 获取用户分页列表信息示例

system/src/main/resources/mapper/system/SysUserMapper.xml














 




 





























<sql id="BaseQuerySelect">
    select
        u.id, u.username, u.nickname, u.phone, u.gender, u.head, u.remark,
        u.state, u.department_id, u.role_id, u.deleted, u.create_time, u.update_time,
        d.name as departmentName,
        r.name as roleName
    from sys_user u
    inner join sys_department d on u.department_id = d.id
    inner join sys_role r on u.role_id = r.id
    where u.deleted = 0
</sql>

<select id="getSysUserById" resultType="io.geekidea.boot.system.vo.SysUserQueryVo">
    <include refid="BaseQuerySelect"/>
    and u.id = #{id}
</select>

<select id="getSysUserPageList" resultType="io.geekidea.boot.system.vo.SysUserQueryVo">
    <include refid="BaseQuerySelect"/>
    <if test="param.departmentId != null">
        and u.department_id = #{param.departmentId}
    </if>
    <if test="param.roleId != null">
        and u.role_id = #{param.roleId}
    </if>
    <if test="param.state != null">
        and u.state = #{param.state}
    </if>
    <if test="param.createTimeStart != null">
        and date_format(u.create_time,'%Y-%m-%d') >= date_format(#{param.createTimeStart},'%Y-%m-%d')
    </if>
    <if test="param.createTimeEnd != null">
        and date_format(u.create_time,'%Y-%m-%d') &lt;= date_format(#{param.createTimeEnd},'%Y-%m-%d')
    </if>
    <if test="param.keyword != null and param.keyword != ''">
        and (
        u.username like concat('%', #{param.keyword} ,'%') or
        u.nickname like concat('%', #{param.keyword} ,'%')
        )
    </if>
    <if test="param.username != null and param.username != ''">
        and u.username like concat('%', #{param.username} ,'%')
    </if>
    <if test="param.nickname != null and param.nickname != ''">
        and u.nickname like concat('%', #{param.nickname} ,'%')
    </if>
</select>
  • resultType="io.geekidea.boot.system.vo.SysUserQueryVo" 与查询结果映射的VO
  • <include refid="BaseQuerySelect"/> 提取了公共查询语句

# 方式二 在代码中组装多表查询

后台都是单表查询操作,然后自行组装数据

  • 追求极致效率,可以考虑使用这种方式
  • 优点:不涉及联表查询,单表查询效率高,无需写XML
  • 缺点:需要自行实现代码组装逻辑,时间和空间复杂度增加,维护成本增加

# 根据用户ID获取权限菜单分页列表示例



 








 







 







@Override
public Paging<SysPermissionQueryVo> getSysPermissionPageList(SysPermissionPageParam sysPermissionPageParam) throws Exception {
    // 根据用户ID获取角色ID
    Long userId = LoginUtil.getUserId();
    LambdaQueryWrapper<SysUser> userWrapper = new LambdaQueryWrapper<>();
    userWrapper.eq(SysUser::getId, userId);
    SysUser sysUser = sysUserMapper.selectOne(userWrapper);
    if (sysUser == null) {
        throw new BusinessException("用户不存在");
    }
    Long roleId = sysUser.getRoleId();
    // 根据角色ID从角色权限关系表中获取所有权限ID
    LambdaQueryWrapper<SysRolePermission> wrapper = new LambdaQueryWrapper();
    wrapper.eq(SysRolePermission::getRoleId, roleId);
    List<SysRolePermission> sysRolePermissions = sysRolePermissionMapper.selectList(wrapper);
    if (CollectionUtils.isEmpty(sysRolePermissions)) {
        throw new BusinessException("用户未设置权限");
    }
    List<Long> permissionIds = sysRolePermissions.stream().map(SysRolePermission::getPermissionId).collect(Collectors.toList());
    // 根据权限ID列表获取权限分页列表
    Page<SysPermission> page = new PageInfo<>(sysPermissionPageParam, OrderItem.desc(getLambdaColumn(SysPermission::getCreateTime)));
    LambdaQueryWrapper<SysPermission> pageWrapper = new LambdaQueryWrapper();
    pageWrapper.in(SysPermission::getId, permissionIds);
    IPage<SysPermission> iPage = sysPermissionMapper.selectPage(page, pageWrapper);
    return new Paging(iPage);
}