1 <!-- if -->
2     <select id="getUserListByUser" resultType="Person" parameterType="Person">
3         select * from smbms_user 
4             where userName like CONCAT ('%',#{userName},'%')
5             <if test="userRole !=null and userRole !=''">
6              and userRole = #{userRole}
7             </if>    
8     </select>

1 <!-- where 条件符合则自动加上where 且去掉第一个and-->
2     <select id="getUser" resultType="Person" parameterType="Person">
3         select * from smbms_user 
4         <where>
5             <if test="userName !=null and userName !=''">
6                 and userName=#{userName}
7             </if>
8         </where>
9     </select>

 1 <!-- set可以更新一个或多个值，并且条件不成立为自动去掉多余的逗号 -->
 2     <update id="updateUser"  parameterType="Person">
 3         update smbms_user 
 4         <set>
 5             <if test="userName !=null and userName !=''">
 6                  userName=#{userName},
 7             </if>
 8             <if test="userPassword !=null and userPassword !=''">
 9                  userPassword=#{userPassword}
10             </if>
11         </set>
12         where id=#{id}
13     </update>

1 <!-- trim 去掉and 加上where     prefix前缀   prefixOverrides去掉前缀             suffix后缀   suffixOverrides去掉后缀  -->
2     <select id="where" parameterType="Person" resultType="Person">
3         select * from smbms_user
4         <trim prefix="where" prefixOverrides="and" suffix="" suffixOverrides="">
5             <if test="userName !=null and userName !=''">
6                 and userName=#{userName}
7             </if>
8         </trim>
9     </select>

 1 <!-- 哪个分支条件成立就拼接哪个         都不成立就执行otherwise分支 -->
 2     <select id="choose" parameterType="Person" resultType="Person">
 3         select * from smbms_user where i=1
 4         <choose>
 5              <when test="userName !=null and userName !=''">
 6                  and userName=#{userName}
 7              </when>
 8              <when test="userPassword !=null and userPassword !=''">
 9                  and userPassword=#{userPassword}
10              </when>
11              <otherwise>
12                  and phone=#{phone}
13              </otherwise>
14         </choose>
15     </select>

1 <!-- 等同于in(?,?,?) -->
2     <select id="foreach"  resultType="Person">
3         select * from smbms_user where userRole in 
4         <foreach collection="list" item="roleid" open="(" separator="," close=")">
5             #{roleid}
6         </foreach>     
7     </select>

 1 <!-- 多个供应商对应多个订单 -->
 2     <select id="forprobill"  resultMap="probill">
 3         select b.*,p.* from smbms_provider p,smbms_bill b where p.id=b.providerId and p.id in 
 4         <foreach collection="array" item="roleid" open="(" separator="," close=")">
 5             #{roleid}
 6         </foreach>     
 7     </select>
 8      <resultMap type="Provider" id="probill">
 9         <id property="id" column="id"/>
10         <collection property="billList" ofType="Bill">
11             <id  property="id" column="id"/>
12             <result property="productName" column="productName"/>
13         </collection>
14     </resultMap> 

1 <!-- map传参多个值      in的参数对应collection参数 为map对应的key值rmap   gender值为map对应的key值gender  区分大小写-->
2     <select id="userMap" resultType="Person" parameterType="Map">
3         select * from smbms_user where userRole in 
4         <foreach collection="rmap" item="roid" open="(" separator="," close=")">
5             #{roid}
6         </foreach>
7          and gender=#{gender} 
8     </select>