MYSQL常用动态SQL

• if

<select id="test">
        select * from tb_user where state = 1
        <if test="id != '' and id != 0">
          and user_id = #{id}
        </if>
    </select>

<select id="test11">
        select * from tb_user
        <where>
            <if test="state != '' and state != 0">
                and state = #{state}
            </if>
            <if test="id != '' and id != 0">
                and user_id = #{id}
            </if>
        </where>
    </select>

• choose
  如果传入的有sex，则添加sex这个条件，其他的不添加。如果sex和id都没有，则执行otherwise

    <select id="test22">
        select * from tb_user where state = 1
        <choose>
            <when test="sex != '' and sex != 0">
                and sex = #{sex}
            </when>
            <when test="id != '' and id != 0">
                and user_id = #{id}
            </when>
            <otherwise>
                 and age = 11
            </otherwise>
        </choose>
    </select>

• where
  他会帮助我们去除多余的and或者or

<select id="test11">
        select * from tb_user
        <where>
            <if test="state != '' and state != 0">
                and state = #{state}
            </if>
            <if test="id != '' and id != 0">
                and user_id = #{id}
            </if>
            <if test="ids != '' and ids != 0">
                or id = #{ids}
            </if>
        </where>
    </select>

• set

    <update id="update">
        update tb_user 
        <set>
            <if test="state != ''">
                state = #{state},
            </if>
            <if test="sex != ''">
                sex = #{sex},
            </if>
            <if test="age != ''">
                age = #{age},
            </if>
        </set>
        where id = #{id}
    </update>

• foreach

    <select id="text33">
        select * form tb_user 
        where id in
        <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </select>

• bind

    <select id="text44">
        <bind name="pattern" value="'%' + paramter.getName() + '%'"/>
        select * form tb_user
        where name like #{pattern}
    </select>