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

最新推荐文章于 2023-10-17 23:08:58 发布

原创最新推荐文章于 2023-10-17 23:08:58 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

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