Mybatis入门系列之（十一）-- 一对一查询

一对一查询

第一种解决方案

假定一个用户对应一个角色：在SysUser中添加一个如下的字段：

    private SysRole role;
    public SysRole getRole() {
        return role;
    }

    public void setRole(SysRole role) {
        this.role = role;
    }

在UserMapper接口中添加对应的方法：

<select id="selectUserAndRoleById" resultType="tk.mybatis.simple.model.SysUser">
    SELECT
      u.id,
      u.user_name "userName",
      u.user_password "userPassword",
      u.user_info "userInfo",
      u.user_email "userEmail",
      u.head_img "headImg",
      u.create_time "createTime",
      r.id "role.id",
      r.role_name "role.roleName",
      r.enabled "role.enabled",
      r.create_by "role.createBy",
      r.create_time "role.createTime"
    FROM  sys_user u
    INNER JOIN sys_user_role ur on u.id = ur.user_id
    INNER JOIN sys_role r on r.id  = ur.role_id
    where u.id = #{id}
</select>

测试代码如下：

@Test
public void testSelectUserAndRoleById(){
    SqlSession sqlSession = getSqlSession();
    try {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        SysUser user = userMapper.selectUserAndRoleById(1L);

        Assert.assertNotNull(user);
        Assert.assertNotNull(user.getRole());

    }finally {
        sqlSession.close();
    }
}

---

第二种解决方案

使用resultMap配置一对一映射

在UserMapper.xml文件中写出如下配置：

<resultMap id="userRoleMap" type="tk.mybatis.simple.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="headImg" column="head_img" jdbcType="BLOB"/>
    <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
    <!--
        role的相关属性
    -->
    <result property="role.id" column="role_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="role_create_time" jdbcType="TIMESTAMP"/>
</resultMap>

<select id="selectUserAndRoleById" resultType="userRoleMap">
    SELECT
      u.id,
      u.user_name,
      u.user_password,
      u.user_info,
      u.user_email,
      u.head_img,
      u.create_time,
      r.id "role_id",
      r.role_name,
      r.enabled "enabled",
      r.create_by "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 r.id  = ur.role_id
    where u.id = #{id}
</select>

测试代码略去。。

mybatis支持resultMap继承。上述resultMap的代码可以修改为如下格式：

<resultMap id="userRoleMap" type="tk.mybatis.simple.model.SysUser" extends="userMap">
    <!--
        role的相关属性
    -->
    <result property="role.id" column="role_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="role_create_time" jdbcType="TIMESTAMP"/>
</resultMap>

其中userMap的代码格式为：

<resultMap id="userMap" type="tk.mybatis.simple.model.SysUser" autoMapping="false">
    <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="headImg" column="head_img" jdbcType="BLOB"/>
    <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>

---

第三种解决方案

使用resultMap的association修改上面配置的id为userRoleMap的resultMap的配置：

<resultMap id="userRoleMap" type="tk.mybatis.simple.model.SysUser" extends="userMap">
    <!--
        role的相关属性
    -->
   <!-- <result property="role.id" column="role_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="role_create_time" jdbcType="TIMESTAMP"/>-->
    <!--
        修改为association
    -->
    <association property="role" columnPrefix="role_"    javaType="tk.mybatis.simple.model.SysRole">
        <result property="id" column="id"/>
        <result property="roleName" column="role_name"/>
        <result property="enabled" column="enabled"/>
        <result property="createBy" column="create_by" jdbcType="BLOB"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
    </association>
</resultMap>

更好的解决方案是：定义一个SysRole的resultMap，实现方式如下：

在UserMapper.xml文件中定义如下resultMap：

<resultMap id="roleMap" type="tk.mybatis.simple.model.SysRole">
    <result 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>

在id为userRoleMap里面引用id为roleMap的映射，代码如下：

<resultMap id="userRoleMap" type="tk.mybatis.simple.model.SysUser" extends="userMap">
    <association property="role" columnPrefix="role_" resultMap="roleMap"/>
</resultMap>

注意：在association标签里面引用了roleMap

但是：

​ 需要注意的是，这里的roleMap是定义在UserMapper.xml文件中，更合适的位置应该是定义在RoleMapper.xml文件中。如果将roleMap定义到RoleMapper.xml中，那么userRoleMap里面该引用roleMap的时候，需要指定roleMap的namespace，代码如下：

<resultMap id="userRoleMap" type="tk.mybatis.simple.model.SysUser" extends="userMap">
    <association property="role" columnPrefix="role_"
                 resultMap="tk.mybatis.simple.mapper.RoleMapper.roleMap"/>
