已解决：Mybatis中一对多（多对多部分）查询数据只有一条

清疚 2021-12-21 12:35 501阅读 0赞

### 表关系 ###

**主表：**角色——用户——未入职原因表

**关联表：**用户角色表（多对多）、用户未入职原因表（多对多）

### Java类对象 ###

**用户表**

public class PmsUser implements Serializable {
        private Long pmsUserId;
    
        private String pmsUserName;
    
        private String pmsUserPhone;
    
        private Integer pmsUserSalary;
    
        private String pmsUserIntervtime;
    
        private String pmsUserNotekeeper;
    
        private String pmsUserImgsurl;
    
        private String pmsUserUptime;
    
        private Long pmsUserStationid;
    
        private Integer pmsUserLevel;
    
        private String pmsUserRemark;
    
        private String[] roleIds;
    
        private String[] passreasonIds;
    
        private List<PmsRole> roleList;
    
        private List<PmsPassreason> passreasonList;
    
        private PmsStation station;
    
        private static final long serialVersionUID = 1L;
    ...

**角色表**

public class PmsRole implements Serializable {
        private Long pmsRoleId;
    
        private String pmsRoleName;
    
        private Boolean pmsRoleStatus;
    
        private String pmsRoleCreatetime;
    
        private String pmsRoleDesc;
    
        private List<PmsUser> users;
    ...

**未入职原因表**

public class PmsPassreason implements Serializable {
        private Long pmsPassreasonId;
    
        private String pmsPassreasonName;
    
        private List<PmsUser> users;
    ...

### 接口类及映射 ###

**接口**

public interface PmsUserMapper {
        ....
        // 查询所有用户（包括所有角色、未入职原因数据）
        List<PmsUser> getAllUserAndPassreasonAndRoleList();
    
    }

**映射**

<resultMap id="DeepUserMap" type="com.ming.pms.mbg.model.PmsUser">
        <id column="pms_user_id" jdbcType="BIGINT" property="pmsUserId" />
        <result column="pms_user_name" jdbcType="VARCHAR" property="pmsUserName" />
        <result column="pms_user_phone" jdbcType="VARCHAR" property="pmsUserPhone" />
        <result column="pms_user_salary" jdbcType="INTEGER" property="pmsUserSalary" />
        <result column="pms_user_intervtime" jdbcType="VARCHAR" property="pmsUserIntervtime" />
        <result column="pms_user_notekeeper" jdbcType="VARCHAR" property="pmsUserNotekeeper" />
        <result column="pms_user_imgsurl" jdbcType="VARCHAR" property="pmsUserImgsurl" />
        <result column="pms_user_uptime" jdbcType="VARCHAR" property="pmsUserUptime" />
        <result column="pms_user_stationid" jdbcType="BIGINT" property="pmsUserStationid" />
        <result column="pms_user_level" jdbcType="INTEGER" property="pmsUserLevel" />
        
        <association property="station" javaType="com.ming.pms.mbg.model.PmsStation">
          <id column="pms_station_id" jdbcType="BIGINT" property="pmsStationId" />
          <result column="pms_station_name" jdbcType="VARCHAR" property="pmsStationName" />
          <result column="pms_station_level" jdbcType="INTEGER" property="pmsStationLevel" />
          <result column="pms_station_createtime" jdbcType="VARCHAR" property="pmsStationCreatetime" />
        </association>
        
        <collection property="roleList" ofType="com.ming.pms.mbg.model.PmsRole">
          <id column="pms_role_id" property="pmsRoleId" />
          <result column="pms_role_name" property="pmsRoleName" />
          <result column="pms_role_status" property="pmsRoleStatus" />
          <result column="pms_role_createtime" property="pmsRoleCreatetime" />
        </collection>
        
        <collection property="passreasonList" ofType="com.ming.pms.mbg.model.PmsPassreason">
          <result column="pms_passreason_id" jdbcType="BIGINT" property="pmsPassreasonId" />
          <result column="pms_passreason_name" jdbcType="VARCHAR" property="pmsPassreasonName" />
        </collection>
      </resultMap>
    
      <select id="getAllUserAndPassreasonAndRoleList" resultMap="DeepUserMap">
        select *
        from
        pms_user u
        left join user_role_relation ur on u.pms_user_id = ur.pms_user_id
        left JOIN pms_role r on ur.pms_role_id = r.pms_role_id
        LEFT JOIN user_passreason_relation up on u.pms_user_id = up.pms_user_id
        left join pms_passreason p on up.pms_passreason_id = p.pms_passreason_id
      </select>

## 问题：查询出来的每个用户只显示一条角色和未入职原因数据 ##

## 原因： ##

## 注意PmsUser“自添加属性”roleList和passreasonList ##

## 与之对应的是PmsUserMapper映射文件“自添加ResultMap”中collection标签中的property属性 ##