mybatis动态sql

if

    <select id="queryBlogif" parameterType="map" resultType="blog">
        select * from blog where 1=1
         <if test="title != null">
            and title = #{title};
         </if>
         <if test="author != null">
             and  author = #{author};
         </if>
    </select>

choose

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

trim(where,set)

    <select id="queryBlogif" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <if test="title != null">
                and title = #{title};
            </if>
            <if test="author != null">
                and  author = #{author};
            </if>
        </where>
    </select>

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

所谓的动态sqL,本质上就是sql语句，只是我们可以在sql层面，去执行一个逻辑代码

sql片段

有的时候，我们可能会将一些公共的部分抽取出来，方便使用

通过include标签的refid属性来调用sql片段

<select id="queryBlogif" parameterType="map" resultType="blog">
    select * from blog
    <where>
     <include refid="if-title-author"></include>
    </where>
</select>

设置id来让sql片段可以被调用，

<sql id="if-title-author">
    <if test="title != null">
        and title = #{title};
    </if>
    <if test="author != null">
        and  author = #{author};
    </if>
</sql>

注意事项：

• 最好基于单表来定义sql片段
• 最好不在sql片段存在where

Foreach

select * from User where 1=1 and (id = 1 or id=2 or id=3)

SELECT *
FROM POST P
WHERE ID in

#{item}

<!-- 就是拼接sql-->
<!--    select * from blog where 1=1 and (id = 1 or id=2 or id=3)
-->
    <select id="queryBlogForeach" resultType="blog" parameterType="map">
        select  * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>