Mybatis-动态sql

	//根据eid,ename,age,sex多条件查询员工信息
	List<Emp> getEmpListByMoreTJ(Emp emp);
	
	//根据eid,ename,age,sex中的其中一个查询一个员工信息
	List<Emp> getEmpListByChoose(Emp emp);
	
	//添加员工信息，将0|1-->女|男
	void insertEmp(Emp emp);
	
	//通过eid所组成的字符串实现批量删除
	void deleteMoreEmp(String eids);
	
	//通过list集合实现批量删除
	void deleteMoreByList(@Param("eids")List<Integer> eids);
	
	//批量添加
	void insertMoreByArray(@Param("emps")Emp[] emps);
	
	//批量修改
	void updateMoreByArray(@Param("emps")Emp[] emps);

1. if where
   1. If用于完成简单的判断
      
   2. Where用于解决SQL语句中where关键字以及条件中第一个and或者or的问题

	<select id="getEmpListByMoreTJ1" resultType="Emp">
		select eid,ename,age,sex,did from emp 
		<where>
			<if test="eid != null">
				and eid = #{eid}
			</if>
			<if test="ename != null and ename != ''">
				and ename = #{ename}
			</if>
			<if test="age != null">
				and age = #{age}
			</if>
			<if test="sex == 1 or sex == 0">//判断等于的时候，只能用数字，可加引号
				and sex = #{sex}
			</if>
		</where>
	</select>

2. Trim 可以在条件判断完的SQL语句前后，添加或者去掉指定的字符
   prefix: 添加前缀
   prefixOverrides: 去掉前缀
   suffix: 添加后缀
   suffixOverrides: 去掉后缀

	<!-- 
		<sql id=""></sql>：设置一段SQL片段，即公共SQL，可以被当前映射文件中所有的SQL语句所访问
		<include refid="empColumns"></include>：访问某个SQL片段
	 -->
	<sql id="empColumns">select eid,ename,age,sex,did from emp </sql>

	<!-- 
		<if test=""></if>：通过test表达式，拼接SQL
		<where>：添加where关键字，同时去掉多余的and
		<trim prefix="" suffix="" prefixOverrides="" suffixOverrides="">：截取并拼接
		prefix：在操作的SQL语句前加入某些内容
		suffix：在操作的SQL语句后加入某些内容
		prefixOverrides：把操作的SQL语句前的某些内容去掉
		suffixOverrides：把操作的SQL语句后的某些内容去掉
	 -->
	
	<!-- List<Emp> getEmpListByMoreTJ();多条件查询：若页面中没有设置此条件，SQL语句中一定不能有该条件 -->
	<select id="getEmpListByMoreTJ" resultType="Emp">
		<include refid="empColumns"></include>
		<trim prefix="where" suffixOverrides="and|or">
			<if test="eid != null">
				eid = #{eid} and
			</if>
			<if test="ename != null and ename != ''">
				ename = #{ename} and 
			</if>
			<if test="age != null">
				age = #{age} or 
			</if>
			<if test="sex == 1 or sex == 0">
				sex = #{sex} 
			</if>
		</trim>
	</select>

3. set 主要是用于解决修改操作中SQL语句中可能多出逗号的问题（set用的少，可用trim解决）
   

4. choose(when、otherwise) if - else if - else(最后的else可以不要)

	<!-- 
		<choose>：选择某一个when或otherwise拼接SQL
			<when test=""></when>：通过test表达式拼接SQL
			.
			.
			.
			<otherwise></otherwise>：当when都不符合条件，就会选择otherwise拼接SQL
		</choose>
	 -->
	
	<!-- List<Emp> getEmpListByChoose(Emp emp); -->
	<select id="getEmpListByChoose" resultType="Emp">
		select eid,ename,age,sex from emp 
		where 
		<choose>
			<when test="eid != null">
				eid = #{eid}
			</when>
			<when test="ename != null and ename != ''">
				ename = #{ename}
			</when>
			<when test="age != null">
				age = #{age}
			</when>
			<otherwise>
				sex = #{sex}
			</otherwise>
		</choose>
	</select>

	<!-- void insertEmp(Emp emp); -->
	<insert id="insertEmp">
		insert into emp(eid,ename,age,sex) values(
			null,
			#{ename},
			#{age},
			<choose>
				<when test="sex == 0">'女'</when>
				<when test="sex == 1">'男'</when>
				<otherwise>'不详'</otherwise>
			</choose>
		)
	</insert>

