MyBatis动态SQL语句

mybatis 的动态sql语句是基于OGNL表达式的。可以方便的在 sql 语句中实现某些逻辑. 总体说来mybatis 动态SQL 语句主要有以下几类:
1. if 语句 (简单的条件判断)
2. choose (when,otherwize) ,相当于java 语言中的 switch ,与 jstl 中的choose 很类似.
3. trim (对包含的内容加上 prefix,或者 suffix 等，前缀，后缀)
4. where (主要是用来简化sql语句中where条件判断的，能智能的处理 and or ,不必担心多余导致语法错误)
5. set (主要用于更新时)
6. foreach (在实现 mybatis in 语句查询时特别有用)

if
动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分。比如:

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

这条语句提供了一个可选的文本查找类型的功能。如果没有传入“title”，那么所有处于“ACTIVE”状态的BLOG都会返回；反之若传入了“title”，那么就会把模糊查找“title”内容的BLOG结果返回(就这个例子而言，细心的读者会发现其中的参数值是可以包含一些掩码或通配符的)。
如果想可选地通过“title”和“author”两个条件搜索该怎么办呢？首先，改变语句的名称让它更具实际意义；然后只要加入另一个条件即可。

<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
有些时候，我们不想用到所有的条件语句，而只想从中择其一二。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

还是上面的例子，但是这次变为提供了“title”就按“title”查找，提供了“author”就按“author”查找，若两者都没有提供，就返回所有符合条件的BLOG(实际情况可能是由管理员按一定策略选出BLOG列表，而不是返回大量无意义的随机结果)。

<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
前面几个例子已经合宜地解决了一个臭名昭著的动态 SQL 问题。现在考虑回到“if”示例，这次我们将“ACTIVE = 1”也设置成动态的条件，看看会发生什么。

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

<insert id="insertSelective" parameterType="com.shengya.service.bean.JobResume">
        insert into job_resume
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="uid != null">
                uid,
            </if>
            <if test="uname != null">
                uname,
            </if>
            <if test="telephone != null">
                telephone,
            </if>
            <if test="resumeType != null">
                resume_type,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="isJobIntension != null">
                is_job_intension,
            </if>
            <if test="evaluation != null">
                evaluation,
            </if>
            <if test="lidLables != null">
                lid_lables,
            </if>
            <if test="professionalPhoto != null">
                professional_photo,
            </if>
            <if test="certificatePhoto != null">
                certificate_photo,
            </if>
            <if test="companyPhoto != null">
                company_photo,
            </if>
            <if test="personMovie != null">
                person_movie,
            </if>
            <if test="isOpen != null">
                is_open,
            </if>
            <if test="isRecommend != null">
                is_recommend,
            </if>
            <if test="isPriority != null">
                is_priority,
            </if>
            <if test="lastRefreshDate != null">
                last_refresh_date,
            </if>
            <if test="version != null">
                version,
            </if>
            <if test="createUser != null">
                create_user,
            </if>
            <if test="createDate != null">
                create_date,
            </if>
            <if test="isDelete != null">
                is_delete,
            </if>
            <if test="resumeLanguageType != null">
                resume_language_type,
            </if>
            <if test="position != null">
                position,
            </if>
            <if test="sourceType != null">
                source_type,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
#{id,jdbcType=INTEGER},
            </if>
            <if test="uid != null">
#{uid,jdbcType=INTEGER},
            </if>
            <if test="uname != null">
#{uname,jdbcType=VARCHAR},
            </if>
            <if test="telephone != null">
#{telephone,jdbcType=VARCHAR},
            </if>
            <if test="resumeType != null">
#{resumeType,jdbcType=INTEGER},
            </if>
            <if test="name != null">
#{name,jdbcType=VARCHAR},
            </if>
            <if test="isJobIntension != null">
#{isJobIntension,jdbcType=INTEGER},
            </if>
            <if test="evaluation != null">
#{evaluation,jdbcType=VARCHAR},
            </if>
            <if test="lidLables != null">
#{lidLables,jdbcType=VARCHAR},
            </if>
            <if test="professionalPhoto != null">
#{professionalPhoto,jdbcType=VARCHAR},
            </if>
            <if test="certificatePhoto != null">
#{certificatePhoto,jdbcType=VARCHAR},
            </if>
            <if test="companyPhoto != null">
#{companyPhoto,jdbcType=VARCHAR},
            </if>
            <if test="personMovie != null">
#{personMovie,jdbcType=VARCHAR},
            </if>
            <if test="isOpen != null">
#{isOpen,jdbcType=INTEGER},
            </if>
            <if test="isRecommend != null">
#{isRecommend,jdbcType=INTEGER},
            </if>
            <if test="isPriority != null">
#{isPriority,jdbcType=INTEGER},
            </if>
            <if test="lastRefreshDate != null">
#{lastRefreshDate,jdbcType=TIMESTAMP},
            </if>
            <if test="version != null">
#{version,jdbcType=VARCHAR},
            </if>
            <if test="createUser != null">
#{createUser,jdbcType=INTEGER},
            </if>
            <if test="createDate != null">
#{createDate,jdbcType=TIMESTAMP},
            </if>
            <if test="isDelete != null">
#{isDelete,jdbcType=INTEGER},
            </if>
            <if test="resumeLanguageType != null">
#{resumeLanguageType,jdbcType=INTEGER},
            </if>
            <if test="position != null">
#{position,jdbcType=VARCHAR},
            </if>
            <if test="sourceType != null">
