本文是Mybatis基础系列的第三篇文章,点击下面链接可以查看前面的文章:
mybatis基础系列(二)——基础语法、别名、输入映射、输出映射
动态sql
MyBatis 的强大特性之一便是它的动态 SQL。摆脱了JDBC中根据不同条件拼接 SQL 语句的痛苦。动态 SQL可以帮我们解决复杂需求。mybatis 动态SQL,通过 if, choose, when, otherwise, trim, where, set, foreach等标签组合成非常灵活的SQL语句。
根据员工编号或者员工姓名查询员工信息,输入的员工编号可能为空。
<select id="queryByEnameOrEempno" parameterType="com.itpsc.request.EmpRequest" resultType="com.itpsc.vo.EmpVo"> SELECT * FROM t_emp WHERE <if test="emp.ename!=null"> ename=#{emp.ename} </if> <if test="emp.empno!=null"> and empno=#{emp.empno} </if> </select>
执行结果:
编号为空 ==> Preparing: SELECT * FROM t_emp WHERE ename=? ==> Parameters: itpsc(String) <== Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno <== Row: 7100, itpsc, developer, 7902, 1980-01-10, null, 1000.00, 20 <== Total: 1 姓名为空 ==> Preparing: SELECT * FROM t_emp WHERE and empno=? ==> Parameters: 7100(Integer)
从执行结果可以看出,上面的if语句只能是在员工姓名不能为空的情况下执行,如果员工姓名为空sql语句就出错了,If+where语句可以解决这个问题。
if+where语句与trim 语句
<select id="queryByEnameOrEempno" parameterType="com.itpsc.request.EmpRequest" resultType="com.itpsc.vo.EmpVo"> SELECT * FROM t_emp <where> <if test="emp.ename!=null"> and ename=#{emp.ename} </if> <if test="emp.empno!=null"> and empno=#{emp.empno} </if> </where> </select>
执行结果:
姓名为空 ==> Preparing: SELECT * FROM t_emp WHERE empno=? ==> Parameters: 7100(Integer) <== Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno <== Row: 7100, itpsc, developer, 7902, 1980-01-10, null, 1000.00, 20 <== Total: 1 编号为空 ==> Preparing: SELECT * FROM t_emp WHERE ename=? ==> Parameters: itpsc(String) <== Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno <== Row: 7100, itpsc, developer, 7902, 1980-01-10, null, 1000.00, 20 <== Total: 1
<where>标签中包含的标签中有返回值的话,它就插入一个where关键字,并且where 标签会自动将其后第一个条件的and或者是or给忽略掉。
trim语句可以实现<where>标签类似的功能:
(1)trim标签可以在包含的内容前加上前缀,也可以在其后加上后缀,对应的属性是prefix和suffix;
(2)trim标签可以把包含内容的首部某些内容忽略,也可以把尾部的某些内容忽略,对应的属性是prefixOverrides和suffixOverrides。
<select id="queryByEnameOrEempno" parameterType="com.itpsc.request.EmpRequest" resultType="com.itpsc.vo.EmpVo"> SELECT * FROM t_emp <trim prefix="where" prefixOverrides="and | or"> <if test="emp.ename!=null"> and ename=#{emp.ename} </if> <if test="emp.empno!=null"> and empno=#{emp.empno} </if> </trim> </select>
执行结果:
empno为空 ==> Preparing: SELECT * FROM t_emp where ename=? ==> Parameters: itpsc2(String) <== Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno <== Row: 7101, itpsc2, developer, 7902, 1980-01-10, 2000.00, 1000.00, 20 <== Total: 1 ename为空 ==> Preparing: SELECT * FROM t_emp where empno=? ==> Parameters: 7100(Integer) <== Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno <== Row: 7100, itpsc1, mannager, 7902, 1980-01-10, null, 1000.00, 20 <== Total: 1 empno、ename都不为空 ==> Preparing: SELECT * FROM t_emp where ename=? and empno=? ==> Parameters: itpsc2(String), 7100(Integer) <== Total: 0
if+set语句与trim语句
条件判断用<where>标签,同理更新操作用<set>标签。
<select id="updateEnameOrJob" parameterType="com.itpsc.request.EmpRequest"> UPDATE t_emp <set> <if test="emp.ename!=null"> ename=#{emp.ename} </if> <if test="emp.job!=null"> job=#{emp.job} </if> </set> WHERE empno=#{emp.empno} </select>
运行结果:
job为空 ==> Preparing: UPDATE t_emp SET ename=? WHERE empno=? ==> Parameters: hello(String), 7100(Integer) ename 为空 ==> Preparing: UPDATE t_emp SET job=? WHERE empno=? ==> Parameters: itpsc(String), 7100(Integer)
<set>标签中包含的标签中有返回值的话,它就插入一个set关键字。job和ename都不为空则sql错误,那又如何写呢,trim标签可以帮我们能实现。
<select id="updateEnameOrJob" parameterType="com.itpsc.request.EmpRequest"> UPDATE t_emp <trim prefix="set" suffixOverrides=","> <if test="emp.ename!=null"> ename=#{emp.ename}, </if> <if test="emp.job!=null"> job=#{emp.job}, </if> </trim> WHERE empno=#{emp.empno} </select>
运行结果:
==> Preparing: UPDATE t_emp set ename=?, job=? WHERE empno=? ==> Parameters: itpsc(String), mannager(String), 7100(Integer)
choose(when,otherwise) 语句
上的例子中,job和ename都不为空则是一种条件,job为空且ename不为空是一种条件,ename为空且job不为空是一种条件。实际上,我们要只要满足任意一个条件,就可以执行。choose(when,otherwise) 语句可以帮我们解决,类似jstl。
<select id="updateEnameOrJob" parameterType="com.itpsc.request.EmpRequest"> UPDATE t_emp <set> <choose> <when test="emp.ename!=null and emp.job!=null"> ename=#{emp.ename},job=#{emp.job} </when> <otherwise> <if test="emp.ename!=null"> ename=#{emp.ename} </if> <if test="emp.job!=null"> job=#{emp.job} </if> </otherwise> </choose> </set> WHERE empno=#{emp.empno} </select>
执行结果:
ename为空且job不为空 ==> Preparing: UPDATE t_emp SET job=? WHERE empno=? ==> Parameters: mannager(String), 7100(Integer) job为空且ename不为空 ==> Preparing: UPDATE t_emp SET ename=? WHERE empno=? ==> Parameters: itpsc2(String), 7100(Integer) job和ename都不为空 ==> Preparing: UPDATE t_emp SET ename=?,job=? WHERE empno=? ==> Parameters: itpsc2(String), mannager(String), 7100(Integer)
foreach 语句
需求:根据员工编号列表查询员工信息。7369,7499,7521。
Sql的写法是:select * from t_emp where 1=1 and empno=7369 or empno=7499 or empno=7521
mybatis的foreach语句可以帮我们实现类似功能。
<select id="queryByIds" parameterType="com.itpsc.request.IdRequest" resultType="com.itpsc.vo.EmpVo"> SELECT * FROM t_emp <where> <foreach collection="ids" item="id" open="and (" close=")" separator="or"> empno=#{id} </foreach> </where> </select>
collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
运行结果:
==> Preparing: SELECT * FROM t_emp WHERE ( empno=? or empno=? or empno=? ) ==> Parameters: 7369(Integer), 7499(Integer), 7521(Integer) <== Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno <== Row: 7369, SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20 <== Row: 7499, ALLEN, SALESMAN, 7698, 1981-02-20, 1600.00, 300.00, 30 <== Row: 7521, WARD, SALESMAN, 7698, 1981-02-22, 1250.00, 500.00, 30 <== Total: 3
sql片段
将上面动态sql代码块抽取出来,组成一个sql片段,其它的statement中就可以引用该sql片段。提供代码复用性,方便团队成员之间进行开发。
<select id="queryCount2" parameterType="com.itpsc.request.EmpRequest" resultType="int"> SELECT count(*) FROM t_emp <where> <if test="emp!=null"> <if test="emp.deptno!=null"> and emp.deptno=#{emp.deptno} </if> <if test="emp.ename!=null"> and emp.ename=#{emp.ename} </if> <if test="emp.job!=null"> and emp.job=#{emp.job} </if> </if> </where> </select>
上面where语句中代码,我们可以通过<sql>标签封装成一个sql片段,然后在其它statement中通过<include>引用。
<sql id="querySql"> <if test="emp!=null"> <if test="emp.deptno!=null"> and emp.deptno=#{emp.deptno} </if> <if test="emp.ename!=null"> and emp.ename=#{emp.ename} </if> <if test="emp.job!=null"> and emp.job=#{emp.job} </if> </if> </sql> <select id="queryCount2" parameterType="com.itpsc.request.EmpRequest" resultType="int"> SELECT count(*) FROM t_emp <where> <include refid="querySql"></include> </where> </select>
本文到此,下篇 mybatis基础系列(四)——关联查询、延迟加载、一级缓存与二级缓存。