mybatis动态SQL

1.sql片段：sql语句中重复使用的可以提取成sql片段供调用（如数据表中的字段）

sql标签是sql片段，id是被调用时用的

<sql id="all">     u_id,u_username,u_password,u_sex,u_createtime,u_createtime,u_delete

</sql>

sql片段用include标签调用

<include refid="all"></include>

2.where+if

where标签会在返回的sql语句前加where再去除and或or

<select id="selectByWhere" resultMap="BaseMap">

              select

              <include refid="all"></include>

              from tb_user

              <where>

                     <if test="uid!=0">

                            and u_id=#{uid}

                     </if>

                     <if test="username!=null and username!=''">

                            <!-- 模糊查询 -->

                            and u_username like concat('%',#{username},'%')

                     </if>

              </where>

       </select>

以上还用了模糊查询：用concat函数拼接%

3.set+if

set标签会在返回的sql前加set再去除最后的，（逗号）

<update id="update">

              update tb_user

              <set>

                     <if test="username!=null and username!=''">

                            u_username=#{username},

                     </if>

                     <if test="password!=null and password!=''">

                            u_password=#{password},

                     </if>

                     <if test="usex!=null and usex!=''">

                            u_sex=#{usex},

                     </if>

                     <if test="udelete!=null and udelete!=''">

                            u_delete=#{udelete},

                     </if>

              </set>

              where u_id=#{uid}

       </update>

4.trim+if：里面有属性：

a)     prefix：前缀，在sql语句前加xxx

b)     suffix：后缀，在sql语句后加xxx

c)      prefixOverride：替换sql语句前的xxx

d)     suffixOverride：替换sql语句后的xxx

5.choose+when+otherwise

从上往下选择，有一个符合就跳出

<!-- 根据一个不限定的条件查询 -->

       <select id="select" resultMap="BaseMap">

              select

              <include refid="all"></include>

              from tb_user

              <where>

                     <choose>

                            <when test="uid!=0">

                                   and u_id=#{uid}

                            </when>

                            <when test="username!=null and username!=''">

                                   and u_username like concat('%',#{username},'%')

                            </when>

                            <otherwise>

                            </otherwise>

                     </choose>

              </where>

       </select>

6.foreach

遍历数组或集合（根据多个id查询用in或or）

<!-- 根据多个id查询 -->

       <select id="selectByIds" resultMap="BaseMap">

              select

              <include refid="all"></include>

              from tb_user

              <where>

                     <foreach collection="ids" open="u_id in(" close=")" item="id" separator=",">

                            #{id}

                     </foreach>

              </where>

       </select>