表关系
主表:角色——用户——未入职原因表
关联表:用户角色表(多对多)、用户未入职原因表(多对多)
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>