MyBatis表關聯 一對多 多對一 多對多

在上一篇文章中，我們已經搭建了eclipse，mybatis，mysql 的開發環境，並且實現了 mybatis 的簡單的增刪改查功能。參考文章

有了上一篇文章的基礎，可以處理一些簡單的應用，但在實際專案中，經常是關聯表的查詢。比如：最常見到的多對一，一對多等。

1. Mybatis表關聯一對多

在Java實體物件對中，一對多可以根據List和Set來實現，兩者在mybitis中都是通過collection標籤來配合來加以實現。這篇介紹的是多表中的多對一表關聯查詢。

應用場景：首先根據文章 ID 讀取一篇文章資訊，然後再讀取這篇文章的評論。

1.1 準備表和資料

CREATE TABLE `article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL DEFAULT '',
  `content` text,
  `created` datetime NOT NULL DEFAULT '2018-09-10 00:00:00',
  PRIMARY KEY (`id`)
);
INSERT INTO `article` VALUES ('1', 'MyBatis詳解', 'MyBatis詳解。。。', '2018-09-10 20:11:23');

CREATE TABLE `comment` (
  `cid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `aid` int(10) unsigned NOT NULL,
  `commentcontent` varchar(254) NOT NULL DEFAULT '',
  `postedby` varchar(20) NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT '2018-09-10 00:00:00',
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `comment` VALUES ('1', '1', 'MyBatis詳解這篇文章寫的真好', 'Angelia的頭號粉絲', '2018-09-10 21:40:17');
INSERT INTO `comment` VALUES ('2', '1', 'MyBatis詳解這篇文章寫的簡直是太好了', 'Angelia的二號粉絲', '2018-09-10 21:40:17');
INSERT INTO `comment` VALUES ('3', '1', 'MyBatis詳解這篇文章寫的不能再好了', 'Angelia的三號粉絲', '2018-09-10 21:40:17'); 

1.2 建立表對應的 JavaBean 物件

public class Article {
	private int id;
	private String title;
	private String content;
	private List<Comment> comments;

	// getters and setters
}

public class Comment {
	private int id;
	private String commentcontent;
	private String postedBy;
	
	private Article article;

	// getters and setters
}

1.3 介面IArticle和對映檔案Article.xml

public interface IArticle {
	public Article getArticle(int id);
}

<mapper namespace="com.angelia.mybatis.dao.IArticle">
	
	<resultMap id="articleMap" type="Article" >
		<id column="id" property="id" />
		<!-- <result property="id" column="id" /> -->
		<result property="title" column="title" />
		<result property="content" column="content" />
	</resultMap>
	
	<!-- Article級聯評論查詢 方法配置 (一篇文章對多個評論) -->
	<resultMap id="articleCommentMap" type="Article" extends="articleMap">
		<collection property="comments" ofType="com.angelia.mybatis.model.Comment" column="aid">
			<id property="id" column="cid" javaType="int" jdbcType="INTEGER" />
			<result property="commentcontent" column="commentcontent" javaType="string" jdbcType="VARCHAR" />
			<result property="postedBy" column="postedBy" javaType="string" jdbcType="VARCHAR" />
		</collection>
	</resultMap>
	
	<select id="getArticle" resultMap="articleCommentMap" parameterType="int">
		SELECT a.*, c.*
		FROM article a, comment c
		WHERE a.id = c.aid AND id = #{aid}
	</select>

</mapper>

1.4 測試類

@Test
public void test() {
	Article article = articleMapper.getArticle(1);
	System.out.println("Article: " + article);
	List<Comment> comments = article.getComments();  
	for(Comment comment : comments) {
		System.out.println("Comment Content:" + comment.getCommentcontent()); 
		System.out.println("Content Posted By:" + comment.getPostedBy());                
	}  
}

2. Mybatis表關聯多對一

在上章的一對多中，我們已經學習如何在 Mybatis 中關聯多表，但在實際專案中也是經常使用多對一的情況，在這一節中我們來學習如何處理多對一。多表對映的多對一關係要用到 mybitis 的 association 來加以實現。

應用場景：首先根據帖子 ID 讀取一個帖子資訊，然後再讀取這個帖子所屬的使用者資訊。

2.1 建立介面IComment和對映檔案Comment.xml

public interface IComment {
	public Comment getComment(int cid);
}

<mapper namespace="com.angelia.mybatis.dao.IComment">

	<resultMap id="commentMap" type="Comment">
		<id property="id" column="cid" />
		<result property="commentcontent" column="commentcontent" />
		<result property="postedBy" column="postedBy" />
	</resultMap>

	<resultMap id="commentArticleMap" type="Comment" extends="commentMap">
		<association property="article" javaType="Article">
			<id property="id" column="aid" />
			<result property="title" column="title" />
			<result property="content" column="content" />
		</association>
	</resultMap>
	
	<!-- <resultMap id="commentArticleMap" type="Comment" >
		<result property="id" column="cid" />
		<result property="commentcontent" column="commentcontent" />
		<result property="postedBy" column="postedBy" />
		<association property="article" javaType="Article">
			<id property="id" column="aid" />
			<result property="title" column="title" />
			<result property="content" column="content" />
		</association>
	</resultMap> -->

	<select id="getComment" resultMap="commentArticleMap" parameterType="int">
		SELECT a.*, c.*
		FROM article a, comment c
		WHERE a.id = c.aid AND c.cid = #{cid}
	</select>

