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>