動(dòng)態(tài)SQL

在使用JDBC拼接SQL的時(shí)候，經(jīng)常要確保不能完了必要的空格，對(duì)于的逗號(hào)，而mybatis的動(dòng)態(tài)SQL則完美的解決了這些問(wèn)題。本文只介紹利用mybatis的動(dòng)態(tài)SQL解決常見的SQL拼接問(wèn)題。
mybatis的動(dòng)態(tài)sql包含一下內(nèi)容：

• if
• choose,when,otherwise
• trim,where,set
• foreach
• bind

解決where后SQL條件判斷問(wèn)題

<select id="selectByParam" parameterType="int" resultMap="studentResult">
    select * from student where
    <if test="studentId != null">
        student_id = #{studentId}
    </if>
    <if test="studentAge != null">
        and student_age = #{studentAge}
    </if>
    <if test="studentPhone != null">
        and student_phone = #{studentPhone}
    </if>
</select>

在上名的sql中，如果三個(gè)if條件全為空，則最后拼接的sql為：

select * from student where

如果第一個(gè)為判斷為空，則最后拼接的sql為：

select * from student where and student_age = #{studentAge} and student_phone = #{studentPhone}

上面拼接的兩個(gè)sql語(yǔ)法都存在問(wèn)題，只需要利用一點(diǎn)小技巧就能解決這個(gè)問(wèn)題。如下，利用<where></where>標(biāo)簽，mybatis會(huì)自動(dòng)處理上面的問(wèn)題。

<select id="selectByParam" parameterType="int" resultMap="studentResult">
    select  *   from student
    <where>
        <if test="studentId != null">
            student_id = #{studentId}
        </if>
        <if test="studentAge != null">
            and student_age = #{studentAge}
        </if>
        <if test="studentPhone != null">
            and student_phone = #{studentPhone}
        </if>
    </where>
</select>

也可以利用trim來(lái)解決

<select id="selectByParam" parameterType="int" resultMap="studentResult">
    select  *  from student
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="studentId != null">
            student_id = #{studentId}
        </if>
        <if test="studentAge != null">
            and student_age = #{studentAge}
        </if>
        <if test="studentPhone != null">
            and student_phone = #{studentPhone}
        </if>
    </trim>
</select>

利用<set>或<trim>解決update中set逗號(hào)問(wèn)題

<update id = "updateById">
    update student
    <if test="studentName != null">student_name = #{studentName},</if>
    <if test="studentAge != null">student_age = #{studentAge},</if>
    <if test="studentPhone != null">student_name = student_phone = #{studentPhone},</if>
    where student_id = #{studentId}
</update>

從上面可以看出，set始終會(huì)多一個(gè)逗號(hào)。解決方案如下：

<update id = "updateById">
    update student
    <set>
        <if test="studentName != null">student_name = #{studentName},</if>
        <if test="studentAge != null">student_age = #{studentAge},</if>
        <if test="studentPhone != null">student_name = student_phone = #{studentPhone},</if>
    </set>
    where student_id = #{studentId}
</update>

或者

<update id = "updateById">
    update student
    <trim prefix="SET" suffixOverrides=",">
        <if test="studentName != null">student_name = #{studentName},</if>
        <if test="studentAge != null">student_age = #{studentAge},</if>
        <if test="studentPhone != null">student_name = student_phone = #{studentPhone},</if>
    </trim>
    where student_id = #{studentId}
</update>

利用foreach查詢

<select id="selectByIds" parameterType="int" resultMap="studentResult">
    select
    <include refid="studentSql"/>
    from student where student_id in
    <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>