</mapper>

2.2 測試程式碼

@Test
public void test() {
	Comment comment = commentMapper.getComment(1);
	System.out.println("Comment Content:" + comment.getCommentcontent()); 
	System.out.println("Content Posted By:" + comment.getPostedBy());
	System.out.println("Article : " + comment.getArticle());
}

3. Mybatis 多對多

Mybatis3.0新增了association和collection標籤專門用於對多個相關實體類資料進行級聯查詢，但仍不支援多個相關實體類資料的級聯儲存和級聯刪除操作。因此在進行實體類多對多對映表設計時，需要專門建立一個關聯物件類對相關實體類的關聯關係進行描述。下文將以“Author”和“Paper”兩個實體類之間的多對多關聯對映為例進行CRUD操作。

應用場景：假設專案中存在作者和論文，從一個作者讀取出它所關聯的論文，從一篇論文也知道這篇論文的所有作者資訊。

3.1 準備表結構和資料

CREATE TABLE `author` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author_name` varchar(64) NOT NULL DEFAULT '',
  `mobile` varchar(16) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of author
-- ----------------------------
INSERT INTO `author` VALUES ('1', 'Angelia', '13888888888');
INSERT INTO `author` VALUES ('2', 'Snowdrop', '13666666666');

CREATE TABLE `paper` (
  `paper_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `paper_title` varchar(100) NOT NULL DEFAULT '',
  `paper_content` text,
  PRIMARY KEY (`paper_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of paper
-- ----------------------------
INSERT INTO `paper` VALUES ('1', 'MyBatis詳解 表關聯多對一', '多對一具體詳解');
INSERT INTO `paper` VALUES ('2', 'MyBatis詳解 表關聯一對多', '一對多具體詳解');
INSERT INTO `paper` VALUES ('3', 'MyBatis詳解 表關聯多對多', '多對多具體詳解');

CREATE TABLE `author_paper` (
  `author_id` int(10) unsigned NOT NULL DEFAULT '0',
  `paper_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_group
-- ----------------------------
INSERT INTO `author_paper` VALUES ('1', '1');
INSERT INTO `author_paper` VALUES ('2', '1');
INSERT INTO `author_paper` VALUES ('1', '2');
INSERT INTO `author_paper` VALUES ('2', '3');

3.2 建立表對應的JavaBean 物件

public class Author {
	private int id;
	private String authorName;
	private String mobile;

        private List<Paper> papers;

	// getters and setters
}

public class Paper {
	private int paperId;
	private String title;
	private String content;
	
	private List<Author> authors;
	
	// getters and setters
}

public class AuthorPaper {
	private int authorId;  
        private int paperId;
	
        // getters and setters
}

3.3 建立介面和對映檔案Author.xml，Paper.xml和AuthorPaper.xml

public interface IAuthor {
	
	public Author getAuthor(int authorId);

	public void insertAuthor(Author author);
}

public interface IPaper {

	public Paper getPaper(int paperId);

	public void insertPaper(Paper paper);
}

public interface IAuthorPaper {
	
	public List<Author> getAuthorsByPaperId(int paperId);
	
	public List<Paper> getPapersByAuthorId(int id);
	
	public void insertAuthorPaper(AuthorPaper authorPaper);
}

<mapper namespace="com.angelia.mybatis.dao.IAuthor">
	
	<parameterMap type="Author" id="parameterAuthorMap">
		<parameter property="id"/>
		<parameter property="authorName"/>
		<parameter property="mobile"/>
	</parameterMap>
	
	<insert id="insertAuthor" parameterMap="parameterAuthorMap">	
		INSERT INTO author(author_name, mobile) VALUES(#{authorName}, #{mobile});
	</insert>   
	
	<resultMap id="resultAuthorMap" type="Author" >
		<result property="id" column="id"/>
		<result property="authorName" column="author_name"/>
		<result property="mobile" column="mobile"/>
		
		<collection property="papers" column="id" select="com.angelia.mybatis.dao.IAuthorPaper.getPapersByAuthorId"/>
	</resultMap>
	
	<select id="getAuthor" resultMap="resultAuthorMap" parameterType="int">
		SELECT * FROM author WHERE id = #{id}
	</select>

</mapper>

<mapper namespace="com.angelia.mybatis.dao.IPaper">

	<parameterMap id="parameterPaperMap" type="Paper">
		<parameter property="paperId" />
		<parameter property="title" />
		<parameter property="content" />
	</parameterMap>

	<insert id="insertPaper" parameterMap="parameterPaperMap">
		INSERT INTO `paper`(paper_title, paper_content) VALUES(#{title}, #{content});
	</insert>

	<resultMap id="resultPaperMap" type="Paper">
		<id property="paperId" column="paper_id" />
		<result property="title" column="paper_title" />
		<result property="content" column="paper_content" />

		<collection property="authors" column="paper_id" select="com.angelia.mybatis.dao.IAuthorPaper.getAuthorsByPaperId" />
	</resultMap>

	<select id="getPaper" resultMap="resultPaperMap" parameterType="int">
		SELECT * FROM `paper` WHERE paper_id = #{paperId}
	</select>

</mapper>

<mapper namespace="com.angelia.mybatis.dao.IAuthorPaper">
	
	<parameterMap type="AuthorPaper" id="parameterAuthorPaperMap">
		<parameter property="authorId"/>
		<parameter property="paperId"/>
	</parameterMap>
	
	<insert id="insertAuthorPaper"  parameterMap="parameterAuthorPaperMap">
		INSERT INTO author_paper(author_id, paper_id) VALUES(#{authorId}, #{paperId})
	</insert>
	
	<!-- 根據一篇論文的ID,檢視這篇論文的的所有作者 -->
	<resultMap id="resultAuthorMap2" type="Author" >
		<result property="id" column="id"/>
		<result property="authorName" column="author_name"/>
		<result property="mobile" column="mobile"/>
	</resultMap>
	
	<select id="getAuthorsByPaperId" resultMap="resultAuthorMap2" parameterType="int">
		SELECT a.*, ap.paper_id
		FROM author a, author_paper ap
		WHERE a.id=ap.author_id AND ap.paper_id = #{paperId}
	</select>
	
	<!-- 根據一個作者ID,檢視這個作者所有論文-->
	<resultMap id="resultPaperMap2" type="Paper" >
		<result property="paperId" column="paper_id"/>
		<result property="title" column="paper_title"/>
		<result property="content" column="paper_content"/>
	</resultMap> 
	
	<select id="getPapersByAuthorId" resultMap="resultPaperMap2" parameterType="int">
		SELECT p.*, ap.author_id
		FROM paper p, author_paper ap
		WHERE p.paper_id = ap.paper_id AND ap.author_id = #{id}
	</select>

</mapper>

3.4 建立測試類

public class AuthorPaperTest {
	private SqlSession session;
	private IAuthorPaper authorPaperMapper;
	private IAuthor authorMapper;
	private IPaper paperMapper;

	@Before
	public void before() {
		session = MyBatisUtil.getSessionFactory().openSession();
		// 獲取Mapper
		authorPaperMapper = session.getMapper(IAuthorPaper.class);
		authorMapper = session.getMapper(IAuthor.class);
		paperMapper = session.getMapper(IPaper.class);
	}
	@After
	public void after() {
		session.close();
	}

	@Test
	public void test() {
		//getAuthorsByPaperId(1);
		//getPapersByAuthorId(1);
		
		//getAuthorAndPapers(2);
		
		getAuthorAndPapers(2);
		
		/*Author author = new Author();
		author.setAuthorName("優雅的作家Angelia");
		author.setMobile("13666666666");
		saveAuthor(author);
		
		Paper paper = new Paper();
		paper.setTitle("MyBatis從入門到精通");
		paper.setContent("MyBatis從入門到精通...");
		savePaper(paper);*/
		
		/*AuthorPaper ap = new AuthorPaper();
		ap.setAuthorId(4);
		ap.setPaperId(4);
		
		saveAuthorPaper(ap);*/
	}

	public void getAuthorsByPaperId(int paperId) {
		List<Author> authors = authorPaperMapper.getAuthorsByPaperId(paperId);
		for (Author author : authors) {
			System.out.println(author);
		}
	}
	
	public void getPapersByAuthorId(int id) {
		List<Paper> papers = authorPaperMapper.getPapersByAuthorId(id);
		for (Paper paper : papers) {
			System.out.println(paper);
		}
	}
	
	public void getPaperAndAuthors(int paperId) {
		Paper paper = paperMapper.getPaper(paperId);
		System.out.println(paper);

		List<Author> authors = paper.getAuthors();
		for (Author author : authors) {
			System.out.println(author);
		}
	}
	
	public void getAuthorAndPapers(int authorId) {
		Author author = authorMapper.getAuthor(authorId);
		System.out.println(author);

		List<Paper> papers = author.getPapers();
		for (Paper paper : papers) {
			System.out.println(paper);
		}
	}

	public void saveAuthorPaper(AuthorPaper authorPaper) {
		authorPaperMapper.insertAuthorPaper(authorPaper);
		session.commit();
	}

	public void saveAuthor(Author author) {
		authorMapper.insertAuthor(author);
		session.commit();
	}

	public void savePaper(Paper paper) {
		paperMapper.insertPaper(paper);
		session.commit();
	}
}