mybatis的association和collection

mybatis

3个结果类

@Data
public class SysUserVo implements Serializable {

    private static final long serialVersionUID = 1L;
    
    private Long userId;
    private Long deptId;
    private String userName;
    private String nickName;
    private String email;
    private String phonenumber;
    private String sex;
    private String avatar;
    private String createBy;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;
    private String updateBy;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date updateTime;
    private SysDeptVo deptVo;
    private List<SysRoleVo> roleVos;
}

@Data
public class SysDeptVo implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long deptId;
    private Long parentId;
    private String ancestors;
    private String deptName;
    private Integer orderNum;
    private String leader;
    private String phone;
    private String email;
    private String createBy;
    private Date createTime;
    private String updateBy;
    private Date updateTime;
}

@Data
public class SysRoleVo implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long roleId;
    private String roleName;
    private String roleKey;
    private Integer roleSort;
    private String dataScope;
    private boolean menuCheckStrictly;
    private boolean deptCheckStrictly;
    private String createBy;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;
    private String updateBy;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date updateTime;
}

Mapper.java

public interface SysUserMapper {
    
    /**
     * 单结果
     */
    SysUserVo queryInfo(@Param("userId") Long userId);

     /**
     * 多结果
     */
    List<SysUserVo> queryAll();
    
}

Mapper.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.iyy.system.mapper.SysUserMapper">

    <select id="queryAll" resultMap="SysUserVoResultMap">

        SELECT su.user_id,
               su.dept_id,
               su.user_name,
               su.nick_name,
               su.email,
               su.phonenumber,
               su.sex,
               su.avatar,
               su.create_by,
               su.create_time,
               su.update_by,
               su.update_time,
               sd.parent_id,
               sd.ancestors,
               sd.dept_name,
               sd.order_num,
               sd.leader,
               sd.phone,
               sd.email       AS dept_email,
               sd.create_by   AS dept_create_by,
               sd.create_time AS dept_create_time,
               sd.update_by   AS dept_update_by,
               sd.update_time AS dept_update_time,
               sr.role_id,
               sr.role_name,
               sr.role_key,
               sr.role_sort,
               sr.data_scope,
               sr.menu_check_strictly,
               sr.dept_check_strictly,
               sr.create_by   AS role_create_by,
               sr.create_time AS role_create_time,
               sr.update_by   AS role_update_by,
               sr.update_time AS role_update_time
        FROM sys_user su
                 LEFT JOIN sys_dept sd ON su.dept_id = sd.dept_id
                 LEFT JOIN sys_user_role sur ON su.user_id = sur.user_id
                 LEFT JOIN sys_role sr ON sur.role_id = sr.role_id

    </select>

    <select id="queryInfo" resultMap="SysUserVoResultMap">

        SELECT su.user_id,
               su.dept_id,
               su.user_name,
               su.nick_name,
               su.email,
               su.phonenumber,
               su.sex,
               su.avatar,
               su.create_by,
               su.create_time,
               su.update_by,
               su.update_time,
               sd.parent_id,
               sd.ancestors,
               sd.dept_name,
               sd.order_num,
               sd.leader,
               sd.phone,
               sd.email       AS dept_email,
               sd.create_by   AS dept_create_by,
               sd.create_time AS dept_create_time,
               sd.update_by   AS dept_update_by,
               sd.update_time AS dept_update_time,
               sr.role_id,
               sr.role_name,
               sr.role_key,
               sr.role_sort,
               sr.data_scope,
               sr.menu_check_strictly,
               sr.dept_check_strictly,
               sr.create_by   AS role_create_by,
               sr.create_time AS role_create_time,
               sr.update_by   AS role_update_by,
               sr.update_time AS role_update_time
        FROM sys_user su
                 LEFT JOIN sys_dept sd ON su.dept_id = sd.dept_id
                 LEFT JOIN sys_user_role sur ON su.user_id = sur.user_id
                 LEFT JOIN sys_role sr ON sur.role_id = sr.role_id
        WHERE su.user_id = #{userId}

    </select>

    <resultMap id="SysUserVoResultMap" type="SysUserVo">
        <id property="userId" column="user_id"/>
        <result property="deptId" column="dept_id"/>
        <result property="userName" column="user_name"/>
        <result property="nickName" column="nick_name"/>
        <result property="email" column="email"/>
        <result property="phonenumber" column="phonenumber"/>
        <result property="sex" column="sex"/>
        <result property="avatar" column="avatar"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <association property="deptVo" javaType="SysDeptVo" resultMap="SysDeptVoResultMap"/>
        <collection property="roleVos" javaType="java.util.List" resultMap="SysRoleVoResultMap"/>
    </resultMap>

    <resultMap id="SysDeptVoResultMap" type="SysDeptVo">
        <id property="deptId" column="dept_id"/>
        <result property="parentId" column="parent_id"/>
        <result property="ancestors" column="ancestors"/>
        <result property="deptName" column="dept_name"/>
        <result property="orderNum" column="order_num"/>
        <result property="leader" column="leader"/>
        <result property="phone" column="phone"/>
        <result property="email" column="dept_email"/>
        <result property="createBy" column="dept_create_by"/>
        <result property="createTime" column="dept_create_time"/>
        <result property="updateBy" column="dept_update_by"/>
        <result property="updateTime" column="dept_update_time"/>
    </resultMap>

    <resultMap id="SysRoleVoResultMap" type="SysRoleVo">
        <id property="roleId" column="role_id"/>
        <result property="roleName" column="role_name"/>
        <result property="roleKey" column="role_key"/>
        <result property="roleSort" column="role_sort"/>
        <result property="dataScope" column="data_scope"/>
        <result property="menuCheckStrictly" column="menu_check_strictly"/>
        <result property="deptCheckStrictly" column="dept_check_strictly"/>
        <result property="createBy" column="role_create_by"/>
        <result property="createTime" column="role_create_time"/>
        <result property="updateBy" column="role_update_by"/>
        <result property="updateTime" column="role_update_time"/>
    </resultMap>

