在实际项目开发中, 经常需要根据不同条件拼接SQL语句, 拼接时还要确保不能忘了必要的空格, 有时候还要注意省掉列名列表最后的逗号, 等等。
动态SQL元素和使用JSTL或其他类似基于XML的文本处理器相似,My Batis采用功能强大的基于OGNL的表达式来完成动态SQL。 OGNL的表达式可以被用在任意的SQL映射语句中。
- <if>元素:
在MyBatis中,<if>元素 是最常用的判断语句,它类似:于Java中的if语句,主要用于实现某些简单的条件选择。其基本使用示例如下:
<!-- 2 登录功能的登录名和密码查询,查询已激活的员工(登录名和密码可以为空) -->
<select id="selectEmployeeByLoginLike" resultType="com.no8.domain.Employee" parameterType="MAP">
SELECT * FROM tb_employee WHERE state = 'ACTIVE'
<if test="loginname != null">
AND loginname = #{loginname}
</if>
<if test="PASSWORD != null">
AND PASSWORD = #{PASSWORD}
</if>
</select>
- <choose>元素:
<!-- 功能:如果传入了id,就根据id查询,没有传入id就根据loginname和password查询, 否则查询sex等于男的数据 -->
<!-- 相当于 if else 使用 -->
<select id="selectEmployeeChoose" resultType="com.no8.domain.Employee" parameterType="MAP">
SELECT * FROM tb_employee
<choose>
<when test="id != null">
where id=#{id}
</when>
<when test="loginname != null and PASSWORD != null">
where loginname = #{loginname} AND PASSWORD = #{PASSWORD}
</when>
<otherwise>
where sex = '男'
</otherwise>
</choose>
</select>
3.< where>、<trim>元素:
<!-- 功能:如果传入了state,就根据state查询,如果传入了id,就根据id查询,没有传入id就根据loginname和password查询,否则sql语句出错-->
<select id="findEmployeeLike"
resultType="com.no8.domain.Employee">
SELECT * FROM tb_employee WHERE
<if test="state != null ">
state = #{state}
</if>
<if test="id != null ">
and id = #{id}
</if>
<if test="loginname != null and password != null">
and loginname = #{loginname} and password = #{password}
</if>
</select>
针对上述情况中“where 1=1”,在MyBatis的SQL中就可以使用<where>或<trim>元素进行动态处理。
<where>会自动判断SQL语句,只有<where>内的条件成立时,才会在拼接SQL中加入where关键字,否则将不会添加;还会去除多余的“AND”或“OR”。
<!-- 功能:如果传入了state,就根据state查询,如果传入了id,就根据id查询,没有传入id就根据loginname和password查询,否则查询所有员工-->
<select id="selectEmployeeLike" resultType="com.no8.domain.Employee">
SELECT * FROM tb_employee
<where>
<if test="state != null ">
state = #{state}
</if>
<if test="id != null ">
and id = #{id}
</if>
<if test="loginname != null and password != null">
and loginname = #{loginname} and password = #{password}
</if>
</where>
</select>
<trim>的作用是去除特殊的字符串,它的prefix属性代表语句的前缀,prefixOverrides属性代表需要去除的哪些特殊字符串,功能和<where> 基本是等效的。
- <set>元素:
在Hibernate中,想要更新某个对象,就需要发送所有的字段给持久化对象,这种想更新的每一条数据都要将其所有的属性都更新一-遍的方法,其执行效率是非常差的。为此,在MyBatis中 可以使用动态SQL中的<set>元素进行处理:
使用<set>和<if>元素对username和jobs进行更新判断,并动态组装SQL。这样就只需要传入想要更新的字段即可.
<!-- 功能:根据id动态修改员工相关信息,即动态更新需要修改的字段员工相关信息 -->
<update id="updateEmployeeIfNecessary" parameterType="com.no8.domain.Employee">
update tb_employee
<set>
<if test="loginname != null">loginname=#{loginname},</if>
<if test="password != null">password=#{password},</if>
<if test="name != null">NAME=#{name},</if>
<if test="sex != null">sex=#{sex},</if>
<if test="age != null">age=#{age},</if>
<if test="phone != null">phone=#{phone},</if>
<if test="sal != null">sal=#{sal},</if>
<if test="state != null">state=#{state}</if>
</set>
where id=#{id}
</update>
- <foreach>元素:
关于动态SQL另外一个常用的操作就是需要对一个集合进行遍历, 通常发生在构建的条件语句时。foreach元素的功能非常强大, 它允许指定一个集合, 声明可以用在元素体内的集合项和索引变量。它也允许指定开闭匹配的字符串以及在法代中间放置分隔符。这个元素是很智能的,因此它不会随机地附加多余的分隔符。
<!-- foreach :
item:配置的是循环中当前的元素
index: 配置的是当前的元素在集合的位置下标
collection:配置的list是传递过来的参数类型
open,close:配置的是以什么符号将这些集合元素包装起来
separator:配置的是各个元素的间隔符
-->
<!-- 功能:查询 id 在指定集合内的员工,如id在(2,3)之间的员工-->
<select id="selectEmployeeIn"
resultType="com.no8.domain.Employee">
SELECT * FROM tb_employee WHERE ID in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</select>
在使用<foreach>时最关键也是最容易出错的就是collection属性,该属性是必须指定的,而且在不同情况下,该属性的值是不一样的。主要有以下3种情况:
- 如果传入的是单参数且参数类型是一个数组或者List的时候,collection属性值分别为array和list (或collection)。
- 如果传入的参数是多个的时候,就需要把它们封装成一个Map了 ,当然单参数也可以封装成Map集合,这时候collection属性值就为Map的键。
- 如果传入的参数是POJO包装类的时候,collection属性值就为该包装类中需要进行遍历的数组或集合的属性名。
6 <bind>元素:
针对上述所知,映射文件中的SQL就要根据不同的情况提供不同形式的实现,这显
然是比较麻烦的,且不利于项目的移植。为了减少这种麻烦,就可以使用MyBatis的<bind>元素来解决这一问题。
<!-- bind -->
<!-- 功能:模糊查询登录名loginname中包含参数字符的员工-->
<!-- _parameter. getUsername()表示传递进来的参数(也可以直接写成对应的参数变量名,如userniame)
需要的地方直接引用<bind>元素的name属性值即可
-->
<select id="selectEmployeeLikeName"
resultType="com.no8.domain.Employee">
<bind name="pattern" value="'%' + _parameter.getName() + '%'" />
SELECT * FROM tb_employee
WHERE loginname LIKE #{pattern}
</select>