SpringBoot+Mybatis关联查询多表数据拼接结果集

• 一、需求描述
• 二、项目代码
• 三、测试

sys_column_role的id跟sys_column_role_permission的column_role_id关联
需要查询结果集1的所有数据，并且也要关联出结果集2的item_text数据
结果集1

 SELECt [id]
      ,[role_name]
      ,[role_code]
      ,[description]   from dbo.sys_column_role

结果集2

SELECt t1.[id]
      ,[column_role_id]
      ,[column_id]
	  ,t2.[item_text]
	  ,t2.[item_value]
  FROM [dbo].[sys_column_role_permission] t1
  left join sys_dict_item t2 on t1.[column_id]=t2.id collate database_default

SysUserPrivilegesController

@RestController
@RequestMapping("/sys/privileges")
@Slf4j
@Api(value = "用户权限分配", tags = {"用户权限分配"})
public class SysUserPrivilegesController {
    @Autowired
    ISysUserPrivilegesService sysUserPrivilegesService;
    //查询所有
    @RequestMapping(value = "/selectAll", method = RequestMethod.POST)
    @ApiOperation("查询所有字段角色表")
    @AutoLog(value = "查询所有字段角色表")
    public Result selectAll() {
        return sysUserPrivilegesService.selectAll();
    }
}

ISysUserPrivilegesService

public interface ISysUserPrivilegesService {
    //查询所有字段角色表
    List selectAllColumnRole();
    
    Result selectAll();
}

SysUserPrivilegesServiceImpl

@Service
public class SysUserPrivilegesServiceImpl implements ISysUserPrivilegesService {
    @Resource
    private SysUserPrivilegesMapper sysUserPrivilegesMapper;
    @Autowired
    ISysUserPrivilegesService sysUserPrivilegesService;
    
    //查询所有字段角色表
    @Override
    public List selectAllColumnRole() {
        List sysColumnRolesList = sysUserPrivilegesMapper.selectAllColumnRole();
        List> sysColumnRolePermissionList = sysUserPrivilegesMapper.selectFieldsData();
        List list = new ArrayList();
        //将sys_column_role_permission和sys_dict_item关联查询的结果，拼接到sys_column_role结果中
        for (int i = 0; i < sysColumnRolesList.size(); i++) {
            SysColumnRole sysColumnRole = new SysColumnRole();
            sysColumnRole.setId(sysColumnRolesList.get(i).getId());
            sysColumnRole.setRoleName(sysColumnRolesList.get(i).getRoleName());
            sysColumnRole.setRoleCode(sysColumnRolesList.get(i).getRoleCode());
            sysColumnRole.setDescription(sysColumnRolesList.get(i).getDescription());
            String id=sysColumnRolesList.get(i).getId();
            List> subPermissions = sysColumnRolePermissionList.stream().filter(v -> v.get("column_role_id").equals(id)).collect(Collectors.toList());
            String fieldsName="";
            String fieldsId="";
            for (int j = 0; j  < subPermissions.size(); j ++) {
                if(StringUtils.isBlank(fieldsId))
                {
                    fieldsId += subPermissions.get(j).get("column_id")==null?"":subPermissions.get(j).get("column_id").toString();
                    fieldsName += subPermissions.get(j).get("item_text")==null?"":subPermissions.get(j).get("item_text").toString();
                }
                else
                {
                    fieldsId += ","+(subPermissions.get(j).get("column_id")==null?"":subPermissions.get(j).get("column_id").toString());
                    fieldsName += ","+(subPermissions.get(j).get("item_text")==null?"":subPermissions.get(j).get("item_text").toString());
                }
            }
            sysColumnRole.setFieldsId(fieldsId);
            sysColumnRole.setFieldsName(fieldsName);
            list.add(sysColumnRole);
        }
        return list;
    }
}    

实体类SysColumnRole

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class SysColumnRole {
    //字段角色表
    private static final long serialVersionUID = 1L;
    
    private String createBy;
    
    private Date createTime;

    
    private String description;
    
    private String roleCode;
    
    private String roleName;
    
    private String id;
    
    private String updateBy;

    
    private Date updateTime;

    private String fieldsId;//数据库中此字段无

    private String fieldsName;//数据库中此字段无

    @Override
    public String toString() {
        return "SysColumnRole{" +
                "createBy='" + createBy + ''' +
                ", createTime=" + createTime +
                ", description='" + description + ''' +
                ", roleCode='" + roleCode + ''' +
                ", roleName='" + roleName + ''' +
                ", id='" + id + ''' +
                ", updateBy='" + updateBy + ''' +
                ", updateTime=" + updateTime +
                ", fieldsId='" + fieldsId + ''' +
                ", fieldsName='" + fieldsName + ''' +
                '}';
    }
}

实体类SysColumnRolePermission

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class SysColumnRolePermission {
    //字段角色子表
    private static final long serialVersionUID = 1L;
    
    private String id;
    
    private String columnRoleId;
    
    private String columnId;

    @Override
    public String toString() {
        return "SysColumnRolePermission{" +
                "id='" + id + ''' +
                ", columnRoleId='" + columnRoleId + ''' +
                ", columnId='" + columnId + ''' +
                '}';
    }
}

SysUserPrivilegesMapper

public interface SysUserPrivilegesMapper {
    
    //修改字段角色表
    boolean updateColumnRoleById(@Param("id") String id, @Param("roleCode") String roleCode, @Param("roleName") String roleName,@Param("description") String description, @Param("updateBy") String updateBy);
    
    //查询所有字段角色表
    List selectAllColumnRole();
    //查询fields属性名和值
    List> selectFieldsData();
}

SysUserPrivilegesMapper.xml

    
    
        SELECT id,role_name,role_code,description
        FROM dbo.sys_column_role
    

    
    
        SELECT t1.id
             ,column_role_id
             ,column_id
             ,t2.item_text
             ,t2.item_value
        FROM dbo.sys_column_role_permission t1
        left join sys_dict_item t2 on t1.column_id=t2.id collate database_default