mybatis高级查询（多表的一个查询）

实体类：

@Data
public class SysUser {
    private Long id;
    private String userName;
    private String userPassword;
    private String userEmail;
    private String userInfo;
    private byte[] headImg;
    private Date createTime;
    //一对一映射
//    private SysRole role;
    //一对多的映射
    private List<SysRole> roleList;
}

@Data
public class SysRole implements Serializable {
    private Long id;
    private String roleName;
    private int enabled;
    private Long createBy;
    private Date createTime;

}

一对一映射

使用自动映射也就是起别名的方式
使用resultMap配置一对一映射

SysUserRepository.xml文件中

<resultMap id="sysUserMap" type="com.example.model.SysUser">
        <id property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="userPassword" column="user_password"/>
        <result property="userEmail" column="user_email"/>
        <result property="userInfo" column="user_info"/>
        <result property="headImg" column="head_img" jdbcType="BLOB"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>

SysRoleRepository.xml文件

<resultMap id="roleMap" type="SysRole">
        <id property="id" column="id"/>
        <result property="roleName" column="role_name"/>
        <result property="enabled" column="enabled"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>


 
 
<!--一对一映射-->
    <resultMap id="userRoleMap" extends="sysUserMap" type="SysUser">
        <result property="role.id" column="id"/>
        <result property="role.roleName" column="role_name"/>
        <result property="role.enabled" column="enabled"/>
        <result property="role.createBy" column="create_by"/>
        <result property="role.createTime" column="create_time" jdbcType="TIMESTAMP"/>
    </resultMap>
    <select id="selectUserAndRole" resultMap="userRoleMap">
        select u.id ,u.user_name,u.user_password,u.user_email,u.user_info,u.head_img,u.create_time,
        r.id,r.role_name,r.enabled,r.create_by,r.create_time
         from sys_user u inner join sys_user_role ur on ur.user_id = u.id
        inner join sys_role r on r.id = ur.role_id where u.id = #{id}
    </select>

一般使用resultMap的association标签

<resultMap id="userRoleMap" extends="sysUserMap" type="SysUser">
   <association property="role" columnPrefix="role_" javaType="SysRole"> //加了前缀role相关的别名也要加上相应的前缀
        <result property="role.id" column="id"/>
        <result property="role.roleName" column="role_name"/>
        <result property="role.enabled" column="enabled"/>
        <result property="role.createBy" column="create_by"/>
        <result property="role.createTime" column="create_time" jdbcType="TIMESTAMP"/>
  </association>
</resultMap>
或者可以这样子
<resultMap id="userRoleMap" extends="sysUserMap" type="SysUser">
   <association property="role" columnPrefix="role_" resultMap="com.example.repository.SysRoleRepository.roleMap"/> //加了前缀role相关的别名也要加上相应的前缀
</resultMap>
    <select id="selectUserAndRole" resultMap="userRoleMap">
        select u.id ,u.user_name,u.user_password,u.user_email,u.user_info,u.head_img,u.create_time,
        r.id roel_id,r.role_name role_role_name,r.enabled role_enabled,r.create_by role_create_by,r.create_time role_create_time
         from sys_user u inner join sys_user_role ur on ur.user_id = u.id
        inner join sys_role r on r.id = ur.role_id where u.id = #{id}
    </select>

一对多查询

一级嵌套

<!--一对多关系(前缀一定得加上防止，id值重复)-->
    <resultMap id="allUserRoleMap" extends="sysUserMap" type="SysUser">
        <collection property="roleList" columnPrefix="role_" resultMap="com.example.repository.SysRoleRepository.roleMap"/>
    </resultMap>
    <select id="selectAllUserAndRole" resultMap="allUserRoleMap">
        select u.id ,u.user_name,u.user_password,u.user_email,u.user_info,u.head_img,u.create_time,
        r.id role_id,r.role_name role_role_name,r.enabled role_enabled,r.create_by role_create_by,r.create_time role_create_time
         from sys_user u
         inner join sys_user_role ur on u.id = ur.user_id
        inner join sys_role r on ur.role_id =r.id
    </select>

多级嵌套

SysPrivilegeRepository.xml文件
 <resultMap id="privilegeMap" type="SysPrivilege">
        <id property="id" column="id"/>
        <result property="privilegeName" column="privilege_name"/>
        <result property="privilegeUrl" column="privilege_url"/>
 </resultMap>

SysRoleRepository.xml文件

    <resultMap id="rolePrivilegeMap" extends="roleMap" type="SysRole">
        <collection property="privilegeList" columnPrefix="privilege_" resultMap="com.example.repository.SysPrivilegeRepository.privilegeMap"/>
    </resultMap>

SysUserRepository.xml文件中

<!--一对多关系，多级嵌套-->
    <resultMap id="userRolePrivilegeMap" extends="sysUserMap" type="SysUser">
        <collection property="roleList" columnPrefix="role_" resultMap="com.example.repository.SysRoleRepository.rolePrivilegeMap"/>
    </resultMap>
    <select id="selectAllUserAndRoleAndPrivilege" resultMap="userRolePrivilegeMap">
        select u.id ,u.user_name,u.user_password,u.user_email,u.user_info,u.head_img,u.create_time,
        r.id role_id,r.role_name role_role_name,r.enabled role_enabled,r.create_by role_create_by,r.create_time role_create_time,
         p.id role_privilege_id,p.privilege_name role_privilege_privilege_name,p.privilege_url role_privilege_privilege_url
        from sys_user u inner join sys_user_role ur on u.id = ur.user_id
        inner join sys_role r on ur.role_id =r.id
        inner join sys_role_privilege rp on r.id = rp.role_id
        inner join sys_privilege p on p.id = rp.privilege_id
    </select>