MyBatis查询结果映射

查询结果映射

所用的数据结构如下

public class User {
    private int id;
    private String username;
    private String address;
    private List<Book> books;
    ......
}

public class Book {
    private int id;
    private String name;
    private Author author; 
    ......
}

public class Author {
    private int id;
    private String name;
    private int age;
    ......
}

user(id, name, address)
book(id, name, aid)
author(id, name, age)
user_book(id, uid, bid) 

一对一

例如：每本书都有一个作者，作者都有自己的属性。

定义一个查询Book的方法，希望同时可以查出其作者的信息，实现方式如下

 Book getBookById(int id);

<resultMap id="BaseResultMap" type="com.xgsama.mybatis.model.Book">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
</resultMap>

<resultMap id="BookWithAuthor" type="com.xgsama.mybatis.model.Book" extends="BaseResultMap">
    <association property="author" javaType="com.xgsama.mybatis.model.Author">
        <id column="aid" property="id"/>
        <result column="aname" property="name"/>
        <result column="aage" property="age"/>
    </association>
</resultMap>

<select id="getBookById" resultMap="BookWithAuthor">
    SELECT b.*, a.`age` AS aage, a.`id` AS aid, a.`name` AS aname
    FROM book b,
         author a
    WHERE b.`aid` = a.`id`
      AND b.`id` = #{id}
</select>
 

以上这种加载方式，是一次性的读取到所有数据。然后在 resultMap 中做映射。如果一对一的属性使用不是很频繁，可能偶尔用一下，这种情况下，我们也可以启用懒加载。

懒加载，就是先查询 book，查询 book 的过程中，不去查询 author，当用户第一次调用了 book 中的 author 属性后，再去查询 author。实现如下

Book getBookById2(int id);
Author getAuthorById(int id);

<resultMap id="BookWithAuthor2" type="com.xgsama.mybatis.model.Book" extends="BaseResultMap">
    <!--定义association的时候，不直接指定映射的字段，而是指定要执行的方法-->
    <!--通过select字段来指定执行的方法,column表示执行方法时传递的参数字段,最后的fetchType表示开启懒加载-->
    <association property="author" javaType="com.xgsama.mybatis.model.Author"
                 select="com.xgsama.mybatis.mapper.BookMapper.getAuthorById" column="aid" fetchType="lazy"/>
</resultMap>

<select id="getBookById2" resultMap="BookWithAuthor2">
    select *
    from book
    where id = #{id};
</select>

<select id="getAuthorById" resultType="com.xgsama.mybatis.model.Author">
    select *
    from author
    where id = #{id};
</select> 

懒加载需要在全局配置中开启
<settings>
<setting name=“lazyLoadingEnabled” value=“true”/>
<setting name=“aggressiveLazyLoading” value=“false”/>
</settings>

一对多

例如：一个人可能有多本书，每本书有自己的属性

定义一个查询User的方法，希望同时可以查出其拥有的Book信息，实现方式如下

User getUserById(int id);
Book getBookByUId(int uid); // 用于实现懒加载

在 resultMap 中，通过 collection 节点来描述集合的映射关系。在映射时，会自动将一的一方数据集合并，然后将多的一方放到集合中，能实现这一点，靠的就是 id 属性。

<resultMap id="UserWithBook" type="com.xgsama.mybatis.model.User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="address" property="address"/>
    <collection property="books"
                select="com.xgsama.mybatis.mapper.BookMapper.getBookByUId"
                column="id" fetchType="lazy">
    </collection>
</resultMap>

<select id="getUserById" resultMap="UserWithBook">
    select *
    from user
    where id = #{id}
</select>

<select id="getBookByUId" resultMap="BookWithAuthor2">
    select b.id as bid, b.name, b.aid
    from book b,
         user_book ub
    where b.id = ub.bid
      and uid = #{uid}
</select> 

以上完整xml如下

<resultMap id="BaseResultMap" type="com.xgsama.mybatis.model.Book">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
</resultMap>
<resultMap id="BookWithAuthor" type="com.xgsama.mybatis.model.Book" extends="BaseResultMap">
    <association property="author" javaType="com.xgsama.mybatis.model.Author">
        <id column="aid" property="id"/>
        <result column="aname" property="name"/>
        <result column="aage" property="age"/>
    </association>
</resultMap>

<resultMap id="BookWithAuthor2" type="com.xgsama.mybatis.model.Book" extends="BaseResultMap">
    <!--通过select字段来指定执行的方法,column 表示执行方法时传递的参数字段,最后的fetchType表示开启懒加载-->
    <association property="author" javaType="com.xgsama.mybatis.model.Author"
                 select="com.xgsama.mybatis.mapper.BookMapper.getAuthorById" column="aid" fetchType="lazy"/>
</resultMap>

<resultMap id="UserWithBook" type="com.xgsama.mybatis.model.User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="address" property="address"/>
    <collection property="books"
                select="com.xgsama.mybatis.mapper.BookMapper.getBookByUId"
                column="id" fetchType="lazy">
    </collection>
</resultMap>


<select id="getBookById" resultMap="BookWithAuthor">
    SELECT b.*, a.`age` AS aage, a.`id` AS aid, a.`name` AS aname
    FROM book b,
         author a
    WHERE b.`aid` = a.`id`
      AND b.`id` = #{id}
</select>

<select id="getBookById2" resultMap="BookWithAuthor2">
    select *
    from book
    where id = #{id};
</select>

<select id="getAuthorById" resultType="com.xgsama.mybatis.model.Author">
    select *
    from author
    where id = #{id};
</select>

<select id="getUserById" resultMap="UserWithBook">
    select *
    from user
    where id = #{id}
</select>

<select id="getBookByUId" resultMap="BookWithAuthor2">
    select b.id as bid, b.name, b.aid
    from book b,
         user_book ub
    where b.id = ub.bid
      and uid = #{uid}
</select>