MyBatis 动态SQL标签汇总

if 标签

<select id="getEmpByCondition" resultType="com.xy.mybatis.dynamic.pojo.Emp">
    select * from emp
    where 1 = 1
    <if test="name != '' and name != null">
        and name = #{name}
    </if>
    <if test="age != '' and age != null">
        and age = #{age}
    </if>
</select>

where 标签

<select id="getEmpByCondition" resultType="com.xy.mybatis.dynamic.pojo.Emp">
    select * from emp
    <where>
        <if test="name != '' and name != null">
            name = #{name}
        </if>
        <if test="age != '' and age != null">
            and age = #{age}
        </if>
    </where>
</select>

trim 标签

<select id="getEmpByCondition" resultType="com.xy.mybatis.dynamic.pojo.Emp">
    select * from emp
    <trim prefix="where" suffixOverrides="and" prefixOverrides="and" suffix="order by id desc">
        <if test="name != '' and name != null">
            name = #{name}
        </if>
        <if test="age != '' and age != null">
            and age = #{age}
        </if>
    </trim>
</select>

choose、when、otherwise 标签

<select id="getEmpByChoose" resultType="com.xy.mybatis.dynamic.pojo.Emp">
    select * from emp
    <where>
        <choose>
            <when test="name != '' and name != null and age != '' and age != null">
                name = #{name} and age = #{age}
            </when>
            <when test="age != '' and age != null">
                age = #{age}
            </when>
            <when test="name != '' and name != null">
                name = #{name}
            </when>
            <otherwise>
                1 = 1
            </otherwise>
        </choose>
    </where>
</select>

foreach 标签

<select id="getEmpByIds" resultType="com.xy.mybatis.dynamic.pojo.Emp">
    select * from emp
    <foreach collection="ids" item="id" open="where id in (" close=")" separator=",">
        #{id}
    </foreach>
</select>

sql、include 标签

<sql id="base">
    select id, name, age
    from emp
</sql>

<select id="getEmpByIds" resultType="com.xy.mybatis.dynamic.pojo.Emp">
    <include refid="base"/>
    <foreach collection="ids" item="id" open="where id in (" close=")" separator=",">
        #{id}
    </foreach>
</select>