程序笔记：MyBatis 动态SQL和模糊查询

之前用mybatis在xml中写sql语句用到过<if>标签，但是不知道这是动态SQL（尴尬），最近空闲整理一下常用的动态SQL。好记性不如烂笔头
以图 t_user 表为例：

1. if 语句

根据 userNo 和 name 来查询数据。如果userNo为空，那么将只根据name来查询；反之只根据userNo来查询.

<select id="getUserByUsernoAndName" returnMap="BaseResultMap" parameterType="com.xxb.domain.UserPO">
    <!-- 这里和普通的sql 查询语句差不多，对于只有一个参数，后面的 #{id}表示占位符，里面不一定要写id,
        写啥都可以，但是不要空着，如果有多个参数则必须写pojo类里面的属性 -->
    select * from t_user where userNo=#{userNo} and name=#{name}
</select>

上面的查询语句，我们可以发现，如果 #{userNo} 为空，那么查询结果也是空，如何解决这个问题呢？使用 if 来判断

<select id="getUserByUsernoAndName" returnMap="BaseResultMap" parameterType="com.xxb.domain.UserPO">
    select * from t_user where 1=1
        <if test="userNo != null">
           and userNo=#{userNo}
        </if>

        <if test="name != null">
           and name=#{name}
        </if>
</select>

但是加一个 1=1作为条件是不是很奇怪，用where 语句来解决这个问题

2. if+where 语句

<select id="getUserByUsernoAndName" returnMap="BaseResultMap" parameterType="com.xxb.domain.UserPO">
    select * from t_user 
    <where>
        <if test="userNo != null">
           and userNo=#{userNo}
        </if>

        <if test="name != null">
           and name=#{name}
        </if>
    </where>
</select>

这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

3. if+set 语句

<update id="updateUserById" parameterType="com.xxb.domain.UserPO">
    update t_user u
    <set>
        <if test="userNo != null">
            u.userNo=#{userNo},
        </if>

        <if test="name != null">
           u.name=#{name}
        </if>
    </set>
    where id=#{id}
</update>

4. choose(when,otherwise) 语句

有时候，我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch 语句

<select id="getUserByChoose" returnMap="BaseResultMap" parameterType="com.xxb.domain.UserPO">
    select * from t_user 
    <where>
        <choose>
            <when test="id != null">
               and id=#{id}
            </when>

            <when test="userNo != null">
               and userNo=#{userNo}
            </when>

            <otherwise>
                  and name=#{name}
            </otherwise>
        </choose>
    </where>
</select>

这里我们有三个条件，id,userNo,name，只能选择一个作为查询条件
　　　如果 id 不为空，那么查询语句为：select * from t_user where id=?
　　　如果 id 为空，那么看userNo 是否为空，如果不为空，那么语句为 select * from _user where userNo=?;
　　　如果 userNo 为空，那么查询语句为 select * from t_user where name=?

5. foreach 语句

我们需要查询 t_user 表中 id 分别为1,2,3的用户
sql语句：select * from t_user where id=1 or id=2 or id=3
　　　　 select * from t_user where id in (1,2,3)

<select id="selectUserByListId" returnMap="BaseResultMap" parameterType="java.util.List">
    select * from t_user 
    <where>
        <!-- collection:指定输入对象中的集合属性
            item:每次遍历生成的对象
            open:开始遍历时的拼接字符串
            close:结束时拼接的字符串
            separator:遍历对象之间需要拼接的字符串
            select * from t_user where 1=1 and (id=1 or id=2 or id=3)
            -->
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id=#{id}
        </foreach>
    </where>
</select>

6. trim 语句

trim标记是一个格式化的标记，可以完成set或者是where标记的功能
用 trim 改写上面第二点的 if+where 语句

<select id="getUserByUsernoAndName" returnMap="BaseResultMap" parameterType="com.xxb.domain.UserPO">
    select * from t_user 
    <!--
    <where>
        <if test="userNo != null">
           and userNo=#{userNo}
        </if>

        <if test="name != null">
           and name=#{name}
        </if>
    </where>
    -->
    <trim prefix="where" prefixOverrides="and | or">
            <if test="userNo != null">
               and userNo=#{userNo}
            </if>
            <if test="name != null">
               and name=#{name}
            </if>
     </trim>
</select>

prefix：前缀　　　　　　
prefixoverride：去掉第一个and或者是or
suffixoverride：去掉最后一个标记（就像是上面前缀中的and or一样）

7. SQL 片段

有时候可能某个 sql 语句我们用的特别多，为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用。
　　比如：我们表中有blob类型字段，平时查询结果用不到这个字段，那么我们就把这个代码抽取出来，如下：
　　

<sql id="Blob_Column_List">
      base64
</sql>
<sql id="Base_Column_list">
      id,name,preview,styleId,laytype,width,height
</sql>
<select id="getByStyleId" parameterType="java.lang.Integer" resultMap="BaseResultMap">
      select <include refid="Base_Column_list"/> from t_style_inner where styleId=#{styleId} order by laytype asc
</select>

动态 sql 语句的编写往往是一个拼接的问题，要注意编写规范。

8. SQL 模糊查询

现在有个这样的场景，同一个输入框即可以输入id也可以输入name,返回的结果是两者的叠加，比如说输入框值为‘1’，返回结果包含id为‘1’、name中包含‘1’的结果集。

<select id="search" resultType="com.×××.×××.db.RoomPo">
        <if test="roomPo.name != null">
            <bind name="likeName" value="'%'+ roomPo.name +'%'"/>
        </if>
        <if test="roomPo.id != null">
            <bind name="likeId" value="roomPo.id"/>
        </if>
        select * from t_room where status=1
        <if test="roomPo.name != null">
            <choose>
                <when test="roomPo.id != null">
                    and ( id = #{likeId} or
                </when>
                <otherwise>
                      and (
                </otherwise>
            </choose>
            name like #{likeName})
        </if>
        limit #{begin},#{pageSize}
    </select>

bind 标签可以使用 OGNL 表达式创建一个变量井将其绑定到上下文中。