程式師世界 >> 編程語言 >> JAVA編程 >> JAVA綜合教程 >> 最常用的動態sql語句梳理——分享給使用Mybatis的小伙伴們！，sqlmybatis

最常用的動態sql語句梳理——分享給使用Mybatis的小伙伴們！，sqlmybatis

編輯：JAVA綜合教程

最常用的動態sql語句梳理——分享給使用Mybatis的小伙伴們！，sqlmybatis

公司項目中一直使用Mybatis作為持久層框架，自然，動態sql寫得也比較多了，最常見的莫過於在查詢語句中使用if標簽來動態地改變過濾條件了。Mybatis的強大特性之一便是它的動態sql，免除了拼接sql帶來的各種麻煩，在開發項目的過程中，常見的和不常見的你都有可能會用到，現在就來把這一塊總結一下。

　　if
　　choose(when,otherwise)
　　trim(where,set)
　　foreach

<select id="getCategory" parameterType="EshopShopCategory" resultMap="EshopCategory" >
 　　SELECT * from CATEGORY t WHERE (1=1)
    <if test="shopCategoryId!=null">
        AND t.category_id  =#{shopCategoryId}
    </if>
    <if test="shopCategoryName!=null">
        AND t.CATEGORY_NAME  like '%${shopCategoryName}%'
    </if>
    <if test="shopId==null">
        AND t.shop_id=0 
    </if>
    ORDER BY SEQUENCE_NO
</select>

　　這通常用於多條件組合查詢。

<insert id="addCategory" parameterType="EshopCategory">
        insert into  CATEGORY(
        <if test="shopCategoryName!=null and shopCategoryName!='' ">
        　　shop_category_name,
        </if>
        <if test="shopId!=null and shopId!=''">
            shop_id,
        </if>
        ADD_TIME) 
        values(
        　　<if test="shopCategoryName!=null and shopCategoryName!=''">
            　　#{shopCategoryName,jdbcType=VARCHAR},
        　　</if>
        　　<if test="shopId!=null and shopId!=''">
            　　#{shopId,jdbcType=NUMERIC},
        　　</if>
        　　current_timestamp
       )    
</insert>

這適用於數據庫有默認值的時候可以不讓插入空值。

<update id="updateProductCategory" parameterType="EshopShopCategory" >
      update CATEGORY t set 
      <if test="shopCategoryName!=null">
          t.category_name=#{shopCategoryName,jdbcType=VARCHAR},
      </if>
      <if test="updateUser!=null">
          t.update_user=#{updateUser,jdbcType=VARCHAR} ,
      </if>
      t.update_time=current_timestamp
      where t.shop_category_id=#{shopCategoryId,jdbcType=NUMERIC}
</update>

這條動態地修改語句用得非常多，是因為很多時候我們在做修改操作時並不確定到底要修改哪些字段（哪些屬性），可能有的需要保存原值不變，這時候就可以做動態的sql，你新建一個對象後將需要修改的字段附上新值，這樣不用修改的屬性在這個對象上表現地是null，調用這個動態的sql時便可以完成部分修改。

choose,when,otherwise

適用場景：我們不想用到所有的條件語句，而只想從中擇其一二。針對這種情況，MyBatis 提供了 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>

到目前為止，我還沒有用到過choose，以後多留意。

trim,where,set

為了避免當if動態條件都不成立時，或者第一個條件不成立第二個條件成立時出現諸如"select * from TableA where"或者"select * from TableA and where"病態sql，我們可以使用trim,where,set標簽來解決。

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

在實際應用中，我通常是不寫where標簽，而在where關鍵字之後加上1=1的條件。即不管有無動態條件，總可以得到完整的sql：select * from A where 1=1。。。

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

foreach

foreach有時候在項目中會遇到，而且不止一次，用的時候是需要動點腦子的。通常用於篩選出在多個值組成的一個集合中或者排除多個值的場景，說白了，也就是我們之前寫sql時用到in、not in的時候：（集合是動態不確定的，需要從前台傳值過來）

<select id="selectNumInOrder" resultType="String">
     select count(0) from order a left join item b on a.ORDER_ID = b.ORDER_ID
     where a.STATUS in ('1','2','3','5') 
     <if test="list.size() > 0">
          and b.PHONE_NUM in 
          <foreach item="numberList" collection="list" open="(" separator="," close=")">
              #{numberList.num}
          </foreach>
     </if>
</select>

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

foreach 元素的功能是非常強大的，它允許你指定一個集合，聲明可以用在元素體內的集合項和索引變量。它也允許你指定開閉匹配的字符串以及在迭代中間放置分隔符。這個元素是很智能的，因此它不會偶然地附加多余的分隔符。

注意你可以將一個 List 實例或者數組作為參數對象傳給 MyBatis，當你這麼做的時候，MyBatis 會自動將它包裝在一個 Map 中並以名稱為鍵。List 實例將會以“list”作為鍵，而數組實例的鍵將是“array”。

以上是結合http://mybatis.github.io/mybatis-3/zh/getting-started.html及自己開發中比較常用的總結出來的，今天給梳理一下，分享給大家！