已解决:Mybatis中一对多(多对多部分)查询数据只有一条

本文介绍了一个复杂的数据库查询案例,展示了如何使用MyBatis的ResultMap和collection标签来处理多对多关系的数据,如用户与角色、用户与未入职原因之间的关联。通过详细解释SQL语句和Java类的设计,为开发者提供了处理复杂数据关系的有效方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

表关系

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

关联表:用户角色表(多对多)、用户未入职原因表(多对多)

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属性

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值