MP提供了大量单表查询的方法,但是没有多表的操作,所以涉及到多表的查询时,需要我们自己实现
前面内容可以跳过,可通过目录跳到多表查询示例
思路1
因为MP是基于MyBatis实现,我们可以使用MyBatis的结果映射来做,下面是一个官网的例子
https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#Result_Maps
结果映射
<!-- 非常复杂的语句 -->
<select id="selectBlogDetails" resultMap="detailedBlogResultMap">
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_password,
A.email as author_email,
A.bio as author_bio,
A.favourite_section as author_favourite_section,
P.id as post_id,
P.blog_id as post_blog_id,
P.author_id as post_author_id,
P.created_on as post_created_on,
P.section as post_section,
P.subject as post_subject,
P.draft as draft,
P.body as post_body,
C.id as comment_id,
C.post_id as comment_post_id,
C.name as comment_name,
C.comment as comment_text,
T.id as tag_id,
T.name as tag_name
from Blog B
left outer join Author A on B.author_id = A.id
left outer join Post P on B.id = P.blog_id
left outer join Comment C on P.id = C.post_id
left outer join Post_Tag PT on PT.post_id = P.id
left outer join Tag T on PT.tag_id = T.id
where B.id = #{id}
</select>
这个对象表示了一篇博客,它由某位作者所写,有很多的博文,每篇博文有零或多条的评论和标签。 我们先来看看下面这个完整的例子,它是一个非常复杂的结果映射(假设作者,博客,博文,评论和标签都是类型别名)
<!-- 非常复杂的结果映射 -->
<resultMap id="detailedBlogResultMap" type="Blog">
<constructor>
<idArg column="blog_id" javaType="int"/>
</constructor>
<result property="title" column="blog_title"/>
<association property="author" 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"/>
<result property="favouriteSection" column="author_favourite_section"/>
</association>
<collection property="posts" ofType="Post">
<id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>
<association property="author" javaType="Author"/>
<collection property="comments" ofType="Comment">
<id property="id" column="comment_id"/>
</collection>
<collection property="tags" ofType="Tag" >
<id property="id" column="tag_id"/>
</collection>
<discriminator javaType="int" column="draft">
<case value="1" resultType="DraftPost"/>
</discriminator>
</collection>
</resultMap>
resultMap 元素有很多子元素和一个值得深入探讨的结构。 下面是resultMap 元素的概念视图。
查询的结果,会通过上面的结果映射,映射为下面这个类的实例
Blog.java
/**
* 博客
*/
@Data
public class Blog {
private int id;
private String title;
private Author author;
private List<Post> posts;
}
/**
* 博文
*/
@Data
class Post{
private int id;
private String subject;
private Author author;
private List<Comment> comments;
private List<Tag> tags;
}
/**
* 作者
*/
@Data
class Author{
private int id;
private String username;
private String password;
private String email;
private String bio;
private String favourite_section;
}
/**
* 评论
*/
@Data
class Comment{
private int id;
}
/**
* 标签
*/
@Data
cl