一、动态查询
1、方式一：
<!--if标签--><!--if中的test属性必须录入，其内部为ognl表达式，不需要#{}。如果test内满足，则主体语句执行--><select id="findAccount" parameterType="account" resultType="account">select * from accounts where 1=1<if test="number!=null and !''.equals(number)">and number = #{number}</if><if test="aid!=null">and aid = #{aid}</if></select>
2、方式二：
<!--where标签：内部if都不满足则不处理，若满足，则将第一个语句的头部的and换成where关键字--><select id="findAccount" parameterType="account" resultType="account">select * from accounts<where><if test="number!=null and !''.equals(number)">and number = #{number}</if><if test="aid!=null">and aid = #{aid}</if></where></select>
3、方式三：
<!--choose标签：与if类似，otherwise表示都不满足则运行的语句，设为1=2，不满足，则不会查询到任何结果，这对于海量数据的查询有节约数据库访问的帮助，当然由于有分页的存在，也会节约数据查询--><select id="findAccount" parameterType="account" resultType="account">select * from accounts<where><choose><when test="number!=null and !''.equals(number)">and number = #{number}</when><when test="balance!=null">and balance = #{balance}</when><otherwise>and 1=2</otherwise></choose></where></select>
二、动态插入
<!--动态插入:本例中，如果number为空，则不插入，从而节省数据库交互资源--><insert id="insertAccount" parameterType="account">insert into accounts(aid,<if test="number != null">number,</if>balance) values (#{aid},<if test="number != null">#{number},</if>#{balance})</insert>
三、动态更新
<!--动态更新--><update id="updateAccount" parameterType="account">update accounts set<if test="number != null and !''.equals(number)">number = #{number},</if><if test="balance!=null">balance = #{balance},</if>aid = #{aid} where aid = #{aid};/*关键在于此处是以aid为目标进行更新，再次更新aid，类似于where 1=1的sql拼接作用*/</update>
四、数组、集合查询
<!--foreach标签：循环遍历--><!--collection在数组中对应array；list集合中对应collection或list；set集合中对应collection--><select id="findAccountBySth2" resultType="account"><foreach collection="array" item="aid" open="(" separator="," close=")">#{aid}</foreach></select>
附加1个集合先判断是否为空，然后遍历的注解式的例子：
@Select( {"<script>"," SELECT "," measure_definition "," FROM udata_model_measures","<trim prefix='where' prefixOverrides='AND'>","<choose>","<when test='list != null and list.size()>0'>","AND measure_id IN","<foreach item='item' collection='list' open='(' close=')' separator=','> ","#{item}","</foreach> ","</when>","<otherwise>","AND 1 = 0","</otherwise>","</choose>","</trim>","</script>"})ArrayList<String> findMeasureDefinitionByMeasureIds(List<String> list);
注意，遍历的集合“collection=‘list’”必需定义条件为list，且与入参的对象名list相对应。
