动态SQL_if

一个查询的方法的Sql语句不一定是固定的。比如电商网站的查询商品，用户使用不同条件查询，Sql语句就会添加不同的查询条件。此时就需要在方法中使用动态Sql语句。

<if>标签内的Sql片段在满足条件后才会添加，用法为：<if test="条件">。例如：根据不同条件查询用户：

  1. 持久层接口添加方法

    1
    2
    // 用户通用查询
    List<User> findByCondition(User user);

  2. 映射文件添加标签

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    <select id="findByCondition" parameterType="User" resultType="User">
            select * from user
            <where>
                <if test="username != null and username.length() != 0">
                    and username like #{username}
                </if>
                <if test="sex != null and sex.length() != 0">
                    and sex = #{sex}
                </if>
                <if test="address != null and address.length() != 0">
                    and address = #{address}
                </if>
            </where>
    </select>

  3. if中的条件不能使用&&/||，而应该使用and/or

  4. if中的条件可以直接通过属性名获取参数POJO的属性值，并且该值可以调用方法。

  5. where后为什么要加1=1？

    任意条件都可能拼接到Sql中。如果有多个条件，从第二个条件开始前都需要加And关键字。加上1=1这个永久成立的条件，就不需要考虑后面的条件哪个是第一个条件，后面的条件前都加And关键字即可。

动态SQL_where

<where>可以代替sql中的where 1=1 和第一个and，更符合程序员的开发习惯，使用<where>后的映射文件如下：

1
2
3
4
5
6
7
8
9
10
11
<select id="findByCondition" resultType="User" parameterType="User">
    select * from user
    <where>
        <if test="username != null and username.length() != 0">
            username like #{username}
        </if>
        <if test="sex != null and sex.length() != 0">
            and sex = #{sex}
        </if>
    </where>
</select>

动态SQL_set

<set>标签用在update语句中。借助<if>，可以只对有具体值的字段进行更新。<set>会自动添加set关键字，并去掉最后一个if语句中多余的逗号。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<update id="update" parameterType="User">
    update user
    <set>
        <if test="username != null and username.length() > 0">
            username = #{username},
        </if>
        <if test="sex != null and sex.length() > 0">
            sex = #{sex},
        </if>
    </set>
    <where>
        id = #{id}
    </where>
</update>

动态SQL_choose、when、otherwise

这些标签表示多条件分支，类似JAVA中的switch...case。<choose>类似switch，<when>类似case，<otherwise>类似default，用法如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="findByCondition" resultType="User" parameterType="User">
    select * from user
    <where>
        <choose>
            <when test="username.length() &lt; 5">
                username like #{username}
            </when>
            <when test="username.length() &lt; 10">
                username = #{username}
            </when>
            <otherwise>
                id = 1
            </otherwise>
        </choose>
    </where>
</select>

这段代码的含义为：用户名<5时使用模糊查询，用户名>=5并且<10时使用精确查询，否则查询id为1的用户

动态SQL_foreach

<foreach>类似JAVA中的for循环，可以遍历集合或数组。<foreach>有如下属性：

  • collection：遍历的对象类型
  • open：开始的sql语句
  • close：结束的sql语句
  • separator：遍历每项间的分隔符
  • item：表示本次遍历获取的元素，遍历List、Set、数组时表示每项元素，遍历map时表示键值对的值。
  • index：遍历List、数组时表示遍历的索引，遍历map时表示键值对的键。

遍历数组

我们使用<foreach>遍历数组进行批量删除。

  1. 持久层接口添加方法

    1
    void deleteBatch(int[] ids);

  2. 映射文件添加标签

    1
    2
    3
    4
    5
    6
    7
    8
    <delete id="deleteBatch" parameterType="int">
        delete from user
        <where>
            <foreach open="id in(" close=")" separator="," collection="array" item="id">
                #{id}
            </foreach>
        </where>
    S</delete>

遍历Map

我们使用<foreach>遍历Map进行多条件查询。

  1. 持久层接口添加方法

    1
    2
    3
    4
    5
    6
    /**
     * 多条件查询
     * @param map 查询的条件键值对 键：属性名 值：属性值
     * @return
     */
    List<User> findUser(@Param("queryMap") Map<String,Object> map);

  2. 映射文件添加标签

    1
    2
    3
    4
    5
    6
    7
    8
    <select id="findUser" parameterType="map" resultType="User">
        select * from user
        <where>
            <foreach collection="queryMap" separator="and" index="key" item="value">
                ${key} = #{value}
            </foreach>
        </where>
    </select>