Mybatis 动态 SQL

作者：範宗雲来源：原创发布时间：2015-05-06 归档：mybatis

环境： JDK 7 Mybatis 3.2.7 Maven 3 Junit 4.11 Spring 4.1.5 Eclipse Luna

person 表

            <select id="queryBy" parameterType="map" resultType="Person">
              select * from person where sex = #{sex}
              <if test="address != null">
                and address = #{address}
              </if>
              <if test="active != null">
                and active = #{active}
              </if>
            </select>

如果没有传入 address 和 active

DEBUG [main] - ==> Preparing: select * from person where sex = ?
DEBUG [main] - ==> Parameters: 女(String)
DEBUG [main] - <== Total: 2
1 店小二女 20 dianxiaoer@126.com 17702084228 广东茂名 false
3 店小四女 22 dianxiaosi@yeah.net 11810011111 广东广州 true

如果同时传入 address 和 active

DEBUG [main] - ==> Preparing: select * from person where sex = ? and address = ? and active = ?
DEBUG [main] - ==> Parameters: 女(String), 广东茂名(String), false(Boolean)
DEBUG [main] - <== Total: 1
1 店小二女 20 dianxiaoer@126.com 17702084228 广东茂名 false

如果传入 address 和 active 其中的一个 ( 如 address )

DEBUG [main] - ==> Preparing: select * from person where sex = ? and address = ?
DEBUG [main] - ==> Parameters: 女(String), 广东茂名(String)
DEBUG [main] - <== Total: 1
1 店小二女 20 dianxiaoer@126.com 17702084228 广东茂名 false

可以知道, <if> 实现的是 if 逻辑。凡是符合条件的语句都会被执行。但 <if> 不能实现 if/else 的逻辑。

choose-when-otherwise

            <select id="findBy" parameterType="map" resultType="Person">
              select * from person where age > #{age}
              <choose>
                <when test="sex != null">
                  and sex = #{sex}
                </when>
                <when test="address != null">
                  and address = #{address}
                </when>
                <otherwise>
                  and active = true
                </otherwise>
              </choose>
            </select>

如果没有传入 sex 和 address

DEBUG [main] - ==> Preparing: select * from person where age > ? and active = true
DEBUG [main] - ==> Parameters: 18(Integer)
DEBUG [main] - <== Total: 2
2 店小三男 21 dianxiaosan@163.com 13800138000 广东茂名 true
3 店小四女 22 dianxiaosi@yeah.net 11810011111 广东广州 true

如果同时传入 sex 和 address

DEBUG [main] - ==> Preparing: select * from person where age > ? and sex = ?
DEBUG [main] - ==> Parameters: 18(Integer), 女(String)
DEBUG [main] - <== Total: 2
1 店小二女 20 dianxiaoer@126.com 17702084228 广东茂名 false
3 店小四女 22 dianxiaosi@yeah.net 11810011111 广东广州 true

如果传入 sex 和 address 其中的一个 ( 如 address )

DEBUG [main] - ==> Preparing: select * from person where age > ? and address = ?
DEBUG [main] - ==> Parameters: 18(Integer), 广东茂名(String)
DEBUG [main] - <== Total: 2
1 店小二女 20 dianxiaoer@126.com 17702084228 广东茂名 false
2 店小三男 21 dianxiaosan@163.com 13800138000 广东茂名 true

choose-when-otherwise 实现的是 if、else if、else 的逻辑。

where

            <select id="selectBy" parameterType="map" resultType="Person">
              select * from person
              <where>
                <if test="id != null">
                  id = #{id}
                </if>
                <if test="age != null">
                  and age = #{age}
                </if>
                <if test="sex != null">
                  and sex = #{sex}
                </if>
                <if test="name != null">
                  and name = #{name}
                </if>
                <if test="email != null">
                  and email = #{email}
                </if>
                <if test="phone != null">
                  and phone = #{phone}
                </if>
                <if test="address != null">
                  and address = #{address}
                </if>
                <if test="active != null">
                  and active = #{active}
                </if>
              </where>
            </select>

如果不传任何参数

