场景:
一个博客系统中,用户(author)可以任意发表博文(blog),用户还可以对博文进行评论(comment)。
数据库中三张表:blog,author,comment。comment使用外键关联blog(多对一),blog使用外键关联author(一对一,假定作者只写了一篇文章)
sql脚本如下:
create table author (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
create table blog (
id INT PRIMARY KEY AUTO_INCREMENT,
authorId INT,
title VARCHAR(100),
createTime TIMESTAMP,
content TEXT,
FOREIGN KEY (authorId) REFERENCES author(id)
);
create table comment (
id INT PRIMARY KEY AUTO_INCREMENT,
blogId INT,
content TEXT,
createTime TIMESTAMP,
FOREIGN KEY (blogId) REFERENCES blog(id)
);
INSERT INTO author(name) VALUES('A1');
INSERT INTO author(name) VALUES('A2');
INSERT INTO blog(authorId, title, createTime, content) VALUES('1', '第一篇', '20151202', '第一篇的内容');
INSERT INTO blog(authorId, title, createTime, content) VALUES('2', '第二篇', '20151203', 'A2写的第二篇的内容');
INSERT INTO comment(blogId, content, createTime) VALUES(1, '第一篇的评论', '20151203');
INSERT INTO comment(blogId, content, createTime) VALUES(2, '第二篇的评论', '20151204');
实体类如下:
public class Author {
private int id;
private String name;
// getters and setters
}
public class Comment {
private int id;
private int blogId;
private String content;
private Timestamp timestamp;
// getters and setters
}
public class Blog {
private int id;
private String title;
private Timestamp createTime;
private String content;
// 维护博文与作者的1:1关系
private Author authorId;
// 维护博文与评论的1:n关系
private List<Comment> comments;
}
sql映射文件BlogMapper如下:
<?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.mapping.BlogMapper">
<!-- 方法一:嵌套查询-->
<select id="getBlogById" parameterType="int" resultMap="BlogResult">
select id,authorId,title,createTime,content from blog where id=#{id}
</select>
<resultMap type="Blog" id="BlogResult">
<id property="id" column="id"/>
<result property="title" column="title"/>
<result property="createTime" column="createTime"/>
<result property="content" column="content"/>
<!-- 这个column就是查询author表时提供的参数,嵌套select -->
<association property="author" column="authorId" javaType="Author" select="getAuthorById"></association>
<!-- ofType指定集合中的对象类型 -->
<collection property="comments" column="id" javaType="List" ofType="Comment" select="getCommentsById"></collection>
</resultMap>
<select id="getAuthorById" parameterType="int" resultType="Author">
select id, name from author where id =#{id}
</select>
<select id="getCommentsById" parameterType="int" resultType="Comment">
select id, blogId, content, createTime from comment where blogId=#{id}
</select>
<!-- 方法二:等值连接查询 or join语句 来嵌套结果-->
<resultMap type="Blog" id="BlogResult2">
<id property="id" column="id"/>
<result property="title" column="title"/>
<result property="createTime" column="createTime"/>
<result property="content" column="content"/>
<!-- 这个column就是查询author表提供的参数 -->
<association property="author" column="authorId" javaType="Author">
<id property="id" column="authorId"/>
<result property="name" column="name"/>
</association>
<!-- ofType指定集合中的对象类型 -->
<collection property="comments" column="id" javaType="List" ofType="Comment">
<id property="id" column="commentId"/>
<result property="content" column="commentContent"/>
<result property="createTime" column="commentCreateTime"/>
</collection>
</resultMap>
<select id="getBlogById2" parameterType="int" resultMap="BlogResult2">
select A.id, A.title, A.createTime, A.content,
B.id as authorId, B.name,
C.id as commentId, C.content as commentContent, C.createTime as commentCreateTime
from blog as A, author as B, comment as C
where A.authorId = B.id and A.id = C.blogId and A.id = #{id}
</select>
<select id="getBlogs" resultMap="BlogResult">
select A.id, A.title, A.createTime, A.content,
B.id as authorId, B.name,
C.id as commentId, C.content as commentContent, C.createTime as commentCreateTime
from blog as A, author as B, comment as C
where A.authorId = B.id and A.id = C.blogId
</select>
</mapper>
方法一:嵌套查询
需要执行多个sql语句,如本例中,先执行select * from blog where id = #{id},再通过得出的authorId查询author,还要执行select * from comment where blogId = #{id}
该方式:简单,对于大型数据集合效率低,查询慢,如getBlogs()方法,如果blog数目为n,那么执行sql的条数应该为1+n+n,也就是所谓的“N+1 查询问题”。
执行了一个单独的 SQL 语句来获取结果列表(就是“+1”)。
对返回的每条记录,你执行了一个查询语句来为每个加载细节(就是“N”)。
方法二:等值连接查询 or join语句 来嵌套结果
可以解决上面存在的问题
测试代码:
public static void main(String[] args) {
SqlSession sqlSession = MyBatisUtil.getSqlSession();
String stmt = "com.mapping.BlogMapper.getBlogById";
Blog blog = sqlSession.selectOne(stmt, 1);
System.out.println(blog.getId() + " " + blog.getTitle() + " " + blog.getAuthor().getName()
+ blog.getCreateTime() + " " + blog.getComments().get(0).getContent());
MyBatisUtil.closeSession(sqlSession);
}