动态 SQL
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
虽然在以前使用动态 SQL 并非一件易事,但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类,现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
准备
首先创建User实体类
public class User {
private Integer id;
private String username;
private String userEmail;
private String userCity;
private Integer age;
}
创建user表
CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(255) DEFAULT NULL,
user_email varchar(255) DEFAULT NULL,
user_city varchar(255) DEFAULT NULL,
age int(11) DEFAULT NULL,
PRIMARY KEY (id)
)
if
定义接口方法
public List<User> findByUser(User user);
接口对应的 Mapper.xml 定义如下所示
<select id="findByUser" resultType="com.example.mybatis.entity.User">
select
id, username, user_email userEmail, user_city userCity, age
from user
where
<if test="username != null and username != ''">
username = #{username}
</if>
<if test="userEmail != null and userEmail != ''">
and user_email = #{userEmail}
</if>
<if test="userCity != null and userCity != ''">
and user_city = #{userCity}
</if>
</select>
如果if标签上的test为true,那么if标签里面的SQL语句将会被拼接。
如果username、userEmail、userCity都不为空,那么SQL将会拼接成如下所示
select id, username, user_email userEmail, user_city userCity, age
from user
where username = ? and user_email = ? and user_city = ?
如果只有username不为空,那么SQL将会拼接成如下所示
select id, username, user_email userEmail, user_city userCity, age
from user
where username = ?
但是这种方式存在一个缺点,假设此时username为空,userEmail、userCity都不为空。
我们来分析动态 SQL 代码,现在没有给 username 赋值,即 username==null,所以 “username=#{username}” 这段代码不会添加到 SQL 语句中,那么最终拼接好的动态 SQL 是这样的:
select id, username, user_email userEmail, user_city userCity, age
from user
where and user_email = ? and user_city = ?
where 后面直接跟 and,很明显的语法错误,此时应该把紧跟在where
后面的and
删掉。为了解决这个问题,可以使用where
标签。
where
将上面的SQL改成如下所示
<select id="findByUser" resultType="com.example.mybatis.entity.User">
select
id, username, user_email userEmail, user_city userCity, age
from user
<where>
<if test="username != null and username != ''">
username = #{username}
</if>
<if test="userEmail != null and userEmail != ''">
and user_email = #{userEmail}
</if>
<if test="userCity != null and userCity != ''">
and user_city = #{userCity}
</if>
</where>
</select>
如果where
标签里面的if
标签有满足条件的,那么where
标签就会被拼接成where语句,若if
标签拼接的SQL最前面有and语句,那么这个and将会被删除。使用这种方法, 会自动删除SQL中不需要的关键字,所以一般 if 标签和 where 标签会组合起来使用。
trim
trim
标签中的 prefix
和 suffix
属性会被用于生成实际的 SQL 语句,会和标签内部的语句拼接。
如果语句的前面或后面遇到 prefixOverrides
或 suffixOverrides
属性中指定的值,MyBatis 会自动将它们删除。在指定多个值的时候,别忘了每个值后面都要有一个空格,保证不会和后面的 SQL 连接在一起。
prefix:给拼接的SQL语句加一个前缀
suffix:给拼接的SQL语句加一个后缀
prefixOverrides:拼接的SQL语句前面遇到 prefixOverrides
,MyBatis 会自动将它们删除
suffixOverrides:拼接的SQL语句后面遇到 suffixOverrides
,MyBatis 会自动将它们删除
下面使用trim
标签来实现where
标签的功能
<select id="findByUser" resultType="com.example.mybatis.entity.User">
select
id, username, user_email userEmail, user_city userCity, age
from user
<trim prefix="where" prefixOverrides="and">
<if test="username != null and username != ''">
username = #{username}
</if>
<if test="userEmail != null and userEmail != ''">
and user_email = #{userEmail}
</if>
<if test="userCity != null and userCity != ''">
and user_city = #{userCity}
</if>
</trim>
</select>
如果username为空,userEmail和userCity不为空,那么if
标签拼接的SQL语句如下所示
and user_email = #{userEmail} and user_city = #{userCity}
因为trim
标签设置了prefixOverrides=“and”,而上面的SQL前面有and语句,所以需要将上面的and语句删掉,又因为trim
标签设置了prefix=“where”,所以需要在拼接的SQL语句前面加一个where语句
最后trim
标签的SQL语句被拼接成如下所示
where user_email = #{userEmail} and user_city = #{userCity}
choose
有时我们不想应用到所有的条件语句,而只想从中择其一项。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
<select id="findByUser" resultType="com.example.mybatis.entity.User">
select
id, username, user_email userEmail, user_city userCity, age
from user
<where>
<choose>
<when test="username != null and username != ''">
username = #{username}
</when>
<when test="userEmail != null and userEmail != ''">
and user_email = #{userEmail}
</when>
<when test="userCity != null and userCity != ''">
and user_city = #{userCity}
</when>
</choose>
</where>
</select>
set
set 标签用于 Update 操作,会自动根据参数选择生成 SQL 语句。
接口定义如下
public int updateUser(User user);
接口对应的 Mapper.xml 定义如下所示
<update id="updateUser" parameterType="com.example.mybatis.entity.User">
update user
<set>
<if test="username != null and username != ''">
username=#{username},
</if>
<if test="userEmail != null and userEmail != ''">
user_email=#{userEmail},
</if>
<if test="userCity != null and userCity != ''">
user_city=#{userCity},
</if>
<if test="age != null">
age=#{age}
</if>
</set>
where id=#{id}
</update>
foreach
foreach 标签可以迭代生成一系列值
**用于 SQL 的 in 语句 **
接口定义如下所示
public List<User> getUsersByIds(List<Integer> ids);
接口对应的 Mapper.xml 定义如下所示
<!--
collection: 指定要遍历的集合
默认情况下
如果为Collection类型的,key为collection;
如果为List类型的,key为list
如果是数组类型,key为array
可以通过@Param("ids")来指定key
item: 将当前遍历的元素赋值给指定的变量
open: 给遍历的结果添加一个开始字符
close: 给遍历的结果添加一个结束字符
separator: 每个元素之间的分隔符
-->
<select id="getUsersByIds"
resultType="com.example.mybatis.entity.User">
select * from user
where id in
<foreach collection="list" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
用于批量插入
接口定义如下所示
public int addUserList(List<User> users);
接口对应的 Mapper.xml 定义如下所示
<insert id="addUserList"
parameterType="com.example.mybatis.entity.User">
insert into user
(username, user_email, user_city, age)
values
<foreach item="user" collection="list" separator=",">
(#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})
</foreach>
</insert>
<!--返回自增主键-->
<insert id="addUserList"
parameterType="com.example.mybatis.entity.User"
useGeneratedKeys="true"
keyProperty="id">
insert into user
(username, user_email, user_city, age)
values
<foreach item="user" collection="list" separator=",">
(#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})
</foreach>
</insert>
<!--还可以这样写-->
<!--
这种方式需要数据库连接属性设置allowMultiQueries=true
这种分号分隔多个SQL还可以用于其他的批量操作,如修改、删除
-->
<insert id="addUserList"
parameterType="com.example.mybatis.entity.User">
<foreach item="user" collection="list" separator=";">
insert into user
(username, user_email, user_city, age)
values
(#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})
</foreach>
</insert>
<!--如果是Oracle数据库,则需要这样写-->
<insert id="addUserList"
parameterType="com.example.mybatis.entity.User">
<foreach item="user" open="begin" close="end;" collection="list">
insert into user
(username, user_email, user_city, age)
values
(#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age});
</foreach>
</insert>
lizc_lizc
发布了147 篇原创文章 · 获赞 34 · 访问量 13万+
私信
关注