mybatis 动态sql

完成多条件查询等逻辑实现
用于实现动态SQL的元素主要有
where ...if     当使用where的时候.能够智能的处理第一个条件前的 and 和or
<where>
     <if test="username!=null">
          and userName like concat('%',#{username},'%')
     </if>
     <if test="userrole!=null">
          and userRole = #{userrole}
     </if>
</where>

set...if     最后一个条件后面的逗号会智能的去掉
<set>
     <if test="username!=null">
          userName = #{username},
     </if>
     <if test="gender!=null">
          gender = #{gender},
     </if>
     <if test="birthday!=null">
          birthday = #{birthday},
     </if>
     <if test="phone!=null">
          phone = #{phone},
     </if>
     <if test="address!=null">
          address = #{address},
     </if>
     <if test="userrole!=null">
          userRole = #{userrole},
     </if>
</set>

trim...if 用于update:  prefix="set",添加前缀, suffix="where id = #{id}",添加后缀   ,suffixOverrides=","去掉最后一个逗号
<trim prefix="set" suffix="where id = #{id}" suffixOverrides=",">
     <if test="username!=null">
          userName = #{username},
     </if>
     <if test="gender!=null">
          gender = #{gender},
     </if>
     <if test="birthday!=null">
          birthday = #{birthday},
     </if>
     <if test="phone!=null">
          phone = #{phone},
     </if>
     <if test="address!=null">
          address = #{address},
     </if>
     <if test="userrole!=null">
          userRole = #{userrole},
     </if>
</trim>

trim...if 用于代替where...if   prefixOverrides="and",去掉前缀
<trim prefix="where" prefixOverrides="and">
     <if test="username!=null">
          and userName like concat('%',#{username},'%')
     </if>
     <if test="userrole!=null">
          and userRole = #{userrole}
     </if>
</trim>

foreach 遍历数组  collection="array"表示遍历的对象类型是数组  item="aaa",表示遍历的每一个元素 不要写paramterType
<foreach collection="array" item="aaa" open="(" close=")" separator=",">
     #{aaa}
</foreach>

foreach 遍历list集合 collection="list"表示遍历的对象类型是list集合 不要写paramterType
<foreach collection="list" item="aaa" open="(" close=")" separator=",">
     #{aaa}
</foreach>


sql片段  :定义一个sql片段
<sql id="where">
     <where>
          <if test="t.username!=null">
              and username=#{t.username}
          </if>
          <if test="t.userrole!=null">
              and userrole=#{t.userrole}
          </if>
     </where>
</sql>
引用sql片段
<include refid="where"/>