mybatis级联查询

关联有嵌套查询和嵌套结果两种方式，本文是按照 嵌套结果 这种方式来说明的

上一章介绍了多对一的关系，用到了，这是一个复杂类型的关联。我们选择一个示例来回顾下,比如：一个博客有一个用户，关联映射就工作于这种结果之上。首先看下，我们在本文中要用到的表结构字段:

博客
blog : id title author_id

作者
author: id username password email bio favourite_section

文章
post :id blog_id author_id created_on section subject draft body

评论
comment : id post_id name comment

标签
T : id name
我们把一个博客和一个用户关联在一起，就像：

<select id="selectBlog" parameterType="int" resultMap="blogResult">              select                   b.id as blog_id,                  b.title as blog_title,                  b.author_id as blog_author_id                  a.id as author_id,                  a.username as author_username,                  a.password as author_passowrd,                  a.email as auhtor_email,                  a.bio as author_bio               from  blog b left outer join author a on b.author_id=a.id                    where b.id=#{id}       </select>

注意这个联合查询，以及所有结果被唯一而清晰的名字而重命名。这使得映射非常简单。现在我们可以映射这个结果：

<resultMap id="blogResult" type="Blog">     <id property="id" column="blog_id"/>      <result property="title" column="blog_title"/>      <!-- 和一个用户关联,Blog 类里面属性时author,关联的列是原先的blog.author_id-->      <association property="author"  column="blog_author_id" javaType="Author"  resultMap="authorResult"/></resultMap><resultMap id="authorResult" type="Author">    <id property="id" column="author_id"/>    <result property="username" column="author_username"/>    <result property="password" column="author_password"/>    <result property="email" column="author_email"/>    <result property="bio" column="author_bio"/></resultMap>

在上面的例子中，你可以看到博客的作者关联authorResult 结果映射来加载作者实例。 上面的实例中，用了外部的结果映射元素来映射关联。这使得Author结果映射可以重用。然而，你不需要重用它的话，或者你仅仅引用你所有的结果映射到一个单独描述的结果映射中。你可以嵌套结果映射。这里给出使用这种方式的相同示例：

<resultMap id="blogResult" type="Blog">     <id property="id" column="blog_id"/>    <result property="title" column="blog_title"/>    <!-- 和一个用户关联,Blog 类里面属性时author,关联的列是原先的blog.author_id-->    <association property="author"  column="blog_author_id" javaType="Author" >            <id property="id" column="author_id"/>            <result property="username" column="author_username"/>            <result property="password" column="author_password"/>            <result property="email" column="author_email"/>            <result property="bio" column="author_bio"/>    </association></resultMap>

上面你已经看到了如何处理有一个类型的关联.但是“有很多个”是怎样的呢？，也就是集合类型， 本文的主要工作是来说这个的 。

集合

相对于关联来说，集合映射多了一个属性”ofType“.这个属性用来区分 JavaBean（或字段）属性类型 和 集合包含的类型 来说是很重要的. ，ofType用来表示集合包含类型。

<collection property="posts"  ofType="Post">            <id property="id" column="post_id"/>            <result property="subject" column="post_subject"/>            <result property="body" column="post_body"/>      </collection>

集合元素的作用和关联几乎是相同的。我们来继续上面的示例，一个博客只有一个作者。但是博客有很多文章，在博客类中，这可以由下面的写法来表示：
private List posts;
这一次联合博客表和文章表（一个blog_id可以对应很多的文章）SQL如下：

<select id="selectBlog" parameterType="int" resultMap="blogResult">         select              b.id as blog_id ,             b.title as blog_title,             b.author_id as blog_author_id,             p.id as post_id,             p.subject as post_subject,             p.body as post_body           from blog b               left outer join  post p on b.id=p.blog_id               where b.id=#{id}  </select>

现在用“文章映射集合”来映射 “博客“，可以简单写为：

<resultMap id="blogResult" type="Blog">    <id property="id" column="blog_id"/>    <result property="title" column="blog_title"/>    <collection property="posts" ofType="Post">        <id property="id" column="post_id"/>        <result property="subject" column="post_subject"/>        <result property="body" column="post_body"/>    </collection></resultMap>

高级关联和集合映射还有很多要琢磨的地方。就让面介绍的集合映射，稍微的整理下（一个博客下面，有很多文章）.

首先创建下，需要用到的表格，以及向其中插入一些数据.

create table author(id int (11) not null auto_increment,                    username varchar(20) not null,                    password varchar(20) not null,                     email varchar(20) not null,                    bio varchar(20) not null,                    favourite_section varchar(20) not null,                     primary key(id)                  )ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into author(id,username,password,email,bio,favourite_section) values(1001,'林枫','123456','[e-mail]','合肥的小男孩','旅游');create table blog (id int (11) not null auto_increment,                    title varchar(20) not null,                     author_id int(11) not null,                      primary key(id))                     ENGINE=InnoDB DEFAULT CHARSET=utf8;   insert into blog(id,title,author_id) values(1,'小说博客',1001);                    create table post(id int (11) not null auto_increment,                  blog_id int(11) not null,                  author_id int(11) not null,                  created_on date not null,                  section varchar(20) not null,                  subject varchar(20) not null,                  draft varchar(20) not null,                  body varchar(20) not null,                   primary key(id)                    )ENGINE=InnoDB DEFAULT CHARSET=utf8;       insert into post(id,blog_id,author_id,created_on,section,subject,draft,body)values(1,1,1001,now(),'旅游','玄幻','草稿','绝世武神');  insert into post(id,blog_id,author_id,created_on,section,subject,draft,body)values(2,1,1001,now(),'旅游','玄幻','草稿','大主宰');insert into post(id,blog_id,author_id,created_on,section,subject,draft,body)values(3,1,1001,now(),'旅游','玄幻','草稿','灵域');

