MyBatis高级映射

1 一对一映射
1)实体类
2)mapper配置文件

<select id="selectUserAndRoleById" resultType="com.musi.bean.SysUser">
        select
          u.id,
          u.user_name userName,
          u.passward,
          u.email email,
          r.id "sysRole.id",
          r.role_name "sysRole.roleName",
          r.create_by "sysRole.createBy"
        from sys_user u
        inner join sys_user_role ur on u.id=ur.user_id
        inner join sys_role on ur.role_id=r.id
        where u.id=#{id}
    </select>

另一种写法

	<resultMap type="com.musi.bean.SysUser" id="userRoleMap">
        <result property="id" column="id" />
        <result property="userName" column="user_name" />
        <result property="password" column="password" />
        <result property="email" column="email" />
        <result property="sysRole.id" column="role_id" />
        <result property="sysRole.roleName" column="role_name" />
        <result property="sysRole.createBy" column="create_by" />
    </resultMap>
    <select id="selectUserAndRoleById2" resultMap="userRoleMap">
        select
          u.id,
          u.user_name,
          u.passward,
          u.email,
          r.id role_id,
          r.role_name,
          r.create_by
        from sys_user u
        inner join sys_user_role ur on u.id=ur.user_id
        inner join sys_role on ur.role_id=r.id
        where u.id=#{id}
    </select>

resultMap的另一种写法

	<resultMap type="com.musi.bean.User" id="userMap">
        <result property="id" column="id" />
        <result property="userName" column="user_name" />
        <result property="password" column="password" />
        <result property="email" column="email" />
    </resultMap>
    <resultMap type="com.musi.bean.SysUser" id="userRoleMap" extends="userMap">
        <result property="sysRole.id" column="role_id" />
        <result property="sysRole.roleName" column="role_name" />
        <result property="sysRole.createBy" column="create_by" />
    </resultMap>

3)使用resultMap的association标签配置一对一映射

<resultMap id="userRoleMap" extends="userMap" type="com.musi.bean.SysUser">
        <association property="role" columnPrefix="role_" javaType="com.musi.bean.SysRole">
            <result property="id" column="id"/>
            <result property="roleName" column="role_name"/>
            <result property="createBy" column="create_by"/>
        </association>
    </resultMap>

association 包含有以下属性:
property:对应实体类中的属性名
javaType:属性对应的Java类型
resultMap:可以直接使用现有的resultMap,而不用在这里配置
columnPrefix:查询列的前缀,配置前缀后,在子标签配置的result的column时前缀可以省略

对应的select语句如下:

<select id="selectUserAndRoleById2" resultMap="userRoleMap">
        select
          u.id,
          u.user_name,
          u.passward,
          u.email,
          r.id role_id,
          r.role_role_name,
          r.role_create_by
        from sys_user u
        inner join sys_user_role ur on u.id=ur.user_id
        inner join sys_role on ur.role_id=r.id
        where u.id=#{id}
    </select>

注意:和sysrole相关列的别名,都已经改成了"role_"前缀,特别注意role_name前也需要加前缀

使用assocation配置时还可以使用resultMap属性配置成一个已经存在的resultMap映射,代码如下:

	<resultMap id="roleMap" type="com.musi.bean.SysRole">
        <id property="id" column="id" />
        <result property="roleName" column="role_name"/>
        <result property="create_by" column="createBy"/>
    </resultMap>
    <resultMap id="userRoleMap" extends="userMap" type="com.musi.bean.SysUser">
        <association property="role" columnPrefix="role_" resultMap="roleMap"/>
    </resultMap>
	<select id="selectUserAndRoleById2" resultMap="userRoleMap">
        select
          u.id,
          u.user_name,
          u.passward,
          u.email,
          r.id role_id,
          r.role_role_name,
          r.role_create_by
        from sys_user u
        inner join sys_user_role ur on u.id=ur.user_id
        inner join sys_role on ur.role_id=r.id
        where u.id=#{id}
    </select>

4)association标签的嵌套查询
首先在UserMapper,xml中加入如下代码:

<resultMap id="userRoleMapSelect" extends="userMap" type="com.musi.bean.SysUser">
        <association property="role" column="{id=role_id}" select="com.musi.mapper.RoleMapper.selectRoleById"/>
    </resultMap>

    <select id="selectUserAndRoleByIdSelect" resultMap="userRoleMapSelect">
      select
        u.id,
        u.user_name,
        u.passward,
        u.email,
        ur.role_id
      from sys_user u
      inner join sys_user_role ur on u.id=uir.user_id
      where u.id = #{id}
    </select>

在RoleMapper.xml中加入如下代码:

<select id="selecrtRoleById" resultMap="roleMap">
	select * from sys_role where id="#{id}"
</select>

备注:可用的参数使用通过上面的column="{id=role_id}“来配置的,因此在嵌套的sql中只能使用#{id}参数,当需要多个参数时,可以配置多个,使用逗号隔开即可.如:column=”{id=role_id,name=role_name}".

2 一对多映射
1)JavaBean类

public class SysUser implements Serializable{

    private String id;

    private String userName;

    private String passward;

    private String email;

    private List<SysRole> roleList;
    ....

2)mapp.xml配置

<resultMap id="userRoleListMap" type="com.musi.bean.SysUser">
        <id property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="passward" column="passward"/>
        <result property="email" column="email"/>
        <collection property="roleList" columnPrefix="role_" javaType="com.musi.bean.SysRole">
            <id property="id" column="id"/>
            <result property="roleName" column="role_name"/>
            <result property="createBy" column="create_by"/>
        </collection>
    </resultMap>
	<select id="selectAllUserAndRoles" resultMap="userRoleListMap">
        select
          u.id,
          u.user_name,
          u.passward,
          u.email,
          r.id role_id,
          r.role_name role_role_name,
          r.create_by role_create_by
        from sys_user
        inner join sys_user_role ur on u.id = ur.user_id
        inner join sys_role r on ur.role_id = r.id
    </select>

resultMap简化写法:

<resultMap id="userRoleListMap" extends="userMap" type="com.musi.bean.SysUser">
        <collection property="roleList" columnPrefix="role_" resultMap="com.musi.mapper.RoleMapper.roleMap"/>
    </resultMap>

3)collection集合的嵌套查询
首先在PrivilegeMapp.xml中添加如下方法:

<select id="selectPrivilegeByRoleId" resultMap="privilegeMap">
        select p.* from sys_privilege p
        inner join sys_role_privilege rp on rp.privilege_id = p.id
        where role_id = #{roleId}
    </select>

在RoleMapper.xml中配置映射和对应的查询方法,代码如下:

	<resultMap id="rolePrivilegeListMapSelect" extends="roleMap" type="com.musi.bean.SysRole">
        <collection property="privilegeList" fetchType="lazy" column="{roleId=id}" select="com.musi.mapper.PrivilegeMapper.selectPrivilegeByRoleId"/>
    </resultMap>
    <select id="selectRoleByUserId" resultMap="rolePrivilegeListMapSelect">
        select
          r.id,
          r.role_name,
          r.create_by
        from sys_role
        inner join sys_user_role ur on ur.role_id  = r.id
        where ur.user_id = #{userId}
    </select>

在UserMapper.xml中添加如下映射和查询

	<select id="selectAllUserAndRolesSelect" resultMap="userRoleLIstMapSelect">
      select
        u.id,
        u.user_name,
        u.passward,
        u.email
      from sys_user u
      where u.id=#{id}
    </select>