Mybatis Mapper xml映射配置知识总结

 写的很乱，可以参照着比较完整的看。

 

 

 1、  SQL映射文件中的顶级元素

   cache – 配置给定命名空间的缓存。

         <cache

                  eviction="FIFO"

                  flushInterval="60000"

                  size="512"

        readOnly="true"/>

 

   cache-ref – 从其他命名空间引用缓存配置。

          <cache-ref namespace="com.someone.application.data.SomeMapper"/>

 

   resultMap – 最复杂,也是最有力量的元素,用来描述如何从数据库结果集中来加 载你的对象。

          <resultMap id="userResultMap" type="User">

          <id property="id" column="user_id" />

          <result property="username" column="username"/>

           <result property="password" column="password"/>

          </resultMap>

             引用它的语句使用 resultMap 属性就行了(注意我们去掉了 resultType 属性)。比如:

          <select id="selectUsers" parameterType="int" resultMap="userResultMap">

                 select user_id, user_name, hashed_password

                 from some_table

                where id = #{id}

           </select>

 

          sql – 可以重用的 SQL 块,也可以被其他语句引用。

                  这个元素可以被用来定义可重用的 SQL 代码段,可以包含在其他语句中。比如:

                         <sql id="userColumns"> id,username,password </sql>

                  这个 SQL 片段可以被包含在其他语句中,例如:

                    <select id="selectUsers" parameterType="int" resultType="hashmap">

                                        select <include refid="userColumns"/>

                                        from some_table

                                       where id = #{id}

                   </select>

 

          insert – 映射插入语句

                      <insert id="insertAuthor" parameterType="domain.blog.Author">

                               insert into Author (id,username,password,email,bio)

                               values (#{id},#{username},#{password},#{email},#{bio})

                        </insert>

           update – 映射更新语句

                        <update id="updateAuthor" parameterType="domain.blog.Author">

                                update Author set

                                     username = #{username},

                                    password = #{password},

                                    email = #{email},

                                    bio = #{bio}

                          where id = #{id}

                    </update>

 

            delete – 映射删除语句

                   <delete id="deleteAuthor" parameterType="int">

                                delete from Author where id = #{id}

                     </delete>

 

              select – 映射查询语句

                 <select id="selectPerson" parameterType="int" resultType="java.util.HashMap">

                                 SELECT * FROM PERSON WHERE ID = #{id}

                       </select>

                          (与Ibaits不同的是参数注释方式由#id#变为#{id},parameterClass变为parameterType，resultClass 变为resultType)

2、 动态SQL

 

              if

                 在动态 SQL 中所做的最通用的事情是包含部分 where 字句的条件。比如:

                           <select id="findActiveBlogWithTitleLike"

                                  parameterType="Blog" resultType="Blog">

                                          SELECT * FROM BLOG

                                          WHERE state = ‘ACTIVE’

                                   <if test="title != null">

                                             AND title like #{title}

                                  </if>

                              </select>

 

            choose, when, otherwise

 

                  <select id="findActiveBlogLike"

                                  parameterType="Blog" 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

 

                 <select id="findActiveBlogLike"

                                  parameterType="Blog" 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>

 

                 如果 where 元素没有做出你想要的,你可以使用 trim 元素来自定义。比如,和 where 元素相等的

                 trim 元素是:

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

                                  ...

                                  </trim>

 

                      set 元素可以被用于动态包含更新的列,而不包含不需更新的。

                 比如:

                                  <update id="updateAuthorIfNecessary"

                                      parameterType="domain.blog.Author">

                                     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>

 

 

            foreach

                 另外一个动态 SQL 通用的必要操作是迭代一个集合, 通常是构建在 IN 条件中的。 比如:

                                  <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>