mybatis动态SQL配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace：命名空间，用于隔离sql，还有一个很重要的作用，后面会讲 -->
<mapper namespace="com.igeek.crm.mapper.UserMapper">

	<!-- 定义sql片段 -->
	<sql id="colums"> id, username, birthday, sex, address </sql>

	<!-- 通过性别和名称查询学生 -->
	<select id="queryBySexAndName" parameterType="User" resultType="User">
		SELECT <include refid="colums"/> FROM `user` 
		WHERE 1 = 1  
		<if test="sex != 0">
			AND sex = #{sex}
		</if>
		<if test="username != null and username != ''">
			AND username LIKE '%${username}%'
		</if>
	</select>
	<!-- 多条件查询 -->
	<select id="queryBySexAndNameAndAddress" parameterType="user" resultType="user">
		SELECT <include refid="colums"/> FROM `user` 
		WHERE 1 = 1  
		<choose>
			<when test="sex != 0">
				AND sex = #{sex}
			</when>
			<when test="username != null and username != ''">
				AND username LIKE '%${username}%'
			</when>
			<when test="address !=null and address != ''">
				AND address = #{address}
			</when>
			<!--  其他情况
			<otherwise>
			</otherwise>
			 -->
		</choose>
	</select>
	
	
	<!-- 通过性别和名称查询学生  where标签-->
	<select id="queryBySexAndName1" parameterType="User" resultType="User">
		SELECT <include refid="colums"/> FROM `user` 
		<where>
			<if test="sex != 0">
				AND sex = #{sex}
			</if>
			<if test="username != null and username != ''">
				AND username LIKE '%${username}%'
			</if>
		</where>
	</select>
	
	
	<!-- 通过性别和名称查询学生  trim标签-->
	<select id="queryBySexAndName2" parameterType="User" resultType="User">
		SELECT <include refid="colums"/> FROM `user` where 1 = 1 
		<trim prefix="and" prefixOverrides="AND|OR">
			<if test="sex != 0">
				AND sex = #{sex}
			</if>
			<if test="username != null and username != ''">
				AND username LIKE '%${username}%'
			</if>
		</trim>
	</select>
	
	<!-- 修改用户信息使用set标签 -->
	<update id="updateUser" parameterType="user">
		update user
		<set>
			<if test="username !=null and username !=''">
				username = #{username},
			</if>
			<if test="birthday!=null">
				birthday=#{birthday},
			</if>
			<if test="sex!=0">
				sex=#{sex},
			</if>
			<if test="address!=null">
				address=#{address}	
			</if>
		</set>
		where id = #{id}
	</update>
	
	<!-- 修改用户信息使用trim标签 -->
	<update id="updateUser1" parameterType="user">
		update user
		<trim prefix="set" suffixOverrides=",">
			<if test="username !=null and username !=''">
				username = #{username},
			</if>
			<if test="birthday!=null">
				birthday=#{birthday},
			</if>
			<if test="sex!=0">
				sex=#{sex},
			</if>
			<if test="address!=null">
				address=#{address},
			</if>
		</trim>
		where id = #{id}
	</update>
	
	<!-- 通过ID查询一组用户信息 -->
	<!-- foreach标签，进行遍历 -->
		<!-- collection：遍历的集合，这里是QueryVo的ids属性 -->
		<!-- item：遍历的项目，可以随便写，，但是和后面的#{}里面要一致 -->
		<!-- open：在前面添加的sql片段 -->
		<!-- close：在结尾处添加的sql片段 -->
		<!-- separator：指定遍历的元素之间使用的分隔符 -->
	<select id="queryByIds" parameterType="QueryVo" resultType="User">
		SELECT <include refid="colums"/> FROM `user`
		WHERE id in
		<foreach collection="ids" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>
</mapper>

传list与数组集合:

