Mybatis之动态sql

1. 当你在业务中有需要通过传过来的条件来进行sql查询的时候，之前的手动拼接既麻烦又容易出错，动态sql就可以根据场景动态的构建查询。

2. 常用的动态sql标签

   

3. if标签

   <select id="selectAllBlog" parameterType= "map" resultType="Blog">  
       select id,title,text from blog 
       where 1=1    
      <if test="title != null">        
          AND title like #{title}   
      </if>    
      <if test="text!= null">        
          AND text like #{text}    
      </if>
   </select>
   

4. where+if标签: “where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

   <select id="selectAllBlog" parameterType= "map" resultType="blog">
       select * from blog
       <where>
           <if test="title != null">
               title like concat('%' #{title} '%')
           </if>
           <if test="text != null">
               AND text like concat('%' #{text} '%')
           </if>
       </where>
   </select>
   

5. set标签

   <update id="updateBlog" parameterType= "map" >
       update blog
       <set>
           <if test="title != null">title=#{title},</if>
           <if test="text != null">text=#{text}</if>
       </set>
       where id=#{id}
   </update>
   

6. choose标签

   <select id="selectBlogByChoose" resultType="blog" parameterType="map">
         select * from blog
         <where>
             <choose>
                 <when test="id !='' and id != null">
                     id=#{id}
                 </when>
                 <when test="title !='' and title != null">
                     and title=#{title}
                 </when>
                 <otherwise>
                     and text=#{text}
                 </otherwise>
             </choose>
         </where>
     </select>
   

7. sql片段：把某一段简单的sql语句抽取出来，方便之后的代码复用

   <sql id="selectall">
       select  *  from  blog
   </sql>
   <select id="find" resultType="Student">
       <include refid="selectall"/>
   </select>
   

8. foreach标签

   //批量查询
   <select id="findBlog" resultType="blog" parameterType="map">
       <include refid="selectall"/> where id in
       <foreach item="ids" collection="array"  open="(" separator="," close=")">
       #{ids}
     </foreach>
   </select>