MyBatis高级映射

﹏ヽ暗。殇╰゛Y 2023-08-17 17:06 115阅读 0赞

1 一对一映射  
1)实体类![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L211c2lfbQ_size_16_color_FFFFFF_t_70]  
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的另一种写法

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

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简化写法:

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>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L211c2lfbQ_size_16_color_FFFFFF_t_70]: /images/20230808/f7b85a19bd7346cfae9cd8605d7bee39.png