MyBatis的动态拼接SQL有哪些

1、IF

等同于Java的if。

<insert id="insertBlog"  parameterType="blog">
    insert into blog(`id`,`title`,`author`,`create_time`,`views`)
    values(#{id},#{title},#{author},#{createTime},#{views});
</insert>
<select id="selectBlog" parameterType="map" resultType="Blog">
    select * from blog where true
    <if test="views != null">
        and views = #{views}
    </if>
    <if test="title != null">
        and title = #{title}
    </if>
</select>

 Map<String,Object> map = new HashMap<String,Object>();
        map.put("title","mybatis");
        map.put("views",7777);
        List<Blog> blogs = mapper.selectBlog(map);
        for (Blog blog1 : blogs) {
            logger.info(blog1);
        }

2、Where

自动根据动态sql的位置去除and和or，当第一个条件不满足，第二个满足的情况下，存在and或or会自动去掉，第一个条件满足，后续的and或or则去除。

<select id="selectBlog" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <if test="views != null">
            views = #{views}
        </if>
        <if test="title != null">
            and title = #{title}
        </if>
    </where>
</select>

3、choose（when，otherwise）

等同于Java的switch。

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

            </otherwise>
        </choose>
    </where>
</select>

4、foreach

<select id="selectPartBlog" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <foreach collection="views" item="view" open="(" close=");" separator="or">
            views = #{view}
        </foreach>
    </where>
</select>

 Map<String,Object> map = new HashMap<String,Object>();
        List<Integer> lists = new ArrayList<>();
        lists.add(5555);
        lists.add(7777);
        map.put("views",lists);
        List<Blog> blogs = mapper.selectPartBlog(map);

        for (Blog blog1 : blogs) {
            logger.info(blog1);
        }

5、set

根据值会把set往前提，还可以自动清除多余的逗号。

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

注意：要注意传入的数据，修改的数据传入为空会报错