MyBatis学习笔记（五）关联查询

场景：
一个博客系统中，用户（author）可以任意发表博文（blog），用户还可以对博文进行评论（comment）。
数据库中三张表：blog，author，comment。comment使用外键关联blog（多对一），blog使用外键关联author（一对一，假定作者只写了一篇文章）
sql脚本如下：

create table author (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

create table blog (
  id INT PRIMARY KEY AUTO_INCREMENT,
  authorId INT,
  title VARCHAR(100),
  createTime TIMESTAMP,
  content TEXT,
  FOREIGN KEY (authorId) REFERENCES author(id)
);

create table comment (
  id INT PRIMARY KEY AUTO_INCREMENT,
  blogId INT,
  content TEXT,
  createTime TIMESTAMP,
  FOREIGN KEY (blogId) REFERENCES blog(id)
);

INSERT INTO author(name) VALUES('A1');
INSERT INTO author(name) VALUES('A2');

INSERT INTO blog(authorId, title, createTime, content) VALUES('1', '第一篇', '20151202', '第一篇的内容');
INSERT INTO blog(authorId, title, createTime, content) VALUES('2', '第二篇', '20151203', 'A2写的第二篇的内容');

INSERT INTO comment(blogId, content, createTime) VALUES(1, '第一篇的评论', '20151203');
INSERT INTO comment(blogId, content, createTime) VALUES(2, '第二篇的评论', '20151204');

实体类如下：

public class Author {
    private int id;
    private String name;
    // getters and setters
}
public class Comment {
    private int id;
    private int blogId;
    private String content;
    private Timestamp timestamp;
    // getters and setters
}
public class Blog {
    private int id;
    private String title;
    private Timestamp createTime;
    private String content;

    // 维护博文与作者的1：1关系
    private Author authorId;

    // 维护博文与评论的1：n关系
    private List<Comment> comments;
}

sql映射文件BlogMapper如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mapping.BlogMapper">

    <!-- 方法一：嵌套查询-->
    <select id="getBlogById" parameterType="int" resultMap="BlogResult">
        select id,authorId,title,createTime,content from blog where id=#{id}
    </select>

    <resultMap type="Blog" id="BlogResult">
        <id property="id" column="id"/>
        <result property="title" column="title"/>
        <result property="createTime" column="createTime"/>
        <result property="content" column="content"/>
        <!-- 这个column就是查询author表时提供的参数，嵌套select -->
        <association property="author" column="authorId" javaType="Author" select="getAuthorById"></association>
        <!-- ofType指定集合中的对象类型 --> 
        <collection property="comments" column="id" javaType="List" ofType="Comment" select="getCommentsById"></collection>
    </resultMap>

    <select id="getAuthorById" parameterType="int" resultType="Author">
        select id, name from author where id =#{id}
    </select>

    <select id="getCommentsById" parameterType="int" resultType="Comment">
        select id, blogId, content, createTime from comment where blogId=#{id}
  </select>

  <!-- 方法二:等值连接查询 or join语句 来嵌套结果-->
  <resultMap type="Blog" id="BlogResult2">
        <id property="id" column="id"/>
        <result property="title" column="title"/>
        <result property="createTime" column="createTime"/>
        <result property="content" column="content"/>
        <!-- 这个column就是查询author表提供的参数 -->
        <association property="author" column="authorId" javaType="Author">
            <id property="id" column="authorId"/>
            <result property="name" column="name"/>
        </association>
        <!-- ofType指定集合中的对象类型 --> 
        <collection property="comments" column="id" javaType="List" ofType="Comment">
            <id property="id" column="commentId"/>
            <result property="content" column="commentContent"/>
            <result property="createTime" column="commentCreateTime"/>
        </collection>
    </resultMap>

  <select id="getBlogById2" parameterType="int" resultMap="BlogResult2">
  select A.id, A.title, A.createTime, A.content,
         B.id as authorId, B.name,
         C.id as commentId, C.content as commentContent, C.createTime as commentCreateTime
         from blog as A, author as B, comment as C
         where A.authorId = B.id and A.id = C.blogId and A.id = #{id}
  </select>

  <select id="getBlogs" resultMap="BlogResult">
  select A.id, A.title, A.createTime, A.content,
         B.id as authorId, B.name,
         C.id as commentId, C.content as commentContent, C.createTime as commentCreateTime
         from blog as A, author as B, comment as C
         where A.authorId = B.id and A.id = C.blogId
  </select>
</mapper>

方法一：嵌套查询
需要执行多个sql语句，如本例中，先执行select * from blog where id = #{id}，再通过得出的authorId查询author，还要执行select * from comment where blogId = #{id}
该方式：简单，对于大型数据集合效率低，查询慢，如getBlogs()方法，如果blog数目为n，那么执行sql的条数应该为1+n+n，也就是所谓的“N+1 查询问题”。
执行了一个单独的 SQL 语句来获取结果列表（就是“+1”）。
对返回的每条记录，你执行了一个查询语句来为每个加载细节（就是“N”）。

方法二：等值连接查询 or join语句 来嵌套结果
可以解决上面存在的问题

测试代码：

public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        String stmt = "com.mapping.BlogMapper.getBlogById";
        Blog blog = sqlSession.selectOne(stmt, 1);
        System.out.println(blog.getId() + " " + blog.getTitle() + " " + blog.getAuthor().getName()
                + blog.getCreateTime() + " " + blog.getComments().get(0).getContent());
        MyBatisUtil.closeSession(sqlSession);
    }