持续创作,加速成长!这是我参与「掘金日新计划 · 6 月更文挑战」的第29天,点击查看活动详情
动态SQL
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
if
先来看看第一个动态SQL标签——if,通过if标签,我们能够根据我们传递的参数进行动态查询,比如:根据id查询员工信息,若是携带了哪个参数sql的条件就要带上这个参数对应的字段的值。
<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
select *
from tbl_employee
where
<if test="id != null">
id=#{id}
</if>
<if test="lastName != null and lastName != ''">
and last_name like #{lastName}
</if>
<if test="email != null and email.trim() != ''">
and email = #{email}
</if>
<if test="gender == 0 or gender == 1">
and gender = #{gender}
</if>
</select>
测试代码:
EmployeeDynamicSqlMapper mapper = sqlSession.getMapper(EmployeeDynamicSqlMapper.class);
Employee employee = new Employee(1, "%c%", null, "jack@qq.com");
List<Employee> emps = mapper.getEmpsByConditionIf(employee);
for (Employee emp : emps) {
System.out.println(emp);
}
我们来看看它生成的sql:
==> Preparing: select * from tbl_employee where id = ? and last_name like ? and email = ?
==> Parameters: 1(Integer), %c%(String), jack@qq.com(String)
<== Columns: id, last_name, gender, email, d_id
<== Row: 1, jack, 1, jack@qq.com, 1
<== Total: 1
Employee(id=1, lastName=null, gender=1, email=jack@qq.com)
然而这样的代码是有问题的,如果我们的条件是这样的:
Employee employee = new Employee(null, "%c%", null, "jack@qq.com");
来看看生成的sql:
==> Preparing: select * from tbl_employee where and last_name like ? and email = ?
==> Parameters: %c%(String), jack@qq.com(String)
因为id为null,所以后面的and被直接拼在了where的后面,此时sql语法就会出错,为此我们需要使用另一个标签——where,它能够完美地解决这一问题。
where
<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
select *
from tbl_employee
<where>
<if test="id != null">
id = #{id}
</if>
<if test="lastName != null and lastName != ''">
and last_name like #{lastName}
</if>
<if test="email != null and email.trim() != ''">
and email = #{email}
</if>
<if test="gender == 0 or gender == 1">
and gender = #{gender}
</if>
</where>
</select>
只需将判断条件全部放在where标签内,MyBatis就会自动将where标签拼接的sql中多出来的and和or删掉,然而where标签依然有弊端,比如这样拼接sql:
<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
select *
from tbl_employee
<where>
<if test="id != null">
id = #{id} and
</if>
<if test="lastName != null and lastName != ''">
last_name like #{lastName} and
</if>
<if test="email != null and email.trim() != ''">
email = #{email} and
</if>
<if test="gender == 0 or gender == 1">
gender = #{gender}
</if>
</where>
</select>
并且传递的参数条件是这种情况:
Employee employee = new Employee(null, "%c%", null, null);
此时last_name属性后面跟着一个and,但是接下来的属性都是null,所以得到了这样的一条sql:
==> Preparing: select * from tbl_employee WHERE last_name like ? and
==> Parameters: %c%(String)
可前面我们已经说到,where标签会自动去除多余的and和or呀,为什么还是报错呢?
原来,where标签只能去除属性前多余的and和or,至于属性后的,它就无能为力了,所以要么就将and和or都写在属性的前面,要么就使用trim标签来解决这一问题。
trim
<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
select *
from tbl_employee
<trim prefix="where" suffixOverrides="and">
<if test="id != null">
id = #{id} and
</if>
<if test="lastName != null and lastName != ''">
last_name like #{lastName} and
</if>
<if test="email != null and email.trim() != ''">
email = #{email} and
</if>
<if test="gender == 0 or gender == 1">
gender = #{gender}
</if>
</trim>
</select>
trim标签中共有四个属性,分别介绍一下作用:
- prefix:指定sql的前缀,该前缀会被放在trim标签拼接好的sql前面
- suffix:指定sql的后缀,该后缀会被放在trim标签拼接好的sql后面
- prefixOverrides:前缀覆盖,MyBatis会自动去除属性前的指定字符串
- suffixOverrides:后缀覆盖,MyBatis会自动去除属性后的指定字符串
在这里我们将前缀指定为where,并指定后缀覆盖为and,就能解决之前的问题了,执行结果:
==> Preparing: select * from tbl_employee where last_name like ?
==> Parameters: %c%(String)
<== Columns: id, last_name, gender, email, d_id
<== Row: 1, jack, 1, jack@qq.com, 1
<== Total: 1
Employee(id=1, lastName=null, gender=1, email=jack@qq.com)
choose
该标签用于分支操作,与if不同的是,choose只有一个会被执行,若有一个choose满足条件,则其它choose都将失效,比如:根据传递过来的参数进行员工信息查询,如果携带id,则根据id查询,如果携带lastName,则根据lastName查询,实现如下:
List<Employee> getEmpsByConditionChoose(Employee employee);
<select id="getEmpsByConditionChoose" resultType="com.wwj.mybatis.bean.Employee">
select *
from tbl_employee
<where>
<choose>
<when test="id != null">
id = ${id}
</when>
<when test="lastName != null">
last_name like #{lastName}
</when>
<otherwise>
</otherwise>
</choose>
</where>
</select>
choose标签类似于带break的switch语句,otherwise标签表示其它条件都不满足则执行该标签内容,编写业务代码:
Employee employee = new Employee(1, "%c%", null, null);
List<Employee> emps = mapper.getEmpsByConditionChoose(employee);
for (Employee emp : emps) {
System.out.println(emp);
}
这里同时携带了id和lastName,sql语句会如何拼接呢?
因为id的判断在lastName之前,所以id分支已经满足条件,其它分支都会失效,故sql语句如下:
==> Preparing: select * from tbl_employee WHERE id = 1
==> Parameters:
<== Columns: id, last_name, gender, email, d_id
<== Row: 1, jack, 1, jack@qq.com, 1
<== Total: 1
若是什么参数都不带,则执行otherwise,sql什么都不拼接,故查询所有数据。