在贴JAVA代码之前，先看下目录结构吧：

作者类 Author.javapackage com.mybatis.model;/** * 作者类 * @author Administrator * */public class Author {    private int id;    private String username;    private String password;    private String email;    private String bio; //个人资料    private String favourite_section; //最喜欢的。。    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getUsername() {        return username;    }    public void setUsername(String username) {        this.username = username;    }    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }    public String getEmail() {        return email;    }    public void setEmail(String email) {        this.email = email;    }    public String getBio() {        return bio;    }    public void setBio(String bio) {        this.bio = bio;    }    public String getFavourite_section() {        return favourite_section;    }    public void setFavourite_section(String favouriteSection) {        favourite_section = favouriteSection;    }}Blog.javapackage com.mybatis.model;import java.util.List;/** * 博客类 * @author Administrator * */public class Blog {    private int id;    private String title;    private Author author;    private List<Post> posts; //博客类有很多文章， 与post表中的blog_id对应    public List<Post> getPosts() {        return posts;    }    public void setPosts(List<Post> posts) {        this.posts = posts;    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getTitle() {        return title;    }    public void setTitle(String title) {        this.title = title;    }    public Author getAuthor() {        return author;    }    public void setAuthor(Author author) {        this.author = author;    }}文章类Post.,javapackage com.mybatis.model;import java.util.Date;/** * 文章类 * @author Administrator * */public class Post {    private int id;    private int blog_id;    private int author_id;    private Date created_on;    private String section;    private String subject;    private String draft;    private String body;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public int getBlog_id() {        return blog_id;    }    public void setBlog_id(int blogId) {        blog_id = blogId;    }    public int getAuthor_id() {        return author_id;    }    public void setAuthor_id(int authorId) {        author_id = authorId;    }    public Date getCreated_on() {        return created_on;    }    public void setCreated_on(Date createdOn) {        created_on = createdOn;    }    public String getSection() {        return section;    }    public void setSection(String section) {        this.section = section;    }    public String getSubject() {        return subject;    }    public void setSubject(String subject) {        this.subject = subject;    }    public String getDraft() {        return draft;    }    public void setDraft(String draft) {        this.draft = draft;    }    public String getBody() {        return body;    }    public void setBody(String body) {        this.body = body;    }}

总配置文件

<?xml version="1.0" encoding="UTF-8" ?>   <!DOCTYPE configuration       PUBLIC "-//mybatis.org//DTD Config 3.0//EN"       "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration>  <typeAliases>    <!-- 给实体类去一个别名  -->      <typeAlias type="com.mybatis.model.Blog" alias="Blog"/>       <typeAlias type="com.mybatis.model.Post" alias="Post"/>  </typeAliases>  <!-- 数据源配置，这里用MySQL数据库 -->  <environments default="development">     <environment id="development">       <transactionManager type="JDBC"/>       <dataSource type="POOLED">           <property name="driver" value="com.mysql.jdbc.Driver"/>           <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/>           <property name="username" value="root"/>           <property name="password" value="123456"/>       </dataSource>     </environment>  </environments>  <mappers>      <mapper resource="com/mybatis/model/Blog.xml"/>  </mappers></configuration>Blog.xml<?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.mybatis.dao.IBlogDao"><!-- 用文章映射集合来映射博客 --><resultMap id="blogResult" type="Blog">    <id property="id" column="blog_id"/>    <result property="title" column="blog_title"/>    <!--文章集合 -->    <collection property="posts" ofType="Post">        <id property="id" column="post_id"/>        <result property="subject" column="post_subject"/>        <result property="body" column="post_body"/>    </collection></resultMap><select id="selectBlog" parameterType="int" resultMap="blogResult">         select              b.id as blog_id ,             b.title as blog_title,             b.author_id as blog_author_id,             p.id as post_id,             p.subject as post_subject,             p.body as post_body           from blog b               left outer join  post p on b.id=p.blog_id               where b.id=#{id}  </select></mapper>测试类Test.javapackage com.mybatis.test;import java.io.IOException;import java.util.List;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import com.mybatis.dao.IBlogDao;import com.mybatis.model.Blog;import com.mybatis.model.Post;public class Test {    /***     * 获得MyBatis SqlSessionFactory     * SqlSessionFactory 负责创建SqlSession ,一旦创建成功，就可以用SqlSession实例来执行映射语句     * ，commit,rollback,close等方法     * @return     */    private static SqlSessionFactory getSessionFactory(){        SqlSessionFactory sessionFactory=null;        String resource="configuration.xml";         try {            sessionFactory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader(resource));        } catch (IOException e) {            e.printStackTrace();        }        return sessionFactory;    }    /**     * main 方法     * @param args     */    public static void main(String[] args) {          SqlSession session=getSessionFactory().openSession();     try {         IBlogDao blogDao=session.getMapper(IBlogDao.class);         Blog blog=blogDao.selectBlog(1);         List<Post> postList=blog.getPosts();         for(Post post:postList){             System.out.println(post.getBody());         }        } catch (Exception e) {            e.printStackTrace();        }        finally{            session.close();        }    }}运行后结果如下：DEBUG [com.mybatis.dao.IBlogDao.selectBlog] - ooo Using Connection [com.mysql.jdbc.Connection@e00ed0]DEBUG [com.mybatis.dao.IBlogDao.selectBlog] - ==>  Preparing: select b.id as blog_id , b.title as blog_title, b.author_id as blog_author_id, p.id as post_id, p.subject as post_subject, p.body as post_body from blog b left outer join post p on b.id=p.blog_id where b.id=? DEBUG [com.mybatis.dao.IBlogDao.selectBlog] - ==> Parameters: 1(Integer)绝世武神大主宰灵域