mybatis学习日记—一对一与一对多

关联映射

一对一关联关系
尽量不使用select *，可能会出现列名冲突的问题，通过列别名来避免冲突，本来resultMap就是做属性和列的对应关系
一对多关联关系
构造器：直接使用有参构造函数，并列的值赋给属性

一对一

方法一：普通方法

<resultMap type="entity.Blog" id="blogMap">
    <!-- 基础属性 -->
    <id property="id" column="id"/>
    <result property="title" column="title"/>
   <!--  author_id：作为外键的基本属性，建立关联关系之后，被关联属性取代了 -->
    <result property="author_id" column="author_id"/>

    <!-- 关联属性:一对一关联关系 -->
    <association property="author" javaType="entity.Author">
        <!-- blog里的author_id列对应的author表里的id列 -->
        <id property="id" column="author_id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="email" column="email"/>
        <result property="bio" column="bio"/>
    </association>
</resultMap>

<!-- 查询语句 -->
<select id="findBlogInfo" parameterType="int" resultMap="blogMap">
    select blog.id blog_id, title, author_id, author.id, username, password, email, bio
    from blog left join author on blog.author_id = author.id 
    where blog.id = #{id}
</select>

方法二：简化association，拆分为两个resultMap

<!-- 简化association，拆分为两个resultMap -->
<resultMap type="entity.Author" id="authorMap">
    <id property="id" column="author_id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="email" column="email"/>
        <result property="bio" column="bio"/>
</resultMap>
<resultMap type="entity.Blog" id="blogMap">
    <!-- 基本属性 -->
    <id property="id" column="blog_id"/>
    <result property="title" column="title"/>

    <!-- 关联属性 -->
    <association property="author" javaType="entity.Author" resultMap="authorMap"></association>
</resultMap>

<!-- 查询语句 -->
<select id="findBlogInfo" parameterType="int" resultMap="blogMap">
    select blog.id blog_id, title, author_id, author.id, username, password, email, bio
    from blog left join author on blog.author_id = author.id 
    where blog.id = #{id}
</select>

方法三：使用嵌套结果集映射，select指向另一个配置映射文件

<!-- 使用嵌套结果集映射，select指向另一个配置映射文件 -->
<resultMap type="entity.Blog" id="blogMap">
    <association property="author" javaType="entity.Author" select="findAuhtorById" column="author_id"></association>
</resultMap>
<select id="findAuhtorById" parameterType="int" resultType="entity.Author">
    select * from author where id = #{id}
</select>
<select id="findBlogById" parameterType="int" resultMap="blogMap">
    select * from Blog where id = #{id}
</select>

一对多

方法一：普通方法

<resultMap type="entity.Blog" id="blogPostMap">
    <!-- 基本属性 -->
    <id property="id" column="blog_id"/>
    <result property="title" column="title"/>

    <!-- 关联属性：一对多，collection ofType(集合内子元素的类型) -->
    <collection property="postList" ofType="entity.Post">
        <id property="id" column="post_id"/>
        <result property="subject" column="subject"/>
        <result property="section" column="section"/>
        <result property="body" column="body"/>
    </collection>
</resultMap>

<!-- 查询语句 -->
<select id="findBlogAndPost" parameterType="int" resultMap="blogPostMap">
    select blog.id blog_id, title, blog.author_id, post.id post_id, subject, section, body
    from blog left join post on blog.id = post.blog_id
    where blog.id = #{id}
</select>

方法二：使用嵌套结果集映射，select指向另一个配置映射文件

<!-- 使用嵌套结果集映射，select指向另一个配置映射文件 -->
<resultMap type="entity.Blog" id="blogPostMap">
    <collection property="postList" ofType="entity.Post" javaType="ArrayList" select="findPostByBlog" column="id"></collection>
</resultMap>
<select id="findPostByBlog" parameterType="int" resultType="entity.Post">
    select * from post where blog_id = #{id}
</select>
<select id="findBlogAndPost" parameterType="int" resultMap="blogPostMap">
    select * from blog where id = #{id}
</select>

方法三：构造器：直接使用有参构造函数，并列的值赋给属性

<!-- 构造器：直接使用有参构造函数，并列的值赋给属性 -->
<resultMap type="entity.Blog" id="blogPostMap">
    <id property="id" column="blog_id"/>
    <result property="title" column="title"/>

    <collection property="postList" ofType="entity.Post">
        <!-- 构造器 -->
        <constructor>
            <idArg column="post_id" javaType="int"/>
            <arg column="subject" javaType="String"/>
            <arg column="section" javaType="String"/>
            <arg column="body" javaType="String"/>
        </constructor>
    </collection>
</resultMap>

<!-- 查询语句 -->
<select id="findBlogAndPost" parameterType="int" resultMap="blogPostMap">
    select blog.id blog_id, title, blog.author_id, post.id post_id, subject, section, body
    from blog left join post on blog.id = post.blog_id
    where blog.id = #{id}
</select>

一对一和一对多的共同实现

<!-- 一对一和一对多的共同实现 -->
<resultMap type="entity.Blog" id="blogAuthorMap">
    <id property="id" column="blog_id"/>
    <result property="title" column="title"/>

    <association property="author" javaType="entity.Author">
        <id property="id" column="author_id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="email" column="email"/>
        <result property="bio" column="bio"/>
    </association>
</resultMap>

<!-- extends：继承上一个resultMap -->
<resultMap type="entity.Blog" id="blogAuthorPostMap" extends="blogAuthorMap">
    <collection property="postList" ofType="entity.Post">
        <id property="id" column="post_id"/>
        <result property="subject" column="subject"/>
        <result property="section" column="section"/>
        <result property="body" column="body"/>
    </collection>
</resultMap>

<select id="findBlogInfo" resultMap="blogAuthorPostMap" parameterType="int">
    select
    B.id blog_id,
    B.title,
    B.author_id,
    A.username,
    A.password,
    P.id post_id,
    P.subject,
    P.section,
    P.body 
    from blog B, post P, author A
    where B.author_id = A.id and B.id = P.blog_id 
    and B.id = #{id}
</select>