MyBatis之动态SQL

目录

一、if 条件

二、choose ，when 和 otherwise 条件

三、where 条件

四、trim 条件

五、foreach 循环

六、set条件

数据库表Emp:

实体类：

public class Emp {
	private Integer empno;// 工号
	private String ename;// 姓名
	private String job;// 职位
	private Integer mgr;// 上级工号
	private Date hiredate;// 入职日期
	private Integer sal;// 工资
	private Integer comm;// 奖金
	private Integer deptno;// 部门编号
    ...
}

一、if 条件

测试接口方法：List<Emp> select1(Map<String, Object> map);

	<select id = "select1" resultMap = "Emp">
		select * from emp where 1 = 1
		<if test = "empno!= null">and empno = #{empno}</if>
		<if test = "ename!= null">and ename = #{ename}</if>
		<if test = "job!= null">and job = #{job}</if>
		<if test = "mgr!= null">and mgr = #{mgr}</if>
		<if test = "hiredate!= null">and hiredate = #{hiredate}</if>
		<if test = "sal!= null">and sal = #{sal}</if>
		<if test = "deptno!= null">and deptno = #{deptno}</if>
		<if test = "comm!= null">and comm = #{comm}</if>
	</select>

测试：

	Map<String, Object> map = new HashMap<String, Object>();
	map.put("comm", "100");
	map.put("deptno", "10");
	List<Emp> emps = empDao.select1(map);

二、choose ，when 和 otherwise 条件

测试接口方法：List<Emp> select2(Map<String, Object> map);

	<select id = "select2" resultMap = "Emp">
		select * from emp
		<choose>
			<when test = "key == 'ename'">where ename like concat('%',#{ename},'%')</when>
			<when test = "key == 'job'">  where job = #{job}  </when>
			<otherwise> where sal  =  #{sal} </otherwise>
		</choose>
	</select>

测试：

	Map<String, Object> map = new HashMap<String, Object>();
	map.put("key", "ename");
	map.put("ename", "张");
	List<Emp> emps = empDao.select2(map);

三、where 条件

1，自动加上 where

2，如果 where 子句以 and 或者 or 开头，则自动去除第一个 and 或者 or

测试接口方法：List<Emp> select2(Map<String, Object> map);

	<select id = "select3" resultMap = "Emp">
		select * from emp
		<where>
			<if test = "empno!= null">empno = #{empno}</if>
			<if test = "ename!= null">or ename like #{ename}</if>
			<if test = "job!= null">or job = #{job}</if>
			<if test = "mgr!= null">or mgr = #{mgr}</if>
			<if test = "hiredate!= null">or hiredate = #{hiredate}</if>
			<if test = "sal!= null">or sal = #{sal}</if>
			<if test = "deptno!= null">or deptno = #{deptno}</if>
			<if test = "comm!= null">or comm = #{comm}</if>
		</where>
	</select>

测试：

	Map<String, Object> map = new HashMap<String, Object>();
	map.put("ename", "%光%");
	map.put("deptno", 20);
	map.put("sal", 5000);
	List<Emp> emps = empDao.select3(map);

四、trim 条件

功能和 where 元素类似，提供了前缀，后缀功能，更加灵活；

测试接口方法：List<Emp> select4(Map<String, Object> maps);

	<select id = "select4" resultMap = "Emp">
		select * from emp
		<trim prefix = "where" prefixOverrides = "and|or">
			<if test = "empno! = null">and empno = #{empno}</if>
			<if test = "ename! = null">and ename like #{ename}</if>
			<if test = "job! = null">or job = #{job}</if>
			<if test = "mgr! = null">or mgr = #{mgr}</if>
			<if test = "hiredate! = null">or hiredate = #{hiredate}</if>
			<if test = "sal! = null">or sal = #{sal}</if>
			<if test = "deptno! = null">or deptno = #{deptno}</if>
			<if test = "comm! = null">or comm = #{comm}</if>
		</trim>
	</select>

测试：

	Map<String, Object> map=new HashMap<String, Object>();
	map.put("deptno", 20);
	map.put("sal", 5000);
	List<Emp> emps=empDao.select4(map);

五、foreach 循环

测试接口方法：List<Emp> select5(Map<String, Object> map);

	<select id = "select5" resultMap = "Emp">
		select * from emp where empno in
		<foreach collection = "aaa" separator = "," open = "(" close = ")"
			item = "empno">
			<!-- jstl:中得写法<foreach var = "empno" items = "aaa" index = "i"> -->
			#{empno}
		</foreach>
		<!-- select * from emp where empno in (1005,1003,1001) -->
	</select>

测试：

	Map<String, Object> map = new HashMap<String, Object>();
	List<Integer> list = new ArrayList<Integer>();
	list.add(1005);
	list.add(1003);
	list.add(1001);	
	map.put("aaa",list);
	List<Emp> emps = empDao.select5(map);

六、set条件

1，用在update语句中，自动加上 set

2，自动去除最后一个逗号

测试接口方法：int update(Emp emp);

	<update id = "update" parameterType = "Emp">
		update emp
		<set>
			<if test = "ename! = null">ename = #{ename},</if>
			<if test = "sal! = null">sal = #{sal},</if>
			<if test = "job! = null">job = #{job},</if>
			<if test = "deptno! = null">deptno = #{deptno},</if>
		</set>
		where empno = #{empno}
	</update>

测试：

	Emp emp = new Emp(1005, "Jack", "CLERK", 1003, new Date(), 6000, null, 30);
	int x = empDao.update(emp);