说明
本文以orcl.scott.emp为例。
源码
if
需求
当empno不为空时,使用empno查询,当ename不为空时,使用ename模糊查询。
接口
/**
* 通过JavaBean、动态if获取员工信息
* 如果empno不为空,那么以empno为准查询
* 如果ename不为空,那么同时以ename字段进行模糊查询
*/
Emp selectByDynParamsIf(Emp emp);
实现
<!-- if -->
<select id="selectByDynParamsIf" resultMap="BaseResultMap" parameterType="com.yan.po.Emp">
select
<include refid="Base_Column_List"/>
from emp where 1=1
<if test="empno != null and empno != ''">
and empno=#{empno,jdbcType=DECIMAL}
</if>
<if test="ename != null and ename != ''">
and ename like ('%'||#{ename}||'%')
</if>
</select>
测试
/**
* 动态SQL-if
*/
emp = new Emp("SCOTT");
emp=empMapper.selectByDynParamsIf(emp);
System.out.println("================以下语句通过动态SQL生成================");
System.out.println(emp.getEname()+"的工作是:"+emp.getJob());
结果
2017-01-05 23:08:18,717 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsIf] - ==> Preparing: select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO from emp where 1=1 and ename like ('%'||?||'%')
2017-01-05 23:08:18,718 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsIf] - ==> Parameters: SCOTT(String)
2017-01-05 23:08:18,722 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsIf] - <== Total: 1
================以下语句通过动态SQL生成================
SCOTT的工作是:ANALYST
choose、when、otherwise
需求
当empno不为空时,使用empno查询,当empno为空而ename不为空时,使用ename模糊查询,否则只取全部结果集的第一行(保证所取得的结果能够包装成Emp)。这里逻辑不严谨,意思一下而已。
接口
/**
* 通过JavaBean、动态choose获取员工信息
* 若empno不为空,则只以empno为准查询
* 若empno为空但ename不为空,则以ename为准模糊查询
* 否则只取第一行记录
* @param emp
* @return
*/
Emp selectByDynParamsChoose(Emp emp);
实现
<!-- choose、when、otherwise -->
<select id="selectByDynParamsChoose" resultMap="BaseResultMap" parameterType="com.yan.po.Emp">
select * from emp
<trim prefix="where" prefixOverrides="and" suffixOverrides=",">
<choose>
<when test="empno != null and empno != ''">
and empno = #{empno},
</when>
<when test="empno == null and ename != null and ename != ''">
and ename like ('%'||#{ename}||'%')
</when>
<otherwise>
and rownum = 1
</otherwise>
</choose>
</trim>
</select>
测试
/**
* 动态SQL-choose
*/
System.out.println("=========empno\\ename 均不为空,但ename是错误的==========");
emp = new Emp((short) 7369,"HAHA");
emp = empMapper.selectByDynParamsChoose(emp);
System.out.println(emp.getEname());
System.out.println("=========empno为空,ename不为空==========");
emp = new Emp(null,"ADAMS");
emp = empMapper.selectByDynParamsChoose(emp);
System.out.println(emp.getHiredate());
System.out.println("=========没有参数==========");
emp = new Emp();
emp = empMapper.selectByDynParamsChoose(emp);
System.out.println(emp.getEname());
结果
=========empno\ename 均不为空,但ename是错误的==========
2017-01-05 23:08:18,723 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==> Preparing: select * from emp where empno = ?
2017-01-05 23:08:18,723 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==> Parameters: 7369(Short)
2017-01-05 23:08:18,728 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - <== Total: 1
SMITH
=========empno为空,ename不为空==========
2017-01-05 23:08:18,740 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==> Preparing: select * from emp where ename like ('%'||?||'%')
2017-01-05 23:08:18,740 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==> Parameters: ADAMS(String)
2017-01-05 23:08:18,744 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - <== Total: 1
Sat May 23 00:00:00 CDT 1987
=========没有参数==========
2017-01-05 23:08:18,749 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==> Preparing: select * from emp where rownum = 1
2017-01-05 23:08:18,749 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==> Parameters:
2017-01-05 23:08:18,755 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - <== Total: 1
SMITH
foreach
需求
查询部门编号在给定集合(数组,list,map等)范围内的员工的集合。
接口
/**
* 通过foreach获取部门id在一定范围内的员工信息
* 除了例子中的数组类型参数,也可以遍历List、Set、Map等集合
*/
List<Emp> selectByDynParamsForeach(List<Short> deptnos);
实现
<!-- collection指集合类名,数组则为array List则为list或collection
-->
<select id="selectByDynParamsForeach" resultType="emp" >
select * from emp where deptno in
<foreach collection="collection" index="index" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</select>
测试
/**
* 动态SQL-foreach
*/
// short[] deptnos=new short[]{10,30};
List<Short> deptnos = new ArrayList<Short>();
List<Emp> empList=new ArrayList<Emp>();
deptnos.add((short) 10);
deptnos.add((short) 30);
empList=empMapper.selectByDynParamsForeach(deptnos);
for(Emp e:empList){
System.out.println(e.getEname());
}
结果
2017-01-05 23:08:18,757 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsForeach] - ==> Preparing: select * from emp where deptno in ( ? , ? )
2017-01-05 23:08:18,758 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsForeach] - ==> Parameters: 10(Short), 30(Short)
2017-01-05 23:08:18,773 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsForeach] - <== Total: 9
ALLEN
WARD
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
MILLER
特别说明
bind
需求
对输入的ename自动添加模糊查询使用的通配符%,对输入的工资加1000再查询。
接口
/**
* 通过bind元素预处理参数来查询员工信息
*/
Emp selectByDynParamsBind(@Param("ename") String ename,@Param("sal")BigDecimal sal );
实现
<select id="selectByDynParamsBind" resultType="emp">
<bind name="p_ename" value="'%'+ename+'%'"/>
<bind name="p_sal" value="sal+1000.00"/>
select * from emp
<trim prefix="where" prefixOverrides="and">
and ename like (#{p_ename})
</trim>
and sal = #{p_sal}
</select>
测试
/**
* 动态SQL-bind
*/
BigDecimal bd = new BigDecimal(4000.00);
emp = empMapper.selectByDynParamsBind("KING",bd);
System.out.println(emp.getJob());
结果
2017-01-05 23:08:18,783 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsBind] - ==> Preparing: select * from emp where ename like (?) and sal = ?
2017-01-05 23:08:18,786 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsBind] - ==> Parameters: %KING%(String), 5000.0(BigDecimal)
2017-01-05 23:08:18,791 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsBind] - <== Total: 1
PRESIDENT
trim
属性 | 实例 | 作用 |
---|---|---|
prefix | <trim prefix="where"> and empno =#{empno} </trim> | 当语句中没有where时,自动添加 |
prefixOverrides | <trim prefixOverrides="and">and empno =#{empno} </trim> | 当因为前缀多余and而发生错误时,将其覆盖或删除 |
suffix | 略 | 后缀,与prefix刚好相反,比如update语句中的逗号 |
suffixOverrides | 略 | 后缀,与prefixOverrides刚好相反,比如update语句中的逗号 |