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

sfyhip

　　动态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>
 Condition.java 查询条件类的定义
/**
*查询条件
*/
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;
// .....
}


 EmpDao.java Mapper类接口方法定义


/**
*员工表的DAO接口。
*该接口由MyBatis自动实现，要求将其
*扫描到Spring容器中，因此需要追加注解。
*/
@MybatisDao
public 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);
}

 

EmpMapper.xml

<?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>
 

测试类TestEmp.java定义:

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()
);
}
}
}