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

矫情吗；* 2022-09-14 02:20 145阅读 0赞

### 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

![在这里插入图片描述][02a0c4d2bcec44f5a9cc4f0dd98e2d02.png]  
结果集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

![在这里插入图片描述][5f57ad6ab0a04709afe1a85021b26016.png]

# 二、项目代码 #

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();
        /*重构Controller方法如下*/
        Result selectAll();
    }

SysUserPrivilegesServiceImpl

@Service
    public class SysUserPrivilegesServiceImpl implements ISysUserPrivilegesService { 
        @Resource
        private SysUserPrivilegesMapper sysUserPrivilegesMapper;
        @Autowired
        ISysUserPrivilegesService sysUserPrivilegesService;
        
        //查询所有字段角色表
        @Override
        public List selectAllColumnRole() { 
            List<SysColumnRole> sysColumnRolesList = sysUserPrivilegesMapper.selectAllColumnRole();
            List<LinkedHashMap<String,Object>> 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<LinkedHashMap<String, Object>> 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;
        /** * id */
        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;
        /** * id */
        private String id;
        /** * column_role_id */
        private String columnRoleId;
        /** * column_id */
        private String columnId;
    
        @Override
        public String toString() { 
            return "SysColumnRolePermission{" +
                    "id='" + id + '\'' +
                    ", columnRoleId='" + columnRoleId + '\'' +
                    ", columnId='" + columnId + '\'' +
                    '}';
        }
    }

SysUserPrivilegesMapper

public interface SysUserPrivilegesMapper { 
        /*@Param注解的作用，当我们的sql中需要多个参数的时候，Maybatis会将参数列表中的参数封装成一个Map进行传递，这个过程是通过@Param来实现的， @Param注解括号中的值会作为key,value就是参数实际的值。解析参数的时候会按照@Param中定义的key获取对应的值。 */
        //修改字段角色表
        boolean updateColumnRoleById(@Param("id") String id, @Param("roleCode") String roleCode, @Param("roleName") String roleName,@Param("description") String description, @Param("updateBy") String updateBy);
        
        //查询所有字段角色表
        List<SysColumnRole> selectAllColumnRole();
        //查询fields属性名和值
        List<LinkedHashMap<String,Object>> selectFieldsData();
    }

SysUserPrivilegesMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.aac.modules.system.mapper.SysUserPrivilegesMapper">
        
        <select id="selectAllColumnRole" resultType="com.modules.system.entity.SysColumnRole">
            SELECT id,role_name,role_code,description
            FROM dbo.sys_column_role
        </select>
    
        
        <select id="selectFieldsData" resultType="java.util.LinkedHashMap">
            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
        </select>
    </mapper>

# 三、测试 #

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oeS6Jmr6Jmrfg_size_20_color_FFFFFF_t_70_g_se_x_16]

[02a0c4d2bcec44f5a9cc4f0dd98e2d02.png]: /images/20220828/78965a723d4045c7a84ac7ef815e272d.png
[5f57ad6ab0a04709afe1a85021b26016.png]: /images/20220828/1de4fc16b79d45a2aee4d1b0dcb01363.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oeS6Jmr6Jmrfg_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/a77de3ea40d642cbbf49daa843fcd4e0.png