传list集合
<select id="queryDeptListByBankLevelAndBankName" parameterType="java.util.HashMap"
			resultMap="queryDeptListByBankLevelAndBankNameResult">
		select DEPTSEQ ,
		DEPTNAME,DEPTID
		from DEPT
		 WHERE 1=1
		<if test="DeptLevel != null and DeptLevel != ''">And DeptLevel=#{DeptLevel}</if>
		<if test="DeptName != null and DeptName != ''"> and DeptName LIKE '%' || #{DeptName} || '%'</if>
		<if test="DeptSeq != null and DeptSeq != ''"> and DeptSeq=#{DeptSeq}</if>
		<if test="deptIdList != null and deptIdList.size > 0 ">
		 and DEPTID not in
		 <foreach collection="deptIdList" item ="deptId" index="i" open="(" close=")" separator=",">
		           #{deptId}
		 </foreach>
		 </if>
		 ORDER BY DEPTID
	</select>
接口:
		ArrayList deptIdList = new ArrayList();
        	String [] deptIds = {"006001", "006002", "006003", "006004", "006005", "006006", "006007",
        	        "006008", "006009", "006010", "006011", "006012", "006013", "006015",
        	        "006016", "006017", "006018", "006019", "006020", "006021", "006022",
        	        "006023", "006024", "006025", "006026", "006027", "006028", "006029",
        	        "006030", "006031", "006032", "006033", "006034", "006035", "006036",
        	        "006037", "006038", "006039", "006040", "006041", "006042", "006043",
        	        "006044", "006045", "006501", "009801", "009802", "009803", "009809",
        	        "009810", "009811", "014001", "015001", "016001", "016501", "019801",
        	        "019802", "019803", "019804", "019805", "019806", "026501", "027006",
        	        "027106", "027206", "027306", "027406", "027506", "029801", "036501",
        	        "037006", "039801", "046501", "047006", "049801", "056501", "057006",
        	        "809801", "059801", "066501", "067006", "069801", "076501", "077006",
        	        "079801", "086501", "087006", "089801", "097006", "099801", "107006",
        	        "109801", "117006", "117106", "119801", "127006", "129801", "806501",
        	        "809801", "816501", "819801", "9802"};
        	Collections.addAll(deptIdList, deptIds);
        	Map map = new HashMap();
        	map.put("deptIdList",deptIdList);
        	subList = this.sqlMap.queryForList("mcmmcif.queryDeptListByBankLevelAndBankName", map);	 
		 
		===============================================
		
		传数组:
		<select id="queryDeptListByBankLevelAndBankName" parameterType="java.util.HashMap"
			resultMap="queryDeptListByBankLevelAndBankNameResult">
		select DEPTSEQ ,
		DEPTNAME,DEPTID
		from DEPT
		 WHERE 1=1
		<if test="DeptLevel != null and DeptLevel != ''">And DeptLevel=#{DeptLevel}</if>
		<if test="DeptName != null and DeptName != ''"> and DeptName LIKE '%' || #{DeptName} || '%'</if>
		<if test="DeptSeq != null and DeptSeq != ''"> and DeptSeq=#{DeptSeq}</if>
		<if test="deptIdList != null and deptIdList.length > 0 ">
		 and DEPTID  in
		 <foreach collection="deptIdList" item ="deptId" index="i" open="(" close=")" separator=",">
		           #{deptId}
		 </foreach>
		 </if>
		 ORDER BY DEPTID
	</select>

接口:
	
	String [] deptIds = {"006007","006010","006011","006024","006025","006034","006035","006037",
        			"016501","016601","016701","016801","016901","017001","017101","017201","017301","017401",
        			"017501","017601","017605","017701","017801","017901","018001","018101","018301","018401",
        			"018501","018601","018701","018801","018901","019001","019101","019201","019301","019401",
        			"019501","019802","019803","019804","019805","019806","026501","027001","027101","027201",
        			"027301","027401","027501","037001","047001","047002","057001","067001","077001","087001",
        			"097001","107001","117001","117101","127001","800101","800102","800103","810101","810102",
        			"810103"};
        	Map map = new HashMap();
        	map.put("deptIds",deptIds);
        	subList = this.sqlMap.queryForList("mcmmcif.queryDeptListByBankLevelAndBankName", map);