========================
MyBatis动态SQL之更多标签
========================

1 where

构建where子句，自动去除打头and/or。

<mapper namespace="cn.tedu.dynamic.dao.MemberDao">
    ...
    <select id="search" resultType="Member">
        select id, nick, gender, age, city from t_member
        <where>
            <if test="gender != null">and gender = #{gender}</if>
            <if test="minAge != null">and age &gt;= #{minAge}</if>
            <if test="maxAge != null">and age &lt;= #{maxAge}</if>
            <if test="city != null">and city = #{city}</if>
        </where>
    </select>
    ...
</mapper>

如果所有if均不满足，在where不起作用。

2 trim

追加前后缀，但不会自动去除打头and/or，除非通过prefixOverrides显式指明。

<mapper namespace="cn.tedu.dynamic.dao.MemberDao">
    ...
    <select id="search" resultType="Member">
        select id, nick, gender, age, city from t_member
        <trim prefix="where" prefixOverrides="and | or"
              suffix="order by age">
            <if test="gender != null">and gender = #{gender}</if>
            <if test="minAge != null">and age &gt;= #{minAge}</if>
            <if test="maxAge != null">and age &lt;= #{maxAge}</if>
            <if test="city != null">and city = #{city}</if>
        </trim>
    </select>
    ...
</mapper>

如果所有if均不满足，则trim不起作用，前后缀都没有。

3 foreach

遍历集合形式的查询参数。

public interface MemberDao {
    ...
    public List<Member> searchByCities(List<String> cities);
    ...
}

public class MemberDaoTest {
    ...
    @Test
    public void testSearchByCities() {
        MemberDao dao = MyBatisUtil.getMapper(MemberDao.class);
        List<String> cities = new ArrayList<>();
        cities.add("上海");
        cities.add("广州");
        List<Member> members = dao.searchByCities(cities);
        System.out.println(members);
    }
    ...
}

<mapper namespace="cn.tedu.dynamic.dao.MemberDao">
    ...
    <select id="searchByCities" resultType="Member">
        select id, nick, gender, age, city from t_member where city in
        <foreach collection="list" open="(" item="city" separator="," close=")">
            #{city}                ______|          |              |         |
        </foreach>                |   ______________|              |         |
    </select>                     |  |  ___________________________|         |
    ...                           |  | |      _______________________________|
</mapper>                         |  | |     |
                                  v  v v     v
select ... from ... where city in (上海, 广州)

运行测试用例。

例程：Dynamic