MyBatis动态SQL

动态SQL

依据不同条件生成不同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>

test标签中写判断语句，标签内写需要添加的sql语句
where 1=1是为了配合后续sql语句扩展性，即加and就可以堆叠sql，只是暂时处理

where

为了解决像上述额外where 1=1无用判断语句的出现（数据量大时产生额外开销），where标签自动进行where、and操作

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

choose，when，otherwise

类似switch，case，default

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

和java的switch对应，只进行一种情况的查询

set

    <insert 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}
    </insert>

set标签会自动删除多余","

SQL片段

用于将公共SQL语句抽取出来

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

使用：

<include refid="ifSql"></include>

Foreach

用于遍历集合，循环叠加SQL语句

    <select id="queryBlogEach" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>

collection对应集合名称，item定义集合每个元素在sql中的名称，open为语句头，close为语句末尾，separator为每个item之间的分割