Mybatis的动态SQL

MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作，并动态拼接成完整的SQL之后再执行，以达到SQL复用、简化编程的效果。

< sql >  封装相同的sql代码，使用的时候利用include引入

<mapper namespace="com.mybatis.part2.dynamic.BookDao">
    <sql id="BOOKS_FIELD"> <!-- 定义SQL片段 -->
        SELECT id,name,author,publish,sort
    </sql>

    <select id="selectBookByCondition" resultType="com.mybatis.part2.dynamic.Book">
				<include refid="BOOKS_FIELD" /> <!-- 通过ID引用SQL片段 -->
        FROM t_books
    </select>
</mapper>

<where> 会自动添加where关键字，并删除多余的and关键字

<select id="selectBookByCondition" resultType="com.mybatis.part2.dynamic.Book">
    SELECT id , name , author , publish , sort
    FROM t_books
    <where> 
        <if test="id != null"> <!-- WHERE，会自动忽略前后缀（如：and | or） -->
            id = #{id}
        </if>

        <if test="name != null">
            and name = #{name}
        </if>

        <if test="author != null">
            and author = #{author}
        </if>

        <if test="publish != null">
            and publish = #{publish}
        </if>

        <if test="sort != null">
            and sort = #{sort}
        </if>
    </where>
</select>

< set > 会自动添加set关键字，并删除多余的“，”

<update id="updateBookByCondition">
    UPDATE t_books
    <set>
        <if test="name != null"><!-- where子句中满足条件的if，会自动忽略后缀（如：,） -->
            name = #{name} ,
        </if>

        <if test="author != null">
            author = #{author} ,
        </if>

        <if test="publish != null">
            publish = #{publish} ,
        </if>

        <if test="sort != null">
            sort = #{sort} ,
        </if>
    </set>
    WHERE id = #{id}
</update>

< trim >

< trim prefix="" suffix="" prefixOverrides="" suffixOverrides="" >代替< where > 、< set >

可以利用该标签灵活选择在前还是后加入where还是set关键字，并设置忽略什么字符串

<select id="selectBookByCondition" resultType="com.mybatis.day2.dynamic.Book">
		SELECT id,name,author,publish,sort
    FROM t_books
    <trim prefix="WHERE" prefixOverrides="AND|OR"> <!-- 增加WHERE前缀，自动忽略前缀 -->
        <if test="id != null">
            and id = #{id}
        </if>

        <if test="name != null">
            and name = #{name}
        </if>

        <if test="author != null">
            and author = #{author}
        </if>

        <if test="publish != null">
            and publish = #{publish}
        </if>

        <if test="sort != null">
            and sort = #{sort}
        </if>
		</trim>
</select>

<update id="updateBookByCondition">
		UPDATE t_books
		<trim prefix="SET" suffixOverrides=","> <!-- 增加SET前缀，自动忽略后缀 -->
				<if test="name != null">
						name = #{name} ,
				</if>

				<if test="author != null">
						author = #{author} ,
				</if>

				<if test="publish != null">
						publish = #{publish} ,
				</if>

				<if test="sort != null">
						sort = #{sort}
				</if>
    </trim>
		WHERE id = #{id}
</update>

< foreach >  用于插入批处理的遍历读取

int batchItems(List<Item> list);

    <insert id="batchItems">
        insert into item(o_id,p_id,i_count,i_num) values
            <foreach collection="list" item="item" separator="," >
                (#{item.o_id},#{item.p_id},#{item.i_count},#{item.i_num})
            </foreach>
    </insert>

参数	描述	取值
collection	容器类型	list、array、map
open	起始符	(
close	结束符	)
separator	分隔符	,
index	下标号	从0开始，依次递增
item	当前项	任意名称（循环中通过 #{任意名称} 表达式访问）