mybatis从使用到了解(七)_mybatis动态sql的妙用

动态SQL

在使用JDBC拼接SQL的时候，经常要确保不能完了必要的空格，对于的逗号，而mybatis的动态SQL则完美的解决了这些问题。本文只介绍利用mybatis的动态SQL解决常见的SQL拼接问题。
mybatis的动态sql包含一下内容：

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

解决where后SQL条件判断问题

<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中，如果三个if条件全为空，则最后拼接的sql为：

select * from student where

如果第一个为判断为空，则最后拼接的sql为：

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

上面拼接的两个sql语法都存在问题，只需要利用一点小技巧就能解决这个问题。如下，利用<where></where>标签，mybatis会自动处理上面的问题。

<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来解决

<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逗号问题

<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始终会多一个逗号。解决方案如下：

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