MyBatis 动态SQL

动态SQl

sql 语句会发生结构变化的才是动态 sql，Mybatis 提供九大动态 SQL 标签：

if 分支标签：

<select id="findUser">
    select * from User where 1=1
    <if test=" age != null ">
        and age > #{age}
    </if>
    <if test=" name != null ">
        and name like concat(#{name},'%')
    </if>
</select>

choose 、when 、otherwise 多分支标签：

<select id="findUser">
    select * from User where 1=1 
    <choose>
        <when test=" age != null ">
            and age > #{age}
        </when>
        <when test=" name != null ">
            and name like concat(#{name},'%')
        </when>
        <otherwise>
            and sex = '男'
        </otherwise>
    </choose>
</select>

foreach 遍历标签：

collection 可为 list ，array，map。注意 map 使用的特殊之处。

<select id="findAll">
    select  * from user where ids in 
    <foreach collection="list"
        item="item" index="index" 
        open="(" separator="," close=")">
            #{item}
    </foreach>
</select> 


<!-- xml 配置 -->
<select id="findAll">
    select  * from user where ids in 
    <foreach collection="array"
        item="item" index="index" 
        open="(" separator="," close=")">
            #{item}
    </foreach>
</select> 


<!-- xml 配置 -->
<select id="findAll">
    select  * from user where
    <foreach collection="_parameter"
         item="value111" index="key111"
         open=" " separator=" or " close=" ">
        id = #{value111}
    </foreach>
</select>

where 标签、set 标签：

• where 标签插入前缀 where
• set 标签插入前缀 set
• where 标签仅智能替换前缀 AND 或 OR
• set 标签可以只能替换前缀逗号，或后缀逗号

<select id="findUser">
    select * from User 
    <where>
        <if test=" age != null ">
            and age > #{age}
        </if>
        <if test=" name != null ">
            and name like concat(#{name},'%')
        </if>
    </where>
</select> 


<update id="updateUser">
    update user 
        <set>
           <if test="age !=null">
               age = #{age},
           </if>
           <if test="username !=null">
                  username = #{username},
           </if> 
           <if test="password !=null">
                  password = #{password},
           </if>
        </set>    
     where id =#{id}
</update> 

trim 标签：

是 where 标签和 set 标签 的基类，添加或去除前后缀

<!--
  注意在使用 trim 标签实现 where 标签能力时
  必须在 AND 和 OR 之后添加空格
  避免匹配到 android、order 等单词 
-->
<trim prefix="WHERE" prefixOverrides="AND | OR" >
    ...
</trim> 



<trim prefix="SET" prefixOverrides="," >
    ...
</trim>

或者

<trim prefix="SET" suffixesToOverride="," >
    ...
</trim>

bind 标签：

创建一个变量，并绑定到上下文，即供上下文使用

<select id="selecUser">
  <bind name="myName" value="'%' + _parameter.getName() + '%'" />
  SELECT * FROM user
  WHERE name LIKE #{myName}
</select>

补充标签：

用于复用语句块

<!-- 简单语句块 -->
<sql id="sql1">
  ${prefix}_user
</sql>

<!-- 嵌套语句块 -->
<sql id="sql2">
  from
    <include refid="${include_target}"/>
</sql>

<!-- 查询时引用嵌套语句块 -->
<select id="select" resultType="map">
  select
    id, username
  <include refid="sql2">
    <property name="prefix" value="t"/>
    <property name="include_target" value="sql1"/>
  </include>
</select>