mybatis级联查询

向右看齐 2022-06-06 09:14 359阅读 0赞

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

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

博客   
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"/>  <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"/>  <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>  <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][http_blog.csdn.net_Smile_Miracle_article_details_53287671]

[http_blog.csdn.net_Smile_Miracle_article_details_53287671]: http://blog.csdn.net/Smile_Miracle/article/details/53287671