</mapper> 

关键①association和collection标签

    <association property="deptVo" javaType="SysDeptVo" resultMap="SysDeptVoResultMap"/>
    <collection property="roleVos" javaType="java.util.List" resultMap="SysRoleVoResultMap"/>

关键②resultMap的id标签不能少

        <id property="userId" column="user_id"/>
        <id property="deptId" column="dept_id"/>
        <id property="roleId" column="role_id"/>

测试

单结果

• sql执行结果

• java映射结果

多结果

• sql执行结果

• java映射结果

mybatis plus

简化Mapper.xml ①

    <resultMap id="SysUserVoResultMap" type="SysUserVo" autoMapping="true">
        <id property="userId" column="user_id"/>
        <association property="deptVo" javaType="SysDeptVo" resultMap="SysDeptVoResultMap"/>
        <collection property="roleVos" javaType="java.util.List" resultMap="SysRoleVoResultMap"/>
    </resultMap>

    <resultMap id="SysDeptVoResultMap" type="SysDeptVo" autoMapping="true">
        <id property="deptId" column="dept_id"/>
        <result property="email" column="dept_email"/>
        <result property="createBy" column="dept_create_by"/>
        <result property="createTime" column="dept_create_time"/>
        <result property="updateBy" column="dept_update_by"/>
        <result property="updateTime" column="dept_update_time"/>
    </resultMap>

    <resultMap id="SysRoleVoResultMap" type="SysRoleVo" autoMapping="true">
        <id property="roleId" column="role_id"/>
        <result property="createBy" column="role_create_by"/>
        <result property="createTime" column="role_create_time"/>
        <result property="updateBy" column="role_update_by"/>
        <result property="updateTime" column="role_update_time"/>
    </resultMap>

简化Mapper.xml ②

    <resultMap id="SysUserVoResultMap" type="SysUserVo" autoMapping="true">
        <id property="userId" column="user_id"/>
        <association property="deptVo" javaType="SysDeptVo" autoMapping="true">
            <id property="deptId" column="dept_id"/>
            <result property="email" column="dept_email"/>
            <result property="createBy" column="dept_create_by"/>
            <result property="createTime" column="dept_create_time"/>
            <result property="updateBy" column="dept_update_by"/>
            <result property="updateTime" column="dept_update_time"/>
        </association>
        <collection property="roleVos" ofType="SysRoleVo" autoMapping="true">
            <id property="roleId" column="role_id"/>
            <result property="createBy" column="role_create_by"/>
            <result property="createTime" column="role_create_time"/>
            <result property="updateBy" column="role_update_by"/>
            <result property="updateTime" column="role_update_time"/>
        </collection>
    </resultMap>