5. foreach 主要用于循环迭代
   collection: 要迭代的集合
   item: 当前从集合中迭代出的元素
   open: 开始字符
   close:结束字符
   separator: 元素与元素之间的分隔符
   index:
   迭代的是List集合: index表示的当前元素的下标
   迭代的Map集合: index表示的当前元素的key

	<!-- void deleteMoreEmp(String eids); -->
	<delete id="deleteMoreEmp">
		delete from emp where eid in (${value})
	</delete>
	
	<!-- 
		<foreach collection="" item="" close="" open="" separator="" index=""></foreach>
		对一个数组或集合进行遍历
		collection：指定要遍历的集合或数组
		item：设置别名
		close：设置循环体的结束内容
		open：设置循环体的开始内容
		separator：设置每一次循环之间的分隔符
		index：若遍历的是list，index代表下标；若遍历的是map，index代表键
	 -->
	
	<!-- void deleteMoreByList(List<Integer> eids); -->
	<!-- 
		delete from emp where eid in ();
		delete from emp where eid = 1 or eid = 2 or eid = 3  //见下图
	 -->
	<delete id="deleteMoreByList">
		delete from emp where eid in 
		<foreach collection="eids" item="eid" separator="," open="(" close=")">
			#{eid}
		</foreach>
	</delete>
	

批量操作

	<!-- 
		delete：
			delete from emp where eid in ();
			delete from emp where eid = 1 or eid = 2 or eid = 3 
		select：
			select * from emp where eid in ();
			select * from emp where eid = 1 or eid = 2 or eid = 3 
		update：
			把每条数据修改为相同内容
			update emp set ... where eid in ();
			update emp set ... where eid = 1 or eid = 2 or eid = 3
			把每条数据修改为对应内容，注意：必须在连接地址（url）后添加参数?allowMultiQueries=true
			update emp set ... where eid = 1;
			update emp set ... where eid = 2;
			update emp set ... where eid = 3
		insert
			insert into emp values(),(),()
	 -->
	 
	 <!-- void insertMoreByArray(Emp[] emps); -->
	 <insert id="insertMoreByArray">
	 	insert into emp values
	 	<foreach collection="emps" item="emp" separator=",">
	 		(null,#{emp.ename},#{emp.age},#{emp.sex},1)
	 	</foreach>
	 </insert>
	 
	 <!-- void updateMoreByArray(@Param("emps")Emp[] emps); -->
	 <update id="updateMoreByArray">
	 	<foreach collection="emps" item="emp">
	 		update emp set ename = #{emp.ename}, age = #{emp.age}, sex = #{emp.sex} where eid = #{emp.eid};//加分号
	 	</foreach>
	 </update>
	 

		//Emp[] emps = new Emp[3];
		//Emp[] emps = new Emp[] {};
		Emp emp1 = new Emp(null, "a", 23, "男");
		Emp emp2 = new Emp(null, "aa", 23, "男");
		Emp emp3 = new Emp(null, "aaa", 23, "男");
		Emp[] emps = {emp1, emp2, emp3};//方法三
		
		mapper.insertMoreByArray(emps);

		Emp emp1 = new Emp(17, "a1", 233, "女");
		Emp emp2 = new Emp(18, "aa1", 233, "女");
		Emp emp3 = new Emp(19, "aaa1", 233, "女");
		Emp[] emps = {emp1, emp2, emp3};
		mapper.updateMoreByArray(emps);

6. sql 标签是用于抽取可重用的sql片段，将相同的，使用频繁的SQL片段抽取出来，单独定义，方便多次引用（例子见2）

public class TestSQL {
	
	@Test
	public void testMore() throws Exception {
		
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
		
			
		/*List<Integer> eids = new ArrayList<>();
		eids.add(7);
		eids.add(8);
		eids.add(10);
		mapper.deleteMoreByList(eids);*/
		
	}
	
	@Test
	public void testDeleteMore() throws Exception {
		
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
		String eids = "3,4,6";
		mapper.deleteMoreEmp(eids);
		
	}
	
	@Test
	public void testChoose() throws Exception {
		
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
		Emp emp = new Emp();
		
		//emp.setEid(1);
		emp.setEname("张三三");
		emp.setAge(23);
		emp.setSex("123");
		
		mapper.insertEmp(emp);
		
		/*List<Emp> list = mapper.getEmpListByChoose(emp);
		for (Emp e : list) {
			System.out.println(e);
		}*/
		
	}
	
	@Test
	public void testIf() throws Exception {
		
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
		Emp emp = new Emp();
		
		//emp.setEid(1);
		emp.setEname("张三");
		emp.setAge(23);
		//emp.setSex("1");
		
		List<Emp> list = mapper.getEmpListByMoreTJ(emp);
		for (Emp e : list) {
			System.out.println(e);
		}
		
	}
	
	public SqlSessionFactory getSqlSessionFactory() throws Exception {
		InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
		return sqlSessionFactory;
	}
	
}