1. select
我们还是先来说最重要的 select ,这里面编写的都是查询语句,并且编写的 SQL 中可以根据需求,设置需要传入的参数占位符,以及动态 SQL 。下面我们先来举几个例子:
1.1 简单SQL
之前我们编写的 findById
,对应的 SQL 就非常简单:
select * from tbl_department where id = #{id}
这里面唯一一个动态的部分就是 #{id}
,它代表的是从参数中取一个名为 id
的值(当然如果 parameterType 为 string 等基本类型时,该参数名一般无意义,仅代表占位符),并填充进去。
学过 MyBatis 的小伙伴都清楚,使用 #{}
的方式,可以有效避免 SQL 注入的问题,那具体底层是怎么办的呢?这个问题我们先留下悬念,后面解析原理时会读到。
1.2 动态条件
据我调查,不少小伙伴在编写动态 where 条件时,会这么写:
<select id="findAllDepartment" parameterType="Department" resultType="Department">
select * from tbl_department
where 1 = 1
<if test="id != null">
and id = #{id}
</if>
<if test="name != null">
and name like concat('%', #{name}, '%')
</if>
</select>
这种写法虽然可行,但 MyBatis 早就帮我们考虑好这种情况了,于是人家提供了一个 <where>
标签:
<select id="findAll" parameterType="Department" resultType="Department">
select * from tbl_department
<where>
<if test="id != null">
and id = #{id}
</if>
<if test="name != null">
and name like concat('%', #{name}, '%')
</if>
</where>
</select>
而且这个 <where>
标签就是一个光秃秃的标签,没有任何属性,就是为了帮我们构造 where 的,而且也帮我们除去了第一个不必要的 and ,防止出现下面的情况:
select * from tbl_department where and id = #{id} and name like concat('%', #{name}, '%')
1.3 if标签
下面我们来逐个扫一遍常用的动态 SQL 标签。使用最频繁的当属 if 标签了,借助 MyBatis 对 OGNL (对象导航语言)的支持,可以在 if 标签的 test 中大展身手。下面我们来举一个例子:
<select id="findAllUser" parameterType="User" resultMap="userMap">
select * from tbl_user
<where>
<if test="id != null">
and id = #{id}
</if>
<!-- 借助OGNL直接获取department对象的id属性 -->
<if test="department.id != null">
and department_id = #{department.id}
</if>
<!-- 借助OGNL直接调用字符串的trim()方法 -->
<if test="name != null and name.trim() != ''">
and name like concat('%', #{name}, '%')
</if>
<!-- 包括可以直接调用静态常量、方法,使用运算符等 -->
</where>
</select>
想必各位都是用 if 的一把好手了,小册也不过多介绍 if 的使用了 ~
1.4 trim标签
小伙伴们在刚开始学习 MyBatis 的时候,老师也好,教程也好,都会告诉各位,用 <where>
标签配合 <if>
标签使用时,and 都要写在 <if>
标签体的前面,如果写到后面:
<select id="findAllDepartmentUseTrim" parameterType="Department" resultType="Department">
select * from tbl_department
<where>
<if test="id != null">
id = #{id} and
</if>
<if test="name != null">
name like concat('%', #{name}, '%') and
</if>
</where>
</select>
会因为整条 SQL 的最后还附带了一个多余的 and 而导致出现 SQL 语法错误!而 <where>
标签本身是不会处理整条 SQL 最后部分多余的 and ,所以我们需要另外的办法处理。当然,最好的办法是不要这么写,但真遇到这种“奇葩”写法,MyBatis 也给出了相应的解决方案:使用 <trim>
标签。
<trim>
标签,译为“修剪”,它可以修剪标签体中多余的内容,或者在标签体的整段 SQL 的前后添加额外的内容。这句话听起来比较绕,我们先来看一个示例,用 <trim>
标签完成与 <where>
标签一致的工作:
<select id="findAllDepartmentUseTrim" parameterType="Department" resultType="Department">
select * from tbl_department
<!-- 使用trim代替where -->
<trim prefix="where" prefixOverrides="and" suffix="" suffixOverrides="">
<if test="id != null">
and id = #{id}
</if>
<if test="name != null">
and name like concat('%', #{name}, '%')
</if>
</trim>
</select>
可以发现 <trim>
标签有 4 个属性:
- prefix :在整个标签前面附加指定内容
- prefixOverrides :去掉标签体内第一个指定的关键字
- suffix :在整个标签最后追加指定内容
- suffixOverrides :去掉标签体内最后一个指定的关键字
上面的示例中用到了前两个,在整段 <trim>
标签之前添加一个 where ,并在如果 id 属性不为空时,剪掉第一个 and ,刚刚好与 <where>
标签的作用一致。
那既然搞明白作用,如果用来解决上面提到的,每一个 if 的最后追加 and ,也就有办法解决了:
<select id="findAllDepartmentUseTrim" parameterType="Department" resultType="Department">
select * from tbl_department
<!-- 使用trim代替where -->
<trim prefix="where" prefixOverrides="" suffix="" suffixOverrides="and">
<if test="id != null">
id = #{id} and
</if>
<if test="name != null">
name like concat('%', #{name}, '%') and
</if>
</trim>
</select>
只需要用 suffixOverrides
属性,去掉最后一个 and 即可。
1.5 choose、when、otherwise标签
使用 <where>
配合 <if>
标签,已经可以满足日常开发的绝大多数场景的需求了,不过还有一种情况是用 <if>
很难解决的:如果一条查询中有多个条件,每次只让其中一个条件生效(类似于 if - elseif - else ),这样用 <if>
确实很难。MyBatis 同样考虑到了这种情况,于是给了我们另外 3 个标签来解决。
我们先来假设一个需求:Department
的 id
、name
、tel
属性,只能同时用一个属性查询,从上到下,谁不为 null 用谁。
按照这个需求,用 Java 编写的伪代码应该如下:
if (id != null && !("".equals(id))) {
// ......
} else if (name != null && !("".equals(name))) {
// ......
} else {
// ......
}
对应的,在 MyBatis 中就应该这样写:
<select id="findAllDepartmentUseChoose" parameterType="Department" resultType="Department">
select * from tbl_department
<choose>
<when test="id != null and id != ''">
where id = #{id}
</when>
<when test="name != null and name != ''">
where name like concat('%', #{name}, '%')
</when>
<otherwise>
where tel = #{tel}
</otherwise>
</choose>
</select>
正正好好,三个标签全部都能用得上,而且都很简单,小伙伴们自行测试一下效果即可。
1.6 foreach
<foreach>
,这也是我们比较常用的标签之一了,它都是用于集合的迭代遍历。最常见的情景是 in 查询:
<select id="findAllDepartmentUseForeach" parameterType="list" resultType="Department">
select * from tbl_department
where id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
除此之外,foreach 还有一种比较 “奇葩” 的使用方式:利用 Map
更新数据,这个我们过会聊到 update 时再说。
1.7 bind
<bind>
标签我们应该之前都没用到过,它的作用是为当前 statement 的上下文中绑定一个新的变量,类似于我们在方法体中声明一个新的变量。我们也可以来通过一个简单的案例来演示一下。
这种需求还是有的,比方说,我们要实现根据 name 模糊查询所有部门,那全模糊的话就需要在 name 的属性前后拼接 '%'
符号,如果是平常编写的话,我们可能会这样写:(借助数据库的 concat 函数拼接字符串)
<select id="findAllDepartment" parameterType="Department" resultMap="department">
select * from tbl_department
where name like concat('%', #{name}, '%')
</select>
或者这样,使用 ${}
避免额外的单引号:(但这样可能会引起 SQL 注入的问题)
<select id="findAllDepartment" parameterType="Department" resultMap="department">
select * from tbl_department
where name like '%${name}%'
</select>
除了这两种写法,我们还可以直接声明一个新的变量,用来拼接全模糊的 % 符号:
<select id="findAllDepartmentUseBind" parameterType="Department" resultType="Department">
<bind name="namelike" value="'%' + _parameter.getName() + '%'"/>
select * from tbl_department
where name like #{namelike}
</select>
注意看这里面 <bind>
的使用,它的声明是一个 name 一个 value 的形式,而 value 中就可以拿到传入的 Department
参数的 name
属性,然后拼接字符串了。这种取上下文参数的方式比较特别,它实际上是用到一个内置的变量 _parameter
,配合 OGNL 表达式来操纵获取的。
bind 声明变量完成后,下面的 SQL 语句中,我们就可以直接使用 #{}
获取到这个 namelike 的变量了。下面我们可以编写一下测试代码看一下效果:(这里只贴出了关键部分测试代码)
Department department = new Department();
department.setName("产品");
List<Department> departmentList = sqlSession.selectList("dynamic.findAllDepartmentUseBind", department);
departmentList.forEach(System.out::println);
运行测试代码,观察控制台的 SQL 打印,以及查询返回的结果,都与我们的预期一致。
[main] DEBUG namic.findAllDepartmentUseBind - ==> Preparing: select * from tbl_department where name like ?
[main] DEBUG namic.findAllDepartmentUseBind - ==> Parameters: %产品%(String)
[main] DEBUG namic.findAllDepartmentUseBind - <== Total: 1
Department{id='53e3803ebbf4f97968e0253e5ad4cc83', name='测试产品部', tel='789'}
有关 <select>
标签中用到的动态 SQL 差不多就这么多,下面我们继续聊有关 <update>
中的动态 SQL 标签。
2. update
之所以拿 <update>
标签说事,不光是它里面包含一个特殊的 <set>
标签,还有一部分,是因为 <insert>
、<delete>
标签用到的,在 <update>
中也都能用得到。so 下面我们聊聊使用在 <update>
标签中的动态 SQL 。
2.1 set
与 <where>
标签类似,<set>
标签本身也是为了解决类似 select 的时候,where 不好抹去 and 的问题,只不过 <set>
标签是抹去最后一个逗号。下面是一个简单的示例:
<update id="updateDepartment" parameterType="Department">
update tbl_department
<set>
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="tel != null and tel != ''">
tel = #{tel},
</if>
</set>
where id = #{id}
</update>
看最后一个 if 标签,tel = #{tel}
的最后还有一个 , 但最终 <set>
会帮我们把这个多余的 , 去掉。
之后我们可以来简单测试一下效果,测试代码编写起来也很简单:
Department department = new Department();
department.setId("53e3803ebbf4f97968e0253e5ad4cc83");
department.setName("测试部");
sqlSession.update("dynamic.updateDepartment", department);
运行测试方法,观察控制台的日志打印:
[main] DEBUG dynamic.updateDepartment - ==> Preparing: update tbl_department SET name = ? where id = ?
[main] DEBUG dynamic.updateDepartment - ==> Parameters: 测试部(String), 53e3803ebbf4f97968e0253e5ad4cc83(String)
[main] DEBUG dynamic.updateDepartment - <== Updates: 1
可见 SQL 可以正确构造和执行,没有多余的逗号。
另外,有了上面的 <trim>
标签的铺垫,想必小伙伴也能联想到,跟 <set>
等价的标签应该是这样的:
<trim prefix="SET" suffixOverrides=",">
</trim>
2.2 foreach
接上上面 select 小节提到的话茬,在 update 中可以巧妙利用模型类转 Map 这样的思路,配合 <foreach>
标签,可以将 update 简化。下面我们来具体实现一下。
2.2.1 实体类转Map
有关实体类如何转为 Map
,可以借助很多工具(如 Cglib 、MapStruct 、Jackson 等),也可以自己写。这里我们直接引入 Cglib 的 jar 包吧:
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>3.1</version>
</dependency>
然后我们就可以来试着用一下了,Cglib 中让实体类对象转 Map
非常简单,只需要调用 BeanMap.create
即可:
Department department = new Department();
department.setName("测试部");
BeanMap beanMap = BeanMap.create(department);
以此法转换后的 beanMap 控制台打印如下:
{name=测试部, tel=null, id=null, users=null}
可以发现确实是成为 Map
的样子了 ( public abstract class BeanMap implements Map
)。
然后,我们构造一下参数,由于封装后的 beanMap 不支持 put
和 remove
操作,所以我们上面构造 Department
对象时没有把 id 塞进去(不然接下来就麻烦了),而是单独把 id 拿出来:
Department department = new Department();
department.setName("测试部");
BeanMap beanMap = BeanMap.create(department);
Map<String, Object> departmentMap = new HashMap<>(2);
departmentMap.put("id", "53e3803ebbf4f97968e0253e5ad4cc83");
departmentMap.put("beanMap", beanMap);
sqlSession.update("dynamic.updateDepartmentByMap", departmentMap);
2.2.2 SQL编写
下面该编写 SQL 了,这里面我们就可以利用 foreach 的特性,巧妙地做到有属性值的更新,没有不更新:
<update id="updateDepartmentByMap" parameterType="map">
update tbl_department
<foreach collection="beanMap" index="key" item="value" open="set " separator=",">
<if test="value != null">
${key} = #{value}
</if>
</foreach>
where id = #{id}
</update>
注意,foreach 在循环 Map
时,键值对的 key 是 index
,value 是 item
。
2.2.3 测试运行
OK ,下面我们来测试效果,运行 main 方法,观察控制台打印的 SQL :
[main] DEBUG dynamic.updateDepartmentByMap - ==> Preparing: update tbl_department set name = ? where id = ?
[main] DEBUG dynamic.updateDepartmentByMap - ==> Parameters: 测试部(String), 53e3803ebbf4f97968e0253e5ad4cc83(String)
[main] DEBUG dynamic.updateDepartmentByMap - <== Updates: 1
发现只有 name
被更新了。
如果测试代码中将 tel
也赋值:
department.setName("测试部");
department.setTel("12345679");
则打印的 SQL 中也会带进去 tel
:
[main] DEBUG dynamic.updateDepartmentByMap - ==> Preparing: update tbl_department set name = ? , tel = ? where id = ?
[main] DEBUG dynamic.updateDepartmentByMap - ==> Parameters: 测试部(String), 12345679(String), 53e3803ebbf4f97968e0253e5ad4cc83(String)
[main] DEBUG dynamic.updateDepartmentByMap - <== Updates: 1
可能小伙伴会觉得,这种设计方法是不是太过于繁琐?明明用实体类就可以解决的,为啥非要兜兜转转这么一大圈?哎,如果小伙伴还有这种想法,可能是通用抽取的意识还不是很强,后面到了二次封装部分,我们会展示一下如何制作通用的 update statement 。
3. <sql>
最后一个要说的就是可以单独抽取出来,复用的 <sql>
了。SQL 片段的抽取,常用于一些容易重复出现的 SQL 语句片段,或者可以制作为通用 SQL 的片段。下面我们来举几个例子。
3.1 抽取表字段
最常见的一种写法,就是将查询的数据库表中的列都罗列出来,以代替 select * (如果直接用 * 查的话,会影响一部分性能),这种写法估计我们刚开始学习 MyBatis 的时候都用过吧!
下面是一个简单示例,示例中我们把 tbl_department
表中的所有列都列出,并使用 include 标签引用这些列。
<select id="findAllUseSql" resultType="map">
select <include refid="columns"/> from tbl_department
</select>
<sql id="columns">
id, name, tel
</sql>
3.2 抽取条件查询判断结构
另一个常用的写法,是在 where 查询时,针对某张表的属性,我们可以提前把可能出现的列过滤条件都罗列好,后面再写 select 时就可以直接 include 过来,比方说这样:
<select id="findAllUseSql" resultType="map">
select <include refid="columns"/> from tbl_department
<where>
<include refid="whereconditions"/>
</where>
</select>
<sql id="whereconditions">
<if test="id != null and id != ''">
and id = #{id}
</if>
<if test="name != null">
and name like concat('%', #{name}, '%')
</if>
</sql>
3.3 传入指定参数
注意一点,SQL 语句片段也是可以接收 <include>
标签传递的参数值的!<include>
标签并不是完全自闭合的,它里面有 <property>
标签可以写。下面我们也举一个场景的例子。
比方说,我们在抽取一个表的字段时,可能查询结果中不会带 id 属性(仅仅是为了场景演示的假设哈),这种情况下我们可以调整一下抽取的 SQL :
<sql id="columns">
name, tel
<if test="${includeId} != null and ${includeId} == true">
, id
</if>
</sql>
这个 includeId 属性,就是我们自己定义的,需要在 include 的时候传入的属性(当然不传那就默认 null )。注意一点,引用属性时必须写 ${}
,无论是在 SQL 还是在 if 的 test 判断中,都要用 ${}
的方式引用。
相应的,在 select 中引用该 SQL 片段,就应该注入 includeId 属性并为其赋值:
<select id="findAllUseSql" resultType="map">
select
<include refid="columns">
<property name="includeId" value="true"/>
</include>
from tbl_department
</select>
这样写好之后,我们来编写一个简单的测试代码:
List<Object> list = sqlSession.selectList("dynamic.findAllUseSql", Collections.emptyMap());
list.forEach(System.out::println);
运行测试,控制台打印的输出结果如下:
{name=全部部门, tel=-, id=00000000000000000000000000000000}
{name=开发部, tel=123, id=18ec781fbefd727923b0d35740b177ab}
{name=测试产品部, tel=789, id=53e3803ebbf4f97968e0253e5ad4cc83}
{name=运维部, tel=456, id=ee0e342201004c1721e69a99ac0dc0df}
可见带着 id 属性。那如果我们把 includeId 属性值改为 false 呢?
<select id="findAllUseSql" resultType="map">
select
<include refid="columns">
<property name="includeId" value="false"/>
</include>
from tbl_department
</select>
再次运行测试代码,这次打印的 Map 中就没有 id 属性了,由此可以体现 SQL 片段的可配置属性特征。
{name=全部部门, tel=-}
{name=开发部, tel=123}
{name=测试产品部, tel=789}
{name=运维部, tel=456}
但是有一点要注意啊,如果 SQL 片段中声明了需要这个 includeId
属性,那么 include 的时候就一定要传进去,否则会在程序运行期抛出异常:ParseException: Encountered " "$" "$ "" at line 1, column 1.
。