SprignMVC+myBatis调整(四)——动态SQL

动态SQL简介：

带台SQL是MyBatis框架中强大特效之一。在一些组合查询页面中刚，需要根据用户输入的查询条件生成不同的查询SQL,这在JDBC或其他相似框架中需要在代码中拼写SQL,经常容易出错，但是在MyBatis中可以解决这种问题。

使用动态SQL元素与JSTL相似，它允许我们在XML中构建不同的SQL语句。常用的元素如下：

判断元素：if,choose

关键字元素：where,set,trim

循环元素：foreach

?

判断元素用法：

if元素

if元素是简单的条件判断逻辑，满足指定条件时追加if元素内的SQL.

<select...>

? SQL语句1

? <if test="条件表达式">

? ? ?SQL语句2

? </if>

</select>

?

注意:条件表达式中大于号小于号用 gt,lt

<if test="id?!=?9">...</if>

<if test="id gt 9">...</if>

<if test="id lt 9">...</if>

	<!-- findById -->	<select id="findById" parameterType="int" resultType="com.tarena.entity.Emp">		<if test="6 gt 5">			select * from t_emp 		</if>	</select>	<!-- findByDept -->	<select id="findByDept" parameterType="com.tarena.entity.Condition"		resultType="com.tarena.entity.Emp">		select * from t_emp		<if test="deptId != null">			where deptno=#{deptId}		</if>	</select>

?

?注意：findById的传参时， Mapper方法中定义要使用@param(value="id")来指定传递参数的名字，否则执行test元素中的比较表达式会出错，找不到getter/setter方法。

?

/**	 * 根据id查询员工	 */	List<Emp> findById(@Param(value="id") int id);	/**	 * 根据部门查询员工	 */	List<Emp> findByDept(Condition cond);

?测试代码：

