多表关联,MyBatis快速入门
分类:编程应用

前方介绍了MyBatis的单表映射。上边来探视更复杂的多表映射。

眼下已经介绍了简便易行的MyBatis单表操作,这里来用多少个简练的工程来演示下多表关联。
工程创建参照他事他说加以考察MyBatis(一)单表操作

在这么些事例中有四个实体类,小编、作品和评价。

1.新建三张表(顾客表author, 文章表article, 批评表comment)

CREATE TABLE author (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(255) NOT NULL  UNIQUE,
  nickname      VARCHAR(255),
  birthday      DATE,
  register_time DATETIME     NOT NULL

) default charset=utf8;

CREATE TABLE article (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(255) NOT NULL,
  content     TEXT         NOT NULL,
  author      INT          NOT NULL,
  create_time DATETIME     NOT NULL,
  modify_time DATETIME     NOT NULL,
  FOREIGN KEY (author) REFERENCES author (id)

) default charset=utf8;

CREATE TABLE comment (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  author      INT      NOT NULL,
  article     INT      NOT NULL,
  content     TINYTEXT NOT NULL,
  create_time DATETIME NOT NULL,
  FOREIGN KEY (author) REFERENCES author (id),
  FOREIGN KEY (article) REFERENCES article (id)
) default charset=utf8;
public class Author { private int id; private String username; private String nickname; private LocalDate birthday; private LocalDateTime registerTime;}public class Article { private int id; private String title; private String content; private Author author; private List<Comment> comments; private LocalDateTime createTime; private LocalDateTime modifyTime;}public class Comment { private int id; private String content; private Author author; private Article article; private LocalDateTime createTime;}

2.新建几个实体类(Author, Article, Comment)--get,set, 构造方法轻巧

public class Author {
    private int id;
    private String username;
    private String nickname;
    private Date birthday;
    private Timestamp registerTime;
}

public class Article {
    private int id;
    private String title;
    private String content;
    private Author author;
    private List<Comment> comments;
    private Timestamp createTime;
    private Timestamp modifyTime;
}

public class Comment {
    private int id;
    private String content;
    private Author author;
    private Article article;
    private Timestamp createTime;
}

相应的数据表如下。

3.先看率先种查询操作,第一章大家的事例中表中的列名和实体类中的属性名是逐个对应的,所以,查询结果大家就径直用resultType来映射了,可是当列名和总体性名区别的时候,resultType就丰裕了,所以才有了resultMap来钦命各样列名和哪位属性名对应,如下:

    <select id="selectAuthor" resultMap="authorResult">
        SELECT
        id,
        username,
        nickname,
        birthday,
        register_time
        FROM author
        WHERE id = #{id}
    </select>
    <resultMap id="authorResult" type="Author">
        <id property="id" column="id"/>
        <result property="registerTime" column="register_time"/>
    </resultMap>

地点的事例中:<id>用来映射主键,<result>用来映射一边的属性,借使列名和品质名同样,还是能简简单单映射关系。譬如这里只钦定registerTime属性和register_time列名映射。

