MyBatis 动态 SQL
动态 SQL 是 MyBatis 强大的特性之一,利用提供的动态 SQL 标签,可以很灵活地对 SQL 进行二次表述,这些标签的用法很像是在 HTML 文本中插入 JSP 标签的用法;
以下是 MyBatis 3.X 支持的几组标签:
- if:条件判断
- choose(when,otherwise):条件判断
- trim(where、set):sql 子句生成
- foreach:迭代器
- bind:上下文变量绑定
if 用法
if 标签用于条件判断,用法很类似于 JSP 的 if 标签,以下是在一个实体映射配置文件中的使用示例:
<!--使用id,name,createDate 属性合查找 User-->
<select id="getUser" resultMap="userMap">
select user_id,user_name,user_password,create_date from users
where 1 = 1
<if test="id != null">
and user_id = #{id}
</if>
<if test="name != null and name != ''">
and user_name = #{name}
</if>
<if test="createDate != null">
and create_date = #{createDate, jdbcType=DATE}
</if>
</select>
<!--使用 name,password 属性组合更新 User-->
<update id="updateUserById">
update users
set
<if test="name != null and name != ''">
user_name = #{name},
</if>
<if test="password != null and name!= ''">
user_password = #{password}
</if>
where user_id = #{id}
</update>
以上配置对应的2个接口方法签名如下:
User getUser(User user);
int updateUserById(User user);
示例来说 getUser 方法可以匹配以下的 User 属性入参并转换为相应的具体 SQL :
- 当 User(id=2011) ,转换为 “select ... from users where 1=1 and user_id = 2011”;
- 当 User(name="assad"),转换为 “select ... from users where 1=1 and user_name = 'assad'”
- 当 User(id= 2011, name="assad"),转换为 “select ... from users where 1=1 and user_id = 2011 and user_name = 'assad'”
- 当 User(null),转换为 “select ... from users where 1=1 "
当 User 属性都为空时候,返回的可能会是一个 User 列表,所以这样的逻辑编写是不严谨的,可以借助下面的choose 标签来进一步完善这个逻辑;
choose(when,otherwise)用法
choose-when-otherwise 用于表述 if-else 逻辑,choose 元素含有子元素 when(必须有一个),otherwise(可以不含),以下示例:
<!--使用 id 或 name 作为入参查找 User -->
<select id="getUserByIdOrName" resultMap="userMap">
select user_id,user_name,user_password,create_date from users
where 1=1
<choose>
<when test="id != null">
and user_id = #{id}
</when>
<when test="name != null and name != ''">
and user_name = #{name}
</when>
<otherwise>
and 1 = 2
</otherwise>
</choose>
</select>
以上配置对应的接口方法签名如下:
User getUserByIdOrName(User user)
示例来说,以下是入参 user 的属性和转化的对应 SQL 语句:
- User(id=123) --- “select ... from users where 1=1 and user_id = 123"
- User(name="assad") --- “select ... from users where 1=1 and user_name = 'assad' "
- User(id= 123, name="assad") --- “select ... from users where 1=1 and user_id = 123 and user_name = 'assad'”
- User(null) --- “select ... from users where 1=1 and 1=2”
以上示例的表述逻辑已经没有问题了,但是在 where 子句存在像 “1=1,1=2” 这样纯粹为了占位为存在的难看代码,此时可以使用下面的 where 标签来解决这个问题;
trim(where,set)用法
where 用法
where 标签用于快速创建 SQL where 子句,如果该标签内包含的元素中有返回值,就插入一个 where 子句,如果 该插入的 where 子句后面的字符串是以 AND 和 OR 开头的,就将其剔除;
以下示例进一步优化以上示例的 getUser 接口方法:
<!--多种入参组合查找 User-->
<select id="getUser" resultMap="userMap">
select user_id,user_name,user_password,create_date from users
<where>
<if test="id != null">
and user_id = #{id}
</if>
<if test="name != null and name != ''">
and user_name = #{name}
</if>
<if test="createDate != null">
and create_date = #{createDate, jdbcType=DATE}
</if>
</where>
</select>
当 if 条件不满足的时候,where 标签中没有内容,SQL 中不会出现 where 子句;当 if 条件满足,where 标签中的内容以 and 开头,也会被自动剔除该开头的 and,保证 SQL where 子句的正确;
set 用法
set 标签用于创建 SQL set 子句,
如果该标签内包含的元素中有返回值,就插入一个 set 子句,如果 set 子句后面的字符串以逗号结尾,就将该逗号剔除;
以下示例优化以上示例中的 updateUserById 接口方法可能 set 子句末尾部出现逗号的不合法行为:
<!--更新User-->
<update id="updateUserById2">
update users
<set>
<if test="name != null and name != ''">
user_name = #{name},
</if>
<if test="password != null and name!= ''">
user_password = #{password},
</if>
</set>
where user_id = #{id}
</update>
trim 用法
where 和 set 两种标签都可以使用 trim 标签表示,在底层那就是通过 TrimSqlNode 实现的;
<!--where 标签对应的 trim 实现-->
<trim prefix="WHERE" prefixOverrides="AND | OR">
.......
</trim>
<!--set 标签对应的 trim 实现-->
<trim prefix="SET" suffixOverrides=",">
........
</trim>
trim 标签含有以下属性:
- prefix:给 trim 标签内的内容 prefix 指定的前缀;
- prefixOverrides:把 trim 标签内的内容匹配的 prefixOverrides 指定前缀剔除;
- suffix:给 trim 标签内的内容 suffix 指定的后缀;
- suffixOverrides:把 trim 标签内的内容匹配的 suffixOverrides 指定前缀剔除;
foreach 用法
foreach 标签用于进行迭代操作,
包含的属性如下:
- collection:必选,要进行迭代操作的属性名;
- item:迭代元素的变量名;
- index:迭代索引的变量名,在array,list 类型时值为当前索引值,map 类型值为当前的key值;
- open:整个循环内容开始的字符串;
- close:整个循环内容结尾的字符串;
- separator:每次循环的分隔符;
- 参数为数组类型,使用值 “array”;
- 参数为列表类型,使用值 “list”;
- 参数为Map类型,指定为 Map 中的 key 列表,或者使用默认值 “_parameter”;
foreach 实现 SQL in 集合
SQL 有 IN 关键字用于表示集合成员关系,如 id IN (1,2,3),但是如果使用 EL 表达式注入参数变量到 SQL 的方 式,无法防止外界的 SQL 注入,mybatis 提供了 foreach 可以用于满足这种需求;
<!--根据id 集合查询 User集合 -->
<select id="getUserByIdList" resultMap="userMap">
select user_id,user_name,user_password,create_date from users
where user_id in
<foreach collection="list" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
</select>
以上配置实现地接口方法的签名:
List<User> getUserByIdList(List<User> idList);
foreach 实现批量 insert
SQL-92 新增了批量插入的特性,目前支持的数据库包括:DB2、SQL Server 2008+、MySQL、SQLite 3.7.11 +,语法如下:
INSERT INTO tablename (column-a [, column-b, ...])
VALUES (value-1a, [value-1b, ...]),
(value-2a, [value-2b, ...]),
.......
foreach 标签可以支持这样的语法,如下示例:
<!--实现批量插入,使用 List 作为入参类型-->
<insert id="addUserList">
insert into users(user_name,user_password,create_date)
values
<foreach collection="list" item="user" separator=",">
(#{user.name}, #{user.password}, #{user.createDate, jdbcType=DATE})
</foreach>
</insert>
以上配置对应的接口方法签名如下:
int addUserList(List<User> userList);
foreach 实现动态 update
当参数类型为 Map 时候,可以使用 foreach 实现批量更新操作,示例如下:
<!-- 更新 User,使用 Map 作为入参类型 -->
<update id="updateUserByMap">
update users
set
<foreach collection="_parameter" item="value" index="key" separator=",">
${key} = #{value}
</foreach>
where id = #{id}
</update>
以上配置对应的接口方法签名如下:
int addUserList(Map<String,Object> userMap);
bind 用法
bind 标签可以使用 OGNL 表达式创建一个变量,并将其绑定到上下文中,如下示例:
<!-- 以名称近似查询 User -->
<select id="getUserLikeName">
select user_id,user_name,user_password,create_date from users
<bind name="nameLikeStr" value="'%' + name + '%'" />
where user_name like #{nameLikeStr}
</select>
找 MyBatis 中常用的 OGNL 表达式有以下:
- e1 or e2 、 e1 and e2
- e1 == e2 或 e1 eq e2 、 e1 != e2 或 e1 neq e2
- e1 lt e2 (小于)、e1 lte e2(小于等于), 同样 gt(大于)、 gte(大于等于)
- e1 + e2、e1 - e2、e1 * e2、e1 / e2、e1 % e2
- !e 或 not e (非)
- e.method(args) : 调用对象方法
- e.property : 调用对象属性值
- e1[e2] : 按索引取值(List、array、Map)
- @class@method(args) : 调用类的静态方法
- @class@field : 调用类的静态属性字段