?

	@Test	public void testFindById() {		//创建Spring容器		ApplicationContext ctx = 			new ClassPathXmlApplicationContext(					"applicationContext.xml");		//通过容器创建DAO		EmpDao dao = ctx.getBean(EmpDao.class);		//调用查询方法		List<Emp> emp = dao.findById(3);		System.out.println(emp.size());	}

?

choose元素:

choose元素的作用相当于JAVA中的Switch语句，基本用法和JSTL中使用一样，和otherwist搭配使用。

<select..>

?SQL语句1

?<choose>

? ?<when test="条件表达式">

? ? ? ? SQL语句2

? ?</when>

? ?<otherwise>

? ? ? ??SQL语句3

? ?</otherwise>

?</choose>

</select>

?

拼关键字元素用法：

where元素主要是用于简化查询语句中where部门的条件判断。where元素可以在<where>元素所在的位置输出一个where关键字，而且还可以将后面的条件多余的and或or关键字去掉。

<select..>

? ?select 字段 from 表

? ?<where>

? ? ? 动态追加条件

? ?</where>

</select>

?

注意：模糊查询中获取参数的表达式写在单引号中，这里使用${ename}取值而不是#{ename}

?

<select id="findByName" parameterType="java.lang.String"		resultType="com.tarena.entity.Emp">		select * from t_emp		<where>		  ename like '%${ename}%'		</where>	</select>

?

? 或者直接写上SQL

?

<select id="findByName" parameterType="java.lang.String"		resultType="com.tarena.entity.Emp">		select * from t_emp where ename like '%${ename}%'	</select>

?

?

set元素：

? ? set元素主要是用在更新操作的时候，它的主要功能和where元素相似，主要是在<set>元素所在位置输出一个set关键字，而且还可以去除内容结尾中无关的逗号。

<update...>

? ? update 表

? ? <set>

? ? ? ?动态追加更新字段

? ? ? ?<if test="ename!=null">

? ? ? ? ?ENAME=#{ename},

?? ? ? </if> ?? ? ? ?

? ? ? ?<if test="sal!=null">

? ? ? ? ?SAL=#{sal},

?? ? ? </if>

? ? ? ?<if test="comm!=null">

? ? ? ? ?COMM=#{comm},

?? ? ? </if>

? ? </set>

? ? where EMPNO=#{empno}

</update>

?

trim元素:

trim元素的主要功能如下:

-可以在自己包含的内容前加上某前缀，也可以在其后加上某些后缀，预制对应的属性是prefix和suffix;

-可以把包含内容的首部某些内容过滤，即忽略，也可以把尾部的某些内容过滤，对应的属性是prxfixOverrides和suffixOverridex;

-正因为trim有上述功能，所有我们也可以非常简单的利用trim里代替where和set元素的功能

<!-等价于where元素>

<trim prefix="WHERE" prefixOverrides="AND|OR">

?..

</trim>

<!-等价于set元素>

<trim prefix="SET" prefixOverrides=",">

?..

?

</trim>

?

进行循环：

foreach元素

foreach元素实现了逻辑循环，可以进行一个集合的迭代，主要用在构建in条件中。

<select ..>

? ?select * from 表 where 字段 in

? ?<foreach collection="集合" item="迭代变量"?

? ? ?open="(" separator=","close=")">

? ?#{迭代变量}

? ?</foreach>

</select>

?

foreach元素非常强大，它允许指定一个集合，声明集合项和索引变量，这些变量可以用在元素体内。它也允许指定开放和关闭的字符串，在迭代之间放置分隔符。

?

<!-- findByIds -->	<select id="findByIds" parameterType="com.tarena.entity.Condition"		resultType="com.tarena.entity.Emp">		select * from t_emp where empno in 		<!--			collection指定了要循环的集合； item指定了循环变量； open指定了生成的字符串的开头部分；			close指定了生成的字符串的结尾部分； separator指定了输出的变量之间的分隔字符。		-->		<foreach collection="empIds" item="eid" open="(" close=")"			separator=",">			#{eid}		</foreach>	</select>

?

?

/** *	查询条件 */public class Condition {	private Integer deptId;		private Double salary;		private List<Integer> empIds;

?

下面是综合练习的代码：

Emp.java

?

/** * 员工表的实体类 */public class Emp {	private Integer empno;	private String ename;	private String job;	private Integer mgr;	private Date hiredate;	private Double sal;	private Double comm;	private Integer deptno;        // .....}

?

/** *	员工表的DAO接口。 *	该接口由MyBatis自动实现，要求将其 *	扫描到Spring容器中，因此需要追加注解。 */@MybatisDaopublic interface EmpDao {	List<Emp> findAll();		List<Emp> findByName(@Param(value="ename") String ename);	/**	 * 根据id查询员工	 */	List<Emp> findById(@Param(value="id") int id);	/**	 * 根据部门查询员工	 */	List<Emp> findByDept(Condition cond);		/**	 * 判断传入的salary的大小	 * 如果salary大于等于2500，则查询全部大于salary的员工，	 * 即where sal>#{salary}；	 * 如果salary小于2500，则查询全部大于2500的员工，	 * 即where sal>2500。	 */	List<Emp> findBySalary(Condition cond);		/**	 * 根据多个条件查询员工，即	 * 查询出指定部门下，高于指定工资的员工。	 */	List<Emp> findByCondition(Condition cond);		/**	 * 更新员工	 */	void update(Emp emp);		/**	 * 根据一组ID查询出对应的员工	 */	List<Emp> findByIds(Condition cond);	}

<?xml version="1.0" encoding="UTF-8" ?>  <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"       "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd"><mapper namespace="com.tarena.dao.EmpDao">	<!-- findAll -->	<select id="findAll" resultType="com.tarena.entity.Emp">		select * from t_emp	</select>	<!-- findById -->	<select id="findById" parameterType="int" resultType="com.tarena.entity.Emp">		<if test="6 gt 5">			select * from t_emp 		</if>	</select>	<select id="findByName" parameterType="java.lang.String"		resultType="com.tarena.entity.Emp">		select * from t_emp where ename like '%${ename}%'	</select>	<!-- findByDept -->	<select id="findByDept" parameterType="com.tarena.entity.Condition"		resultType="com.tarena.entity.Emp">		select * from t_emp		<if test="deptId != null">			where deptno=#{deptId}		</if>	</select>	<!-- findBySalary -->	<select id="findBySalary" parameterType="com.tarena.entity.Condition"		resultType="com.tarena.entity.Emp">		select * from t_emp		<choose>			<when test="salary >= 2500">				where sal>#{salary}			</when>			<otherwise>				where sal>2500			</otherwise>		</choose>	</select>	<!-- findByCondition -->	<select id="findByCondition" parameterType="com.tarena.entity.Condition"		resultType="com.tarena.entity.Emp">		select * from t_emp		<!-- where元素等价于where 1=1 -->		<!--			<where> <if test="deptId != null"> and deptno=#{deptId} </if> <if			test="salary != null"> and sal>#{salary} </if> </where>		-->		<!--			prefix在前面增加一个指定的词； prefixOverrides去掉前面的第一个指定的词； suffix在后面增加一个指定的词；			suffixOverrides去掉后面的最后一个指定的词。		-->		<trim prefix="where" prefixOverrides="and">			<if test="deptId != null">				and deptno=#{deptId}			</if>			<if test="salary != null">				and sal>#{salary}			</if>		</trim>	</select>	<!-- update -->	<update id="update" parameterType="com.tarena.entity.Emp">		update t_emp		<!-- set元素可以生成set关键字，				另外可以自动去掉最后多余的"," -->		<!--			<set> <if test="ename != null"> ename=#{ename}, </if> <if test="job			!= null"> job=#{job}, </if> <if test="sal != null"> sal=#{sal}, </if>			</set>		-->		<trim prefix="set" suffixOverrides=",">			<if test="ename != null">				ename=#{ename},				</if>			<if test="job != null">				job=#{job},				</if>			<if test="sal != null">				sal=#{sal},				</if>		</trim>		where empno=#{empno}	</update>	<!-- findByIds -->	<select id="findByIds" parameterType="com.tarena.entity.Condition"		resultType="com.tarena.entity.Emp">		select * from t_emp where empno in 		<!--			collection指定了要循环的集合； item指定了循环变量； open指定了生成的字符串的开头部分；			close指定了生成的字符串的结尾部分； separator指定了输出的变量之间的分隔字符。		-->		<foreach collection="empIds" item="eid" open="(" close=")"			separator=",">			#{eid}		</foreach>	</select></mapper>

package com.tarena.test;import java.util.ArrayList;import java.util.List;import org.junit.Test;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import com.tarena.dao.EmpDao;import com.tarena.entity.Condition;import com.tarena.entity.Emp;/** *	EmpDao测试类 */public class TestEmpDao {	@Test	public void testFindById() {		//创建Spring容器		ApplicationContext ctx = 			new ClassPathXmlApplicationContext(					"applicationContext.xml");		//通过容器创建DAO		EmpDao dao = ctx.getBean(EmpDao.class);		//调用查询方法		List<Emp> emp = dao.findById(3);		System.out.println(emp.size());	}		@Test	public void testFindByName() {		//创建Spring容器		ApplicationContext ctx = 			new ClassPathXmlApplicationContext(					"applicationContext.xml");		//通过容器创建DAO		EmpDao dao = ctx.getBean(EmpDao.class);		//调用查询方法  模糊查询		List<Emp> list = dao.findByName("S");		for(Emp e : list) {			System.out.println(				e.getEmpno() + " " + 				e.getEname() + " " + 				e.getDeptno()			);		}	}		@Test	public void testFindAll() {		//创建Spring容器		ApplicationContext ctx = 			new ClassPathXmlApplicationContext(					"applicationContext.xml");		//通过容器创建DAO		EmpDao dao = ctx.getBean(EmpDao.class);		//调用查询方法		List<Emp> list = dao.findAll();		for(Emp e : list) {			System.out.println(				e.getEmpno() + " " + 				e.getEname() + " " + 				e.getDeptno()			);		}	}		@Test	public void testFindByDept() {		ApplicationContext ctx = 			new ClassPathXmlApplicationContext(					"applicationContext.xml");		EmpDao dao = ctx.getBean(EmpDao.class);		Condition cond = new Condition();//		cond.setDeptId(20);		List<Emp> list = dao.findByDept(cond);		for(Emp e : list) {			System.out.println(				e.getEmpno() + " " +				e.getEname() + " " +				e.getDeptno()			);		}	}		@Test	public void testFindBySalary() {		ApplicationContext ctx = 			new ClassPathXmlApplicationContext(					"applicationContext.xml");		EmpDao dao = ctx.getBean(EmpDao.class);		Condition cond = new Condition();		cond.setSalary(1000.0);		List<Emp> list = dao.findBySalary(cond);		for(Emp e : list) {			System.out.println(				e.getEmpno() + " " +				e.getEname() + " " +				e.getSal() + " " +				e.getDeptno()			);		}	}		@Test	public void testFindByCondition() {		ApplicationContext ctx = 			new ClassPathXmlApplicationContext(					"applicationContext.xml");		EmpDao dao = ctx.getBean(EmpDao.class);		Condition cond = new Condition();		cond.setDeptId(20);		cond.setSalary(2000.0);		List<Emp> list = dao.findByCondition(cond);		for(Emp e : list) {			System.out.println(				e.getEmpno() + " " +				e.getEname() + " " +				e.getSal() + " " +				e.getDeptno()			);		}	}			@Test	public void testUpdate() {		ApplicationContext ctx = 			new ClassPathXmlApplicationContext(					"applicationContext.xml");		EmpDao dao = ctx.getBean(EmpDao.class);		Emp e = new Emp();		e.setEmpno(14);		e.setEname("猪八戒");		e.setSal(1500.0);		dao.update(e);	}		@Test	public void testFindByIds() {		ApplicationContext ctx = 			new ClassPathXmlApplicationContext(					"applicationContext.xml");		EmpDao dao = ctx.getBean(EmpDao.class);		Condition cond = new Condition();		List<Integer> ids = new ArrayList<Integer>();		ids.add(3);		ids.add(7);		ids.add(8);		cond.setEmpIds(ids);		List<Emp> list = dao.findByIds(cond);		for(Emp e : list) {			System.out.println(				e.getEmpno() + " " +				e.getEname() + " " +				e.getDeptno()			);		}	}	}