</resultMap>

---

第四种解决方案：association的嵌套查询

首先在RoleMapper.xml文件的内容如下：

<mapper namespace="tk.mybatis.simple.mapper.RoleMapper">
    <!--
        id为roleMap
    -->
    <resultMap id="roleMap" type="tk.mybatis.simple.model.SysRole">
        <result 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>
    <!-- id为selectRoleById  这里引用了roleMap -->
    <select id="selectRoleById" resultMap="roleMap">
        SELECT * FROM  sys_role where id = #{id}
    </select>
</mapper>

在UserMapper.xml文件中定义如下resultMap:

<!--
    这里继承了本文件的userMap
    在association标签里面指定了select标签为RoleMapper.xml文件里面的selectRoleById查询标签
    column：将主查询中列的结果作为嵌套查询的参数
-->
<resultMap id="userRoleMapSelect" extends="userMap"
           type="tk.mybatis.simple.model.SysUser">
    <association property="role" column="{id=role_id}"
                 select="tk.mybatis.simple.mapper.RoleMapper.selectRoleById"/>
</resultMap>

定义一个select标签：

<select id="selectUserAndRoleByIdSelect" resultMap="userRoleMapSelect">
    SELECT u.id,
            u.user_name,
            u.user_password,
            u.user_info,
            u.user_email,
            u.head_img,
            u.create_time,
            ur.role_id
    FROM sys_user u
    INNER JOIN sys_user_role ur on u.id = ur.user_id
    where u.id = #{id}
</select>

接口中增加方法：

/**
 * 根据用户id查询用户信息和用户的角色信息
 * @param id
 * @return
 */
SysUser selectUserAndRoleByIdSelect(Long id);

测试代码如下：

@Test
public void testSelectUserAndRoleByIdSelect(){
    SqlSession sqlSession = getSqlSession();
    try {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        SysUser user = userMapper.selectUserAndRoleByIdSelect(1001L);

        Assert.assertNotNull(user);
        Assert.assertNotNull(user.getRole());

    }finally {
        sqlSession.close();
    }
}

输出结果如下：

[DEBUG] 2018-04-29 21:24:05,566 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Preparing: SELECT u.id, u.user_name, u.user_password, u.user_info, u.user_email, u.head_img, u.create_time, ur.role_id FROM sys_user u INNER JOIN sys_user_role ur on u.id = ur.user_id where u.id = ?
[DEBUG] 2018-04-29 21:24:05,598 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Parameters: 1001(Long)
[TRACE] 2018-04-29 21:24:05,647 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165)
<== Columns: id, user_name, user_password, user_info, user_email, head_img, create_time, role_id
[TRACE] 2018-04-29 21:24:05,648 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165)
<== Row: 1001, test, 123456, <>, test@mybatis.tk, <>, 2018-04-24 17:08:34.0, 2
[DEBUG] 2018-04-29 21:24:05,661 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
====> Preparing: SELECT * FROM sys_role where id = ?
[DEBUG] 2018-04-29 21:24:05,661 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
====> Parameters: 2(Long)
[TRACE] 2018-04-29 21:24:05,696 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165)
<==== Columns: id, role_name, enabled, create_by, create_time
[TRACE] 2018-04-29 21:24:05,696 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165)
<==== Row: 2, 普通用户, 1, 1, 2018-04-24 15:08:34.0
[DEBUG] 2018-04-29 21:24:05,697 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
<==== Total: 1
[DEBUG] 2018-04-29 21:24:05,699 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
<== Total: 1

从日志分析查看得出：执行了两次查询，如果是一对多关系，则会执行N+1次查询。涉及到懒加载的问题。

如何实现懒加载呢？？修改userRoleMapSelect为如下代码，添加fetchType=”lazy”属性

<resultMap id="userRoleMapSelect" extends="userMap"
           type="tk.mybatis.simple.model.SysUser">

    <association property="role" column="{id=role_id}"
                 fetchType="lazy"
                 select="tk.mybatis.simple.mapper.RoleMapper.selectRoleById"/>
</resultMap>

如果以上的配置并不能实现懒加载，那么修改mybatis-config.xml

<settings>   
<!--        
    如果该参数为true，表示对任意延迟属性的调用会使带有延迟加载属性的对象完整加载，
    反之，每种属性都将按需加载。  
-->    
  <setting name="aggressiveLazyLoading" value="false"/>
</settings>

测试结果略去。。。