mybatis ---- 级联查询一对多（集合映射）

川长思鸟来 2022-08-26 03:54 10阅读 0赞

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

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

博客
    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>

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

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

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

## 集合 ##

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

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

private List<Post> 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>

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

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

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

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(),'旅游','玄幻','草稿','灵域');

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

![SouthEast][]

作者类 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;
    	}
    	
    }

总配置文件

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

[SouthEast]: /images/20220824/0982fd0a0776451e829790db96898b8c.png