14mybatis映射文件2

动态sql

前面mybatis映射文件中的sql都比较简单，但是许多业务都是复杂的sql，sql都是动态变化的。

mybatis如何实现动态sql的呢？通过xml中的标签。

if标签

原来代码这样写：(如果username没有值，就会出错)

<select id="findByCondition" parameterType="com.domain.User">
    select * from user where id=#{id} and username=#{username}
</select>

用if标签

<select id="findByCondition" parameterType="com.domain.User">
    select * from user where 1=1
    <if test="id!=0">
        and id=#{id}
    </if>
    <if test="username!=null">
        and username=#{username}
    </if>
</select>

where标签

<select id="findByCondition" parameterType="com.domain.User">
    select * from user
    <where>
        <if test="id!=0">
            and id=#{id}
        </if>
        <if test="username!=null">
            and username=#{username}
        </if>
    </where>
</select>

foreach标签

<select id="findByIds" parameterType="list" resultType="com.domain.User">
    select * from user
    <where>
        <foreach collection="list" open="id in (" close=")" item="id" separator=",">
            #(id)
        </foreach>
    </where>
</select>

sql片段抽取

重复使用的sql代码可以抽取出来

<!--sql片段抽取-->
<sql id="selectUser">select * from user</sql>
<select id="findByCondition" parameterType="com.domain.User">
    <!--sql片段引用-->
    <include refid="selectUser"></include>
    <where>
        <if test="id!=0">
            and id=#{id}
        </if>
        <if test="username!=null">
            and username=#{username}
        </if>
    </where>
</select>