MyBatis之联合查询使用

Posted on Edited on In

1:1查询

嵌套查询

查询语句

1
2
3
4
5
6
7
8
9
<sql id="Base_Column_List">
    bid, author_id, title, content
</sql>
<select id="selectByPrimaryKeyAuthor" parameterType="java.lang.Integer" resultMap="BaseResultMapAuthor">
    select
    <include refid="Base_Column_List"/>
    from blog
    where bid = #{bid,jdbcType=INTEGER}
</select>

查询结果

1
2
3
4
5
6
7
8
9
<resultMap id="BaseResultMap" type="com.zhaojian.entity.Blog">
    <id column="bid" jdbcType="INTEGER" property="bid"/>
    <result column="author_id" jdbcType="INTEGER" property="authorId"/>
    <result column="title" jdbcType="VARCHAR" property="title"/>
    <result column="content" jdbcType="VARCHAR" property="content"/>
</resultMap>
<resultMap id="BaseResultMapAuthor" extends="BaseResultMap" type="com.zhaojian.entity.Blog">
    <association property="author" column="author_id" select="com.zhaojian.mapper.AuthorMapper.selectByPrimaryKey"/>
</resultMap>

虽然查询语句中只有一条SQL，但是由于结果集中包含有<association>标签，当我们调用该条查询时，MyBatis会帮我们去关联查询出指定的结果。

<association>标签解析

属性名 说明
property Java Bean中的属性名，关联查询结果映射该变量上
column 以查询语句中的这个字段的值，作为条件传递到关联查询语句中，多个字段则用英文逗号隔开
select 关联查询语句的ID，如果不在同一个namespace下，需要写上namespace

嵌套结果

查询语句

1
2
3
4
5
6
<select id="selectByPrimaryKeyAuthor2" parameterType="java.lang.Integer" resultMap="BaseResultMapAuthor2">
    select
    *
    from blog,author
    where blog.author_id = author.aid and bid = #{bid,jdbcType=INTEGER}
</select>

查询结果

1
2
3
4
5
6
<resultMap id="BaseResultMapAuthor2" extends="BaseResultMap" type="com.zhaojian.entity.Blog">
    <association property="author" javaType="com.zhaojian.entity.Author">
        <id column="aid" jdbcType="INTEGER" property="aid"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
    </association>
</resultMap>

1:N查询

查询语句

1
2
3
4
5
6
<select id="selectByPrimaryKeyPost" parameterType="java.lang.Integer" resultMap="BaseResultMapPost">
    select
    <include refid="Base_Column_List"/>
    from blog
    where bid = #{bid,jdbcType=INTEGER}
</select>

查询结果

1
2
3
4
<resultMap id="BaseResultMapPost" extends="BaseResultMap" type="com.zhaojian.entity.Blog">
    <collection property="postList" ofType="com.zhaojian.entity.Post" column="bid"
                select="com.zhaojian.mapper.PostMapper.selectByBlogId"/>
</resultMap>