mybatis一对一,一对多查询

最近重新看了一遍mybatis, 对这块进行总结, 方便后期学习工作查询.

---

准备:

spring和mybatis的环境搭建这里就不赘述 .
准备实验的两个实体类:
Blog:

public class Blog {
        private String blogId;
        private String title;
        private Author author;

        // get/set略
    }   

Author:

public class Author {
        private String authorId;
        private String username;
        private String password;
        private String email;
        private List<Blog> blogs;
    }

对应两张表:
blog:

author:

一个Blog对象对应一个Author, 一个Author可以对应多个Blog对象. 下面将对一对一和一对多查询进行分析.

---

一对一:

一对一使用关键字 : Association

第一种方式:

当查询一条blog,会将基本信息封装进Blog对象中, 然后针对author_id重新查询一次author表, 将查询数据放到Author对象中,此时构建出来的Blog对象, 就包括Author .
注意 : 虽然这种方式简单, 但是会带来N+1问题
执行单条sql查询出一个blog集合(+1)
然后对集合中每个Blog, 取出author_id重新查一次author表的详细信息(+N)
AuthorMapper.xml:

<mapper namespace="com.zhangyan.mapper.BlogMapper" >
        <resultMap type="com.zhangyan.pojo.Blog" id="blogMap">
            <id property="blogId" column="blog_id"/>
            <result property="title" column="title"/>
            <association property="author" select="getAuthorById" 
                column="author_id" javaType="com.zhangyan.pojo.Author" >
            </association>
        </resultMap>
        <select id="selectBlogById" resultMap="blogMap" parameterType="java.lang.String">
            select * from blog where blog_id = #{id}
        </select>
        <select id="getAuthorById" resultType="com.zhangyan.pojo.Author">
            select
             author_id as authorId,
             username as username,
             password as password,
             email as email
             from author where author_id=#{author_id}
        </select>
    </mapper>

第二种方式:

一条join语句查出blog和author所有信息, 然后封装进对应的对象中.

<mapper namespace="com.zhangyan.mapper.BlogMapper" >
        <resultMap type="com.zhangyan.pojo.Blog" id="blogMap">
            <id property="blogId" column="blog_id"/>
            <result property="title" column="title"/>
            <association property="author" column="author_id" resultMap="authorMap" />
        </resultMap>
        <resultMap type="com.zhangyan.pojo.Author" id="authorMap">
            <id property="authorId" column="author_id"/>
            <result property="username" column="username"/>
            <result property="password" column="password"/>
            <result property="email" column="email"/>
        </resultMap>
        <select id="selectBlogById" resultMap="blogMap" parameterType="java.lang.String">
            select * from blog b left join author a on b.author_id=a.author_id where blog_id = #{id}
        </select>
    </mapper>

上面这种方式可以独立出一个ResultMap, 可以供其他查询使用 . 但是如果假设只使用一次可以使用下面格式 .

<mapper namespace="com.zhangyan.mapper.BlogMapper" >
        <resultMap type="com.zhangyan.pojo.Blog" id="blogMap">
            <id property="blogId" column="blog_id"/>
            <result property="title" column="title"/>
            <association property="author" column="author_id" javaType="com.zhangyan.pojo.Author">
                <id property="authorId" column="author_id"/>
            <result property="username" column="username"/>
            <result property="password" column="password"/>
            <result property="email" column="email"/>   
            </association>
        </resultMap>
        <select id="selectBlogById" resultMap="blogMap" parameterType="java.lang.String">
            select * from blog b left join author a on b.author_id=a.author_id where blog_id = #{id}
        </select>
    </mapper>

---

一对多:

第一种方式:

当然这种方式, 也是引起N+1问题.

<mapper namespace="com.zhangyan.mapper.AuthorMapper" >
        <resultMap type="com.zhangyan.pojo.Author" id="authorMap">
            <id property="authorId" column="author_id"/>
            <result property="username" column="username"/>
            <result property="password" column="password"/>
            <result property="email" column="email"/>
            <collection property="blogs" column="author_id" ofType="com.zhangyan.pojo.Blog" 
                javaType="ArrayList" select="getBlogs"/>        
        </resultMap>

        <select id="selectAuthorById" resultMap="authorMap" parameterType="java.lang.String">
            select * from author where author_id=#{author_id}
        </select>
        <select id="getBlogs" resultType="com.zhangyan.pojo.Blog" parameterType="java.lang.String">
            select 
            blog_id as blogId,
            title as title
            from blog where author_id=#{author_id}
        </select>
    </mapper>

第二种方式:

当然也可以单独提出来resultmap.这里就不赘述.

<mapper namespace="com.zhangyan.mapper.AuthorMapper" >
        <resultMap type="com.zhangyan.pojo.Author" id="authorMap">
            <id property="authorId" column="author_id"/>
            <result property="username" column="username"/>
            <result property="password" column="password"/>
            <result property="email" column="email"/>
            <collection property="blogs" column="author_id" ofType="com.zhangyan.pojo.Blog" 
                javaType="ArrayList">
                <id property="blogId" column="blog_id"/>
                <result property="title" column="title"/>   
            </collection>       
        </resultMap>

        <select id="selectAuthorById" resultMap="authorMap" parameterType="java.lang.String">
            select * from author a left join blog b on a.author_id=b.author_id where a.author_id=#{author_id}
        </select>
    </mapper>