Mybatis入门（三）

各位小伙伴大家好，这一篇博客来介绍Mybatis强大的映射功能和如何启用缓存以及动态SQL。

resultMap

当数据库和类的字段名字一模一样的时候，可以选择使用resultType，但是现实情况没有那么简单，往往数据库字段名和类属性名不一样，而且还可能 存在一对多或者一对一查询。举个例子。

现在有三张表，第一张表Blog存储博客信息，同时存储了作者id，文章tag，第二张表author存储作者信息，第三张表存储tag。 此时我们可以构造一个resultMap，用于映射查询返回的值。

<resultMap id="detailedBlogResultMap" type="Blog">
    <result property="title" column="blog_title"/>
    <!--一对一关联查询，如一篇博客只有一个作者。association和collection都可以开启懒加载（需要数据的时候才去查询）-->
    <association property="author" javaType="Author" fetchType="lazy">
    <!--id表示主键字段，用result也可以，但是用id可以提高性能-->
        <id property="id" column="author_id"/>
        <!--返回的单列结果，property为类里面的名字，column为数据库列名字-->
        <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表示一对多查询，一篇博客关联多个tags-->
    <collection property="tags" ofType="Tag" fetchType="lazy">
          <id property="id" column="tag_id"/>
    </collection>
</resultMap>

<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,
       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 Tag T on PT.tag_id = T.id
  where B.id = #{id}
</select>

缓存

Mybatis默认开启一级缓存，如果要开启二级缓存，非常简单，直接在xml上加

<cache/>

动态SQL

if

类似java if语句

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

choose、when、otherwise

choose类似java switch语句

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

trim、where、set

where

where自动去除语句前的And

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

set

用于更新语句，自动去掉额外的逗号

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

trim

trim可以实现同功能的where，以下等价于where（自动去除语句前的And或者Or，使得查询语句不出错）

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

以下等价于set

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

foreach

用于遍历

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>