MyBatis表关联一对多多对一多对多

程序员文章站 2022-04-22 19:41:52

...

在上一篇文章中，我们已经搭建了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();
	}
}

相关标签： MyBatis 表关联多对一多对多

上一篇：下拉列表的操作

下一篇： 18.hibernate多对多单向关联

MyBatis表关联一对多多对一多对多

axure菜单怎么设置一对多联动效果?

JPA中实现双向多对多的关联关系(附代码下载)

MyBatis从入门到精通(十一)：MyBatis高级结果映射之一对多映射

[sql]使用自连接查询多对一数据

EXCEL VLOOKUP一对多查找根据部门查找员工姓名

JPA中实现双向一对多的关联关系

关于MyBatis 查询数据时属性中多对一的问题(多条数据对应一条数据)

mybatis 多对一关联查询

Django 标签筛选的实现代码(一对多、多对多)

JPA中实现单向一对多的关联关系

MyBatis表关联 一对多 多对一 多对多

axure菜单怎么设置一对多联动效果?

JPA中实现双向多对多的关联关系(附代码下载)

MyBatis从入门到精通(十一)：MyBatis高级结果映射之一对多映射

[sql]使用自连接查询多对一数据

EXCEL VLOOKUP一对多查找 根据部门查找员工姓名

JPA中实现双向一对多的关联关系

关于MyBatis 查询数据时属性中多对一的问题(多条数据对应一条数据)

mybatis 多对一关联查询

Django 标签筛选的实现代码(一对多、多对多)

JPA中实现单向一对多的关联关系

MyBatis表关联一对多多对一多对多

EXCEL VLOOKUP一对多查找根据部门查找员工姓名