CREATE TABLE author ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR NOT NULL UNIQUE, nickname VARCHAR, birthday DATE, register_time DATETIME NOT NULL);CREATE TABLE article ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR NOT NULL, content TEXT NOT NULL, author INT NOT NULL, create_time DATETIME NOT NULL, modify_time DATETIME NOT NULL, FOREIGN KEY  REFERENCES author ;CREATE TABLE comment ( id INT AUTO_INCREMENT PRIMARY KEY, author INT NOT NULL, article INT NOT NULL, content TINYTEXT NOT NULL, create_time DATETIME NOT NULL, FOREIGN KEY  REFERENCES author , FOREIGN KEY  REFERENCES article ;

4. 次之种,多表查询的率先种格局。比方我们要询问某篇小说和对应的小编。先看例子:

<!-- 查询作者 -->
    <select id="selectAuthor" resultMap="authorResult">
        SELECT
        id,
        username,
        nickname,
        birthday,
        register_time
        FROM author
        WHERE id = #{id}
    </select>
    <resultMap id="authorResult" type="Author">
        <id property="id" column="id"/>
        <result property="registerTime" column="register_time"/>
    </resultMap>

<!-- 查询文章 -->
    <select id="selectArticle" resultMap="articleMap">
        SELECT
        id,
        title,
        content,
        author,
        create_time,
        modify_time
        FROM article
        WHERE id = #{id}
    </select>

    <resultMap id="articleMap" type="Article">
        <id property="id" column="id"/>
        <result property="createTime" column="create_time"/>
        <result property="modifyTime" column="modify_time"/>
<!-- 嵌套查询作者 -->
        <association property="author" column="author" select="selectAuthor"/>
    </resultMap>

下面的例子中,第贰个select查询只是贰个简约的单表查询,首个select查询中也是多个简短的单表查询(看起来是),可是在resultMap中钦命了贰个association标签,当中就钦定着另三个select查询的ID。MyBatis会在每一条查询记录上再执行二遍association钦赐的查询。以此来到达多表关联合检查询的目的。然而,很确定,当数据量大的时候,这种艺术的频率堪忧,因为每施行贰遍sql语句,大家还要施行贰遍association钦点的查询。所以经常采取上边包车型地铁询问办法:

其一事例比上边的单表映射复杂相当多,首先数据表和实体类的品质而不是各样对应的,有个别属性名称不一致,还大概有部十分键在实体类中是类,而在数码表中独有主键ID,某个属性依旧集结类型。

5.第二种查询办法。上边包车型地铁事例中,咱们询问某篇小说对应的撰稿人,以及对应的有所评价。

<select id="selectArticleDetails" resultMap="detailedArticleResultMap">
        select
        A.id as article_id,
        A.title as article_title,
        A.content as article_content,
        A.create_time as article_createTime,
        A.modify_time as article_modifyTime,
        B.id as author_id,
        B.username as author_username,
        B.nickname as author_nickname,
        B.birthday as author_birthday,
        B.register_time as author_registerTime,
        C.id as comment_id,
        C.content as comment_content,
        C.create_time as comment_createTime
        from Article A
        left outer join Author B on B.id = A.author
        left outer join Comment C on A.id = C.article
        where A.id = #{id}
    </select>

    <resultMap id="detailedArticleResultMap" type="Article">
        <id property="id" column="article_id"/>
        <result property="title" column="article_title"/>
        <result property="content" column="article_content"/>
        <result property="createTime" column="article_createTime"/>
        <result property="modifyTime" column="article_modifyTime"/>

<!-- 指定某些实体类属性对应的类型 -->
        <association property="author" javaType="Author">
            <id property="id" column="author_id"/>
            <result property="username" column="author_username"/>
            <result property="nickname" column="author_nickname"/>
            <result property="birthday" column="author_birthday"/>
            <result property="registerTime" column="author_registerTime"/>
        </association>

<!-- 指定集合属性对应的类型 -->
        <collection property="comments" ofType="Comment" columnPrefix="comment_">
            <id property="id" column="id"/>
            <result property="content" column="content"/>
            <result property="createTime" column="createTime"/>
        </collection>
    </resultMap>

单看查询语句来讲,这种查询办法和我们一直使用的多表关联合检查询大约完全一样,只是在resultMap中须求用association关键字来钦点相应的炫彩,这里association钦命的不再是一个select查询的ID,而是resultMap属性。同期用collection关键字钦点会集属性对应的种类和resultMap属性。这里columnPrefix用来钦定列名的前缀,result中的column就足以一贯写成content,而不用写成comment_content了。


最终demo

在头里的例证中,由于是总结的一对一单表映射,所以一贯行使resultType属性钦赐须要映射的结果。不过即使是头昏眼花的例证,或许列名和质量名不对应,那么这种意况就十一分了。那时候需求改为利用另壹脾质量resultMap来映射结果。

resultMap品质要求钦点多个resultMap的ID。在resultMap中大家供给内定结果的照射,假使列名和质量名同样的话还足以大约映射。id用于映射主键,result用于映射别的属性。

<!--查询作者--><select resultMap="authorResult"> SELECT id, username, nickname, birthday, register_time FROM author WHERE id = #{id}</select><!--作者结果映射--><resultMap type="Author"> <id property="id" column="id"/> <result property="registerTime" column="register_time"/></resultMap>

下面的例证演示了如何利用resultMap。上面我们来看看哪些关联结果。假诺大家今后要查询文章,由于作品表中有三个笔者的外键,小说实体类也可能有小编的引用。因而轻松的询问在这里并不适用。大家供给利用关联来将小说和我提到起来,有三种办法:嵌套查询关联和嵌套结果关系。

那是一种非常简单的诀要,极度轻便明白。嵌套查询关联必要选择association成分,并钦赐select属性。select属性钦定另多个查询的ID,MyBatis会在每一条记下上选拔该查询再推行一遍嵌套查询以赢得结果。

<!--查询文章--><select resultMap="articleMap"> SELECT id, title, content, author, create_time, modify_time FROM article WHERE id = #{id}</select><!--嵌套查询关联文章表--><resultMap type="Article"> <id property="id" column="id"/> <result property="createTime" column="create_time"/> <result property="modifyTime" column="modify_time"/> <association property="author" column="author" select="selectAuthor"/></resultMap>

可是这种措施有三个题材,正是名牌的N+1难点。大家要获取部分文章,须要实行一条SQL语句,然后对于每条小说,大家还得实践贰次查询来获得作者的音信。在询问数据量十分的大的时候会明确影响属性。为了幸免那个标题,大家须求采纳下面包车型客车办法:嵌套结果关系。

嵌套结果关系

嵌套结果关系其实正是大家在编辑SQL语句的时候平素编写多表查询。假诺有重名的列大家得以设想增添前缀来化解名称争持难点。供给静心本次大家在association成分加多的不是select属性了。而是resultMap属性。因为作品表和作者表存在重名属性,所以大家供给在SQL语句中应用as子句修改列名,同期选取columnPrefix="a_"来钦命列前缀。那样MyBatis技能精确识别。

其余七个急需在乎的地点是暗中认可景况下MyBatis的autoMappingBehaviorPARTIAL,也即是说MyBatis会自动映射单表属性,不过境遇这种关联结果就不会自行映射。因而大家在认可未有重新名称从此就可以手动设置autoMapping="true",覆盖MyBatis的大局配置。通常情况下autoMappingBehavior的值不要内定为FULL,除非您分明全体表的保有字段不会冒出重复。在咱们这几个事例中,假诺去掉表前缀并让MyBatis自动映射全部字段,你会发掘小编ID和小说ID会被漏洞很多的设置为同三个ID。

<select resultMap="articleMap"> SELECT article.id, title, content, author, create_time, modify_time, author.id AS a_id, username AS a_username, nickname AS a_nickname, birthday AS a_birthday, register_time AS a_register_time FROM article, author WHERE article.id = #{id} AND author.id = article.author</select><!--嵌套查询关联文章表--><resultMap type="Article" autoMapping="true"> <id property="id" column="id"/> <result property="createTime" column="create_time"/> <result property="modifyTime" column="modify_time"/> <association property="author" column="author" resultMap="authorMap" columnPrefix="a_"/></resultMap>

笔者们能够见到嵌套结果比嵌套查询要复杂非常多。那是为了质量而无助的折中方案。别的在结果映射中最棒显式钦命主键,由于主键能够独一标记行,能让MyBatis以更加好的品质来映射结果。

不经常叁个实体类会包涵另一个实体类的集聚。比如地方的稿子类包罗了多个斟酌集结。那时候就必要其余一种办法将会集映射到结果了。

嵌套查询集合

嵌套查询集合和嵌套查询关联非常周围,只可是把association要素改为collection要素就能够。和嵌套查询关联同样,嵌套查询集结也是有N+1品质难点。在数据量大的时候最棒不要接纳。

在嵌套查询集结中,必要格外增加七个天性ofType,钦赐结果中元素的档次。对于每一条记下,MyBatis会调用内定的查询,查询出一个晤面,并传给要映射的体系。

<!--嵌套查询关联文章表--><resultMap type="Article" autoMapping="true"> <result property="createTime" column="create_time"/> <result property="modifyTime" column="modify_time"/> <collection property="comments" column="id" ofType="Comment" select="selectCommentsByArticle"/></resultMap><!--查找评论--><select resultMap="commentMap"> SELECT id, content, create_time FROM comment WHERE article = #{id}</select><resultMap type="Comment" autoMapping="true"> <id property="id" column="id"/> <result property="createTime" column="create_time"/></resultMap>

嵌套结果集结

嵌套结果集结和嵌套结果关系类似。可是出于本次不是至极的涉及映射,而是一对多的汇聚映射。所以我们不得不动用外连接来编写SQL语句。相同的,为了差距重名的行,我们供给加多列前缀。其他争辨类还应该有多少个外键,这里为了省事就不进行查询和照耀了。要是再加多外键的映照,SQL语句就可以变得更加长。

<select resultMap="articleMap"> SELECT article.id, title, article.content, article.author, article.create_time, modify_time, comment.id AS c_id, comment.content AS c_content, comment.create_time AS c_create_time FROM article LEFT JOIN comment ON article.id = comment.article WHERE article.id = #{id}</select><!--嵌套查询关联文章表--><resultMap type="Article" autoMapping="true"> <id property="id" column="id"/> <result property="createTime" column="create_time"/> <result property="modifyTime" column="modify_time"/> <collection property="comments" column="id" ofType="Comment" resultMap="commentMap" columnPrefix="c_"/></resultMap>

MyBatis中最困苦的地方正是这个映射了。在编辑映射的时候大家必需特别小心,最棒使用单元测验来协理大家一步一步核查映射的不利。

测量试验一下

前方介绍了单表映射,关联映射,集结映射等二种酷炫格局。上面大家来看看MyBatis文书档案中的两个例子。这是二个相当长的SQL语句和相应的结果映射。假如您一丝一毫看懂了,就说明你早就完全调节了MyBatis最核心最注重的功力了。

<!-- Very Complex Statement --><select 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>

相应的炫彩文件。

<!-- Very Complex Result Map --><resultMap 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> </collection></resultMap>

本文由正版必中一肖图发布于编程应用,转载请注明出处:多表关联,MyBatis快速入门

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文