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>
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13

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

<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>
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在上面的例子中，你可以看到博客的作者关联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>
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13

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

集合

相对于关联来说，集合映射多了一个属性”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>
 
 
1
2
3
4
5

集合元素的作用和关联几乎是相同的。我们来继续上面的示例，一个博客只有一个作者。但是博客有很多文章，在博客类中，这可以由下面的写法来表示： 
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>
 
 
1
2
3
4
5
6
7
8
9
10
11
12

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

<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>
 
 
1
2
3
4
5
6
7
8
9

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

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

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','824156593@qq.com','合肥的小男孩','旅游');



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(),'旅游','玄幻','草稿','灵域');
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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

作者类 Author.java
package 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.java
package 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.,java
package 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;
    }

}
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166

总配置文件

<?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.java
package 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)
绝世武神
大主宰
灵域

转载网址：http://blog.csdn.net/Smile_Miracle/article/details/53287671