MyBatis動態(tài)sql語句(OGNL語法)

1、if

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

2、where

像上面的那種情況，如果where后面沒有條件，然后需要直接寫if判斷（開頭如果是 and / or 的話，會去除掉）

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

3、choose（when、otherwise） 

choose 相當(dāng)于 java 里面的 switch 語句。otherwise（其他情況）

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

4、trim

prefix：前綴prefixoverride：去掉第一個(gè)and或者是or

select * from test
<trim prefix="WHERE" prefixoverride="AND丨OR">
      <if test="a!=null and a!=' '">AND a=#{a}<if>
      <if test="b!=null and b!=' '">AND a=#{a}<if>
</trim>

5、set

set 元素主要是用在更新操作的時(shí)候，如果包含的語句是以逗號結(jié)束的話將會把該逗號忽略，如果set包含的內(nèi)容為空的話則會出錯(cuò)。

<update id="dynamicSetTest" parameterType="Blog">  
    update t_blog  
    <set>  
        <if test="title != null">  
            title = #{title},  
        </if>  
        <if test="content != null">  
            content = #{content},  
        </if>  
        <if test="owner != null">  
            owner = #{owner}  
        </if>  
    </set>  
    where id = #{id}  
</update> 

 6、foreach

foreach主要用在構(gòu)建in條件中

<select id="dynamicForeachTest" resultType="Blog">  
        select * from t_blog where id in  
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">  
            #{item}  
        </foreach>  
    </select>  

open separator close

相當(dāng)于是in （？，？，？）

 如果是個(gè)map怎么辦

<select id="dynamicForeach3Test" resultType="Blog">  
        select * from t_blog where title like "%"#{title}"%" and id in  
        <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">  
            #{item}  
        </foreach>  
    </select>  

collection對應(yīng)map的鍵，像這樣

List<Integer> ids = new ArrayList<Integer>();  
        ids.add(1);  
        ids.add(2);  
        ids.add(3);  
        ids.add(6);  
        ids.add(7);  
        ids.add(9);  
        Map<String, Object> params = new HashMap<String, Object>();  
        params.put("ids", ids);