动态 SQL
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格,还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。
动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
1. if
<select id="getUsersByNameLikeAndAge" resultType="com.chen.entity.User">
SELECT * FROM users WHERE
<if test="name != null and name != ''">name like #{name}</if>
<if test="age != null && age != 20">and age=#{age}</if>
</select>
特殊符号需要写为转义字符,其中 &&
需要被替换为 &&
。
2. where
在上面的例子中,当匹配条件并不完全匹配时,即如果出现只有第二个条件匹配时,SELECT * FROM users WHERE and age=#{age}
,又或者两个都不匹配时,SELECT * FROM users WHERE
,此时查询会失败。
第一种办法是在sql 语句中的 where 后加入永真式,并修改为
<select id="getUsersByNameLikeAndAge" resultType="com.chen.entity.User">
SELECT * FROM users WHERE 1=1
<if test="name != null and name != ''">and name like #{name}</if>
<if test="age != null && age != 20">and age=#{age}</if>
</select>
第二种办法可以通过 where 元素,它知道只有在一个以上的if条件有值的情况下才去插入 “WHERE” 子句。而且,若最后的内容是 “AND” 或 “OR” 开头的,where 元素也知道如何将他们去除。
<select id="getUsersByNameLikeAndAge" resultType="com.chen.entity.User">
SELECT * FROM users
<where>
<if test="name != null and name != ''">name like #{name}</if>
<if test="age != null && age != 20">and age=#{age}</if>
</where>
</select>
3. trim
可以通过自定义 trim 元素来定制我们想要的功能。
<trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">
...
</trim>
trim 有四个属性:
- prefix : 前缀,它会将拼接后的整个 sql 语句加一个前缀。
- prefixOverrides : 前缀覆盖,去掉 sql 语句前面多余的字符。
- suffix : 后缀,它会将拼接后的整个 sql 语句加一个后缀。
- suffixOverrides : 后缀覆盖,去掉 sql 语句前面多余的字符。
<select id="getUsersByNameLikeAndAge" resultType="com.chen.entity.User">
SELECT * FROM users
<trim prefix="where" suffixOverrides="and">
<if test="name != null and name != ''">name like #{name} and </if>
<if test="age != null && age != 20">age=#{age}</if>
</trim>
</select>
和 where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
4. set
类似的用于动态更新语句的解决方案叫做 set。set 元素可以被用于动态包含需要更新的列,而舍去其他的。
<update id="updateUserBySet">
UPDATE users
<set>
<if test="name!=null">name=#{name},</if>
<if test="age!=null">age=#{age}</if>
</set>
where id=#{id}
</update>
而和 set 元素等价的自定义 trim 元素为:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
5. choose 分支选择
有些时候,我们不想用到所有的条件语句,而只想从中择其一。针对这种情况,MyBatis 提供了 choose 元素,它像是 Java 中的带 break 的 switch 语句。
<select id="getUsersByChoose" resultType="com.chen.entity.User">
SELECT * FROM users
<where>
<choose>
<when test="id!=null">id = #{id}</when>
<when test="name!=null">name like #{name}</when>
<when test="age!=null">age = #{age}</when>
<otherwise>
age = 20
</otherwise>
</choose>
</where>
</select>
6. foreach
可以通过 foreach 元素来实现迭代,可以将任何可迭代对象(如列表、集合等)和任何的字典或者数组对象传递给foreach作为集合参数。
当使用可迭代对象或者数组时,index是当前迭代的次数,item的值是本次迭代获取的元素。
当使用字典(或者Map.Entry对象的集合)时,index是键,item是值。
其中 separator 是每个元素之间的分隔符,open 是所有结果的一个开始字符,close 是所有结果的一个结束字,#{变量名} 是取出当前遍历的元素。
<select id="getUsersByForeach" resultType="com.chen.entity.User">
SELECT * FROM users WHERE id IN
<foreach collection="list" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
批量插入数据
1.可以通过 foreach 批量插入数据
<insert id="addUsers">
INSERT INTO users(name,age,d_id) VALUES
<foreach collection="list" item="user" separator=",">
(#{user.name},#{user.age},#{user.department.id})
</foreach>
</insert>
此时 sql 语句可能会被拼接为
INSERT INTO users(name,age,d_id) VALUES (?,?,?) , (?,?,?) , (?,?,?)
2.通过 ;
来分隔多条查询
<insert id="addUsers">
<foreach collection="list" item="user" separator=";">
INSERT INTO users(name,age,d_id)
VALUES (#{user.name},#{user.age},#{user.department.id})
</foreach>
</insert>
此时 sql 语句可能会被拼接为
INSERT INTO users(name,age,d_id) VALUES (?,?,?) ; INSERT INTO users(name,age,d_id) VALUES (?,?,?) ; INSERT INTO users(name,age,d_id) VALUES (?,?,?)
但同时也会抛出 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException
异常,需要通过 mysql 中设置 allowMultiQueries (可以修改数据库配置文件url=jdbc:mysql:///mybatis?allowMultiQueries=true
)来允许通过 ;
来分隔多条查询,其默认为 false
。
7. 内置参数
mybatis 默认还有两个内置参数
_parameter
:代表整个参数
- 当传入单个参数:
_parameter
就是这个参数 - 当传入多个参数:参数会被封装为 map ,而
_parameter
就代表这个 map 。
- 当传入单个参数:
_databaseId
:如果配置了 databaseIdProvider 标签,则_databaseId
代表当前数据库的别名。
8. bind 元素
bind 元素可以将 OGNL 表达式的值绑定到一个变量中,方便后来引用这个变量的值。
<select id="getUserLike" resultType="com.chen.entity.User">
<bind name="_name" value="'%' + name + '%'" />
SELECT * FROM users
WHERE name LIKE #{_name}
</select>
9. sql 元素
sql 元素可以抽取可重用的 sql 片段,方便后来引用。可以通过 include 元素来引用外部定义的 sql 。而 include 可以自定义一些 property ,sql 元素内部可以使用自定义的属性(通过 ${property}
)。
<sql id="selectSql">
select * from ${tableName}
</sql>
<select id="getUsersByForeach" resultType="com.chen.entity.User">
<include refid="selectSql">
<property name="tableName" value="users"/>
</include>
WHERE id IN
<foreach collection="list" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>