MyBatis 动态SQL标签汇总

我会带着你远行 2024-03-26 19:03 69阅读 0赞

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