mybatis的动态SQL

动态SQL

使⽤动态 SQL 可简化代码的开发，减少开发者的工作量，程序可以⾃动根据业务参数来决定 SQL 的组成。

if 标签

实体的类型全部为包装类型

<select id="findByAccount" parameterType="com.southwind.entity.Account" resultType="com.southwind.entity.Account">
    select * from t_account where
    <if test="id!=null">
        id = #{id}
    </if>
    <if test="username!=null"> 
        and username = #{username} 
    </if>
    <if test="password!=null"> 
        and password = #{password} 
    </if>
    <if test="age!=null">
        and age = #{age}
    </if>
</select>

where 标签

使用了where标签，就不需要手动跟and了

<select id="findByAccount" parameterType="com.southwind.entity.Account" resultType="com.southwind.entity.Account">
    select * from t_account
    <where>
        <if test="id!=null">
            id = #{id}
        </if>
        <if test="username!=null">
            and username = #{username}
        </if>
        <if test="password!=null">
            and password = #{password}
        </if>
        <if test="age!=null">
            and age = #{age}
        </if>
    </where>
</select>

choose 、when 标签

<select id="findByAccount" parameterType="com.southwind.entity.Account" resultType="com.southwind.entity.Account">
    select * from t_account
    <where>
        <choose>
            <when test="id!=null">
                id = #{id} 
            </when>
            <when test="username!=null"> 
                username = #{username} 
            </when>
            <when test="password!=null"> 
                password = #{password} 
            </when>
            <when test="age!=null"> 
                age = #{age} 
            </when>
        </choose>
    </where>
</select>

trim 标签

1. trim 标签中的 prefix 和 suffix 属性会被⽤用于⽣生成实际的 SQL 语句句，会和标签内部的语句句进⾏行行拼接.
2. 如果语句前后出现了了 prefixOverrides 或者 suffixOverrides 属性中指定的值，MyBatis 框架会自动将其删除。

<select id="findByAccount" parameterType="com.southwind.entity.Account" resultType="com.southwind.entity.Account">
    select * from t_account
    <trim prefix="where" prefixOverrides="and">
        <if test="id!=0">
            id = #{id}
        </if>
        <if test="username!=null">
            and username = #{username}
        </if>
        <if test="password!=null">
            and password = #{password}
        </if>
        <if test="age!=0">
            and age = #{age}
        </if>
    </trim>
</select>

set 标签

用于 update 操作，会自动根据参数选择⽣生成 SQL 语句

<update id="update" parameterType="com.southwind.entity.Account"> 
    update t_account
    <set>
        <if test="username!=null">
            username = #{username},
        </if>
        <if test="password!=null"> 
            password = #{password}, 
        </if>
        <if test="age!=0"> 
            age = #{age} 
        </if>
    </set>
    where id = #{id}
</update>

foreach 标签

foreach 标签可以迭代生成⼀系列值，这个标签主要用于 SQL 的 in 语句

<select id="findByIds" parameterType="com.southwind.entity.Account" resultType="com.southwind.entity.Account">
      select * from t_account
      <where>
          <foreach collection="ids" open="id in (" close=")" item="id" separator=",">
              #{id}
          </foreach>
      </where>
</select>