DEBUG [main] - ==> Preparing: select * from person
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 3
1 店小二女 20 dianxiaoer@126.com 17702084228 广东茂名 false
2 店小三男 21 dianxiaosan@163.com 13800138000 广东茂名 true
3 店小四女 22 dianxiaosi@yeah.net 11810011111 广东广州 true

如果仅传 id 参数

DEBUG [main] - ==> Preparing: select * from person WHERE id = ?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 1
1 店小二女 20 dianxiaoer@126.com 17702084228 广东茂名 false

如果传非 id 参数

DEBUG [main] - ==> Preparing: select * from person WHERE active = ?
DEBUG [main] - ==> Parameters: true(Boolean)
DEBUG [main] - <== Total: 2
2 店小三男 21 dianxiaosan@163.com 13800138000 广东茂名 true
3 店小四女 22 dianxiaosi@yeah.net 11810011111 广东广州 true

可以看到, 只有在一个或以上满足 if 条件的时候, where 子句才会被插入, 并且, <where> 会自动的正确的去掉 where 子句中多余的 and 或 or 条件。
你还可以这样做：

            <sql id="sqlWhere">
              <where>
                <if test="id != null">
                  id = #{id}
                </if>
                <if test="age != null">
                  and age = #{age}
                </if>
                <if test="sex != null">
                  and sex = #{sex}
                </if>
                <if test="name != null">
                  and name = #{name}
                </if>
                <if test="email != null">
                  and email = #{email}
                </if>
                <if test="phone != null">
                  and phone = #{phone}
                </if>
                <if test="address != null">
                  and address = #{address}
                </if>
                <if test="active != null">
                  and active = #{active}
                </if>
              </where>
            </sql>
            
            <select id="selectBy" parameterType="map" resultType="Person">
              select * from person <include refid="sqlWhere" />
            </select>

将 where 单独抽出来, 这样可以达到重用的目的, 在需要使用的地方, 用 <include> 引进来即可。

set

            <update id="update" parameterType="Person">
              update person
              <set>
                <if test="age != null">
                  age = #{age},
                </if>
                <if test="sex != null">
                  sex = #{sex},
                </if>
                <if test="name != null">
                  name = #{name},
                </if>
                <if test="email != null">
                  email = #{email},
                </if>
                <if test="phone != null">
                  phone = #{phone},
                </if>
                <if test="address != null">
                  address = #{address},
                </if>
                <if test="active != null">
                  active = #{active}
                </if>
              </set>
              where id = #{id}
            </update>

与 <where> 相类似, <set> 可以自动的正确的清除 set 子句中无关的 "," 号。当然, 你也可以像 <where> 那样, 将它单独抽出来：

            <sql id="sqlSet">
              <set>
                <if test="age != null">
                  age = #{age},
                </if>
                <if test="sex != null">
                  sex = #{sex},
                </if>
                <if test="name != null">
                  name = #{name},
                </if>
                <if test="email != null">
                  email = #{email},
                </if>
                <if test="phone != null">
                  phone = #{phone},
                </if>
                <if test="address != null">
                  address = #{address},
                </if>
                <if test="active != null">
                  active = #{active}
                </if>
              </set>
            </sql>
            
            <update id="update" parameterType="Person">
              update person <include refid="sqlSet" /> where id = #{id}
            </update>

这样可以达到重用 sql 子句的目的。

foreach

            <select id="search" parameterType="list" resultType="Person">
              select * from person where id in
              <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item}
              </foreach>
            </select>

<foreach> 用于遍历一个集合。collection 为传进来的集合参数, item 定义迭代的变量的名称, open 和 close 定义开闭的字符, separator 定义迭代之间的分隔符。若传进参数 [1, 2]

DEBUG [main] - ==> Preparing: select * from person where id in ( ? , ? )
DEBUG [main] - ==> Parameters: 1(Integer), 2(Integer)
DEBUG [main] - <== Total: 2
1 店小二女 20 dianxiaoer@126.com 17702084228 广东茂名 false
2 店小三男 21 dianxiaosan@163.com 13800138000 广东茂名 true