There are two ways to associate nested queries with nested results, which is explained in this paper.
The previous chapter describes a many-to-one relationship, using <association></association>, which is a complex type of association.Let's take an example to review, for example: a blog has a user, and affinity mapping works on this result.First, let's look at the table structure fields that we'll use in this article:
Blog
blog : id title author_id
//author
author: id username password email bio favourite_section
//Article
post :id blog_id author_id created_on section subject draft body
//comment
comment : id post_id name comment
//Label
T : id name
We associate a blog with a user like:
<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>
Notice that this union query, and that all results are renamed with unique and clear names.This makes mapping very simple.Now we can map this result:
<resultMap id="blogResult" type="Blog">
<id property="id" column="blog_id"/>
<result property="title" column="blog_title"/>
<!-- Associate with a user,Blog When attributes within a class author,The associated column is the original 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>
In the example above, you can see that the author of the blog associates the authorResult result mapping to load the author instance.In the example above, an external result mapping element is used to map the association.This allows Author result mappings to be reused.However, you do not need to reuse it, or you simply reference all your results to map to a result map that is described separately.You can nest result maps.The same example of using this approach is given here:
<resultMap id="blogResult" type="Blog">
<id property="id" column="blog_id"/>
<result property="title" column="blog_title"/>
<!-- Associate with a user,Blog When attributes within a class author,The associated column is the original 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>
You've seen above how to handle one type of association. But what about "There are many"?The main work of this article is to talk about this.
aggregate
Relative to associations, set mapping has an additional attribute, ofType. This attribute is important to distinguish JavaBean (or field) attribute types from collection containing types. ofType is used to represent collection containing types.
<collection property="posts" ofType="Post">
<id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>
<result property="body" column="post_body"/>
</collection>
Collection elements have almost the same roles and associations.Let's continue with the example above, where a blog has only one author.But blogs have a lot of articles, and in the blog class, this can be represented by the following: private List<Post> posts;
This time federate blog tables and article tables (a blog_id can correspond to many articles) SQL is as follows:
<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>
Now use the "Article Mapping Collection" to map "Blog", which can be simply written as: <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>
Advanced associations and set mappings have a lot to think about.Just let the collection mapping described by the face tidy up a little (there are many articles under a blog).
First create the tables you need and insert some data into them.
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,'Maple Tree','123456','824156593@qq.com','A little boy in Hefei','Travel?');
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,'Fiction Blog',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(),'Travel?','Fantasy','draft','Ultimate Martial Gods');
insert into post(id,blog_id,author_id,created_on,section,subject,draft,body)
values(2,1,1001,now(),'Travel?','Fantasy','draft','Dominant');
insert into post(id,blog_id,author_id,created_on,section,subject,draft,body)
values(3,1,1001,now(),'Travel?','Fantasy','draft','Spiritual Fields');
Before you paste JAVA code, look at the directory structure: package com.mybatis.model;
/**
* Author class
* @author Administrator
*
*/
public class Author {
private int id;
private String username;
private String password;
private String email;
private String bio; //personal data
private String favourite_section; //Favourite..
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;
/**
* Blog Class
* @author Administrator
*
*/
public class Blog {
private int id;
private String title;
private Author author;
private List<Post> posts; //The blog class has many articles corresponding to the blog_id in the post table
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;
/**
* Article Class
* @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;
}
}
Total Profile <?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>
<!-- Alias an entity class -->
<typeAlias type="com.mybatis.model.Blog" alias="Blog"/>
<typeAlias type="com.mybatis.model.Post" alias="Post"/>
</typeAliases>
<!-- Data source configuration, used here MySQL data base -->
<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">
<!-- Map blogs with a collection of article mappings -->
<resultMap id="blogResult" type="Blog">
<id property="id" column="blog_id"/>
<result property="title" column="blog_title"/>
<!--Collection of Articles -->
<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 class 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 {
/***
* Get MyBatis SqlSessionFactory
* SqlSessionFactory Responsible for creating SqlSession, once created successfully you can use the SqlSession instance to execute the mapping statement
* ,commit,rollback,close Methods such as
* @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 Method
* @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();
}
}
}
The results after running are as follows:
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)
//Ultimate Martial Gods
//Dominant
//Spiritual Fields
This article was adapted from http://blog.csdn.net/yulei_qq/article/details/22057633