#{sourceType,jdbcType=INTEGER},
            </if>
        </trim>
    </insert>

prefixOverrides 属性会忽略通过管道分隔的文本序列(注意此例中的空格也是必要的)。它带来的结果就是所有在 prefixOverrides 属性中指定的内容将被移除，并且插入 prefix 属性中指定的内容。

类似的用于动态更新语句的解决方案叫做 set。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>

 <update id="updateByPrimaryKeySelective" parameterType="com.shengya.service.bean.JobResume">
        update job_resume
        <set>
            <if test="uid != null">
                uid = #{uid,jdbcType=INTEGER},
            </if>
            <if test="uname != null">
                uname = #{uname,jdbcType=VARCHAR},
            </if>
            <if test="telephone != null">
                telephone = #{telephone,jdbcType=VARCHAR},
            </if>
            <if test="resumeType != null">
                resume_type = #{resumeType,jdbcType=INTEGER},
            </if>
            <if test="name != null">
                name = #{name,jdbcType=VARCHAR},
            </if>
            <if test="isJobIntension != null">
                is_job_intension = #{isJobIntension,jdbcType=INTEGER},
            </if>
            <if test="evaluation != null">
                evaluation = #{evaluation,jdbcType=VARCHAR},
            </if>
            <if test="lidLables != null">
                lid_lables = #{lidLables,jdbcType=VARCHAR},
            </if>
            <if test="professionalPhoto != null">
                professional_photo = #{professionalPhoto,jdbcType=VARCHAR},
            </if>
            <if test="certificatePhoto != null">
                certificate_photo = #{certificatePhoto,jdbcType=VARCHAR},
            </if>
            <if test="companyPhoto != null">
                company_photo = #{companyPhoto,jdbcType=VARCHAR},
            </if>
            <if test="personMovie != null">
                person_movie = #{personMovie,jdbcType=VARCHAR},
            </if>
            <if test="isOpen != null">
                is_open = #{isOpen,jdbcType=INTEGER},
            </if>
            <if test="isRecommend != null">
                is_recommend = #{isRecommend,jdbcType=INTEGER},
            </if>
            <if test="isPriority != null">
                is_priority = #{isPriority,jdbcType=INTEGER},
            </if>
            <if test="lastRefreshDate != null">
                last_refresh_date = #{lastRefreshDate,jdbcType=TIMESTAMP},
            </if>
            <if test="version != null">
                version = #{version,jdbcType=VARCHAR},
            </if>
            <if test="createUser != null">
                create_user = #{createUser,jdbcType=INTEGER},
            </if>
            <if test="createDate != null">
                create_date = #{createDate,jdbcType=TIMESTAMP},
            </if>
            <if test="isDelete != null">
                is_delete = #{isDelete,jdbcType=INTEGER},
            </if>
            <if test="resumeLanguageType != null">
                resume_language_type = #{resumeLanguageType,jdbcType=INTEGER},
            </if>
            <if test="position != null">
                position = #{position,jdbcType=VARCHAR},
            </if>
            <if test="sourceType != null">
                source_type = #{sourceType,jdbcType=INTEGER},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

这里，set 元素会动态前置 SET 关键字，同时也会消除无关的逗号，因为用了条件语句之后很可能就会在生成的赋值语句的后面留下这些逗号。

若你对等价的自定义 trim 元素的样子感兴趣，那这就应该是它的真面目：

<trim prefix="SET" suffixOverrides=","> ... </trim>
注意这里我们忽略的是后缀中的值，而又一次附加了前缀中的值

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>

<select id="selectCompanyInputResumeList" resultMap="ResumeRecordMap">
        SELECT t1.id id,t1.name name, t1.create_date createDate ,
        t2.position position ,t2.industry industry,t2.region region,t2.job_status jobStatus,
        t3.name userName,t3.sex sex,t3.birthday birthday,t3.join_job_date joinJobDate,t3.student_no studentNo,
        t4.inauguration inauguration,t4.banner_img bannerImg
        FROM job_resume t1
        LEFT JOIN job_intension t2 ON t2.rid=t1.id
        LEFT JOIN  job_user_info t3 ON t3.uid=t1.uid
        LEFT JOIN  user_info t4 ON t4.uid=t1.uid
        WHERE t1.create_user IN
        <if test="ids!=null">
            <foreach collection="ids" item="item" open="(" close=")" separator=",">
                ${item}
            </foreach>
        </if>
        AND t1.is_priority = 1 and t1.is_delete = 0
        ORDER BY t1.create_date desc
        <if test="pageInfo!=null">
            limit ${pageInfo.startIndex} ,${pageInfo.pagesize}
        </if>
    </select>

dao:
 public List<ResumeRecord> selectCompanyInputResumeList(List list,PageInfo pageInfo) {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("ids", list);
        map.put("pageInfo",pageInfo);
return getSqlSession().selectList(getMapperNameSpace() + ".selectCompanyInputResumeList",map);
    }

foreach 元素的功能是非常强大的，它允许你指定一个集合，声明可以用在元素体内的集合项和索引变量。它也允许你指定开闭匹配的字符串以及在迭代中间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。

注意 你可以将一个 List 实例或者数组作为参数对象传给 MyBatis，当你这么做的时候，MyBatis 会自动将它包装在一个 Map 中并以名称为键。List 实例将会以“list”作为键，而数组实例的键将是“array”。

到此我们已经完成了涉及 XML 配置文件和 XML 映射文件的讨论。下一部分将详细探讨 Java API，这样才能从已创建的映射中获取最大利益。