[学习笔记] MyBatis_08 动态SQL

动态SQL

MyBatis最引以为傲的功能, 开发者不用像使用JDBC时根据不同条件拼SQL语句. 任何使用过JSTL和XML的人都会眼熟动态SQL

---

if

官方文档例子:

<select id="findActiveBlogWithTitleLike" resultType="Blog">
	SELECT * FROM BLOG WHERE state = ‘ACTIVE’
	<if test="title != null">
		AND title like #{title}
	</if>
</select>

若有多个查询条件:

<select id="findActiveBlogLike" resultType="Blog">
	SELECT * FROM BLOG WHERE state = ‘ACTIVE’
	<if test="title != null">
		AND title like #{title}
	</if>
	<if test="author != null and author.name != null">
		AND author_name like #{author.name}
	</if>
</select>

---

choose, when, otherwise

有时候我们不需要用到所有查询条件, 而是从多个查询条件中选择一个(类似Java中的switch语句或是if-else). MyBatis提供了<choose>元素

<select id="findActiveBlogLike" resultType="Blog">
	SELECT * FROM BLOG WHERE state = ‘ACTIVE’
	<choose>
		<when test="title != null">
			AND title like #{title}
		</when>
		<when test="author != null and author.name != null">
			AND author_name like #{author.name}
		</when>
		<otherwise>
			AND featured = 1
		</otherwise>
	</choose>
</select>

---

where, set, trim

有时候我们不知道哪个查询条件会成为第一个查询条件, 甚至不知道会不会有查询条件, 而使用WHERE 1=1会影响性能, 此时可使用<WHERE>元素

WHERE元素

WHERE元素会判断查询条件是否有WHERE关键字, 如果没有, 则在第一个查询条件之前插入一个WHERE. 如果发现查询条件以AND或者OR开头, MyBatis会把第一个查询条件的AND/OR替换为WHERE

<select id="findActiveBlogLike" resultType="Blog">
	SELECT * FROM BLOG
	<where>
		<if test="state != null">
			AND state = #{state}
		</if>
		<if test="title != null">
			AND title like #{title}
		</if>
		<if test="author != null and author.name != null">
			AND author_name like #{author.name}
		</if>
	</where>
</select>

SET元素

和where类似, 根据set中的sql动态的去掉最后一个逗号, 并在最前面加上一个set关键字. 如果set元素中没有内容, 则会自动忽略set语句

<update id="updateAuthorIfNecessary">
	update Author
	<set>
		<if test="username != null">username=#{username},</if>
		<if test="password != null">password=#{password},</if>
		<if test="email != null">email=#{email},</if>
		<if test="bio != null">bio=#{bio}, </if>
	</set>
	where id=#{id}
</update>

trim元素

使用前提: trim元素中包含了一个字符串

prefix属性: 在这个字符串之前插入一个prefix属性值
prefixOverriddes属性: 字符串的内容以prefixOverriddes属性值开头(可能包含管道符号), 那么使用prefix属性值替换内容的开头
suffix属性: 在这个字符串之前插入一个suffix属性值
suffixOverriddes属性: 字符串的内容以suffixOverriddes属性值结尾(可能包含管道符号), 那么使用suffix属性值替换内容的开头

使用trim改写where:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
	...
</trim>

使用trim改写set:

<trim prefix="SET" suffixOverrides=",">
	...
</trim>

---

foreach

foreach元素用于对集合做迭代

官方文档案例

<select id="selectPostIn" resultType="domain.blog.Post">
	SELECT * FROM POST P WHERE ID in
	<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
			#{item}
	</foreach>
</select>

语法

foreach元素:
	collection属性: 表示对哪一个集合或数字做迭代
    				如果参数时数组类型: 此时Map的key为array
                    如果参数时List类型: 此时Map的key为list
                    我们也可以在参数上使用Param注解, 规定死Map中的key是什么
    open属性: 在迭代集合前使用什么符号
    close属性: 在迭代集合后使用什么符号
    separator属性: 迭代元素时, 元素间的分隔符是什么
    item属性: 被迭代的每一个元素的变量
    index属性: 迭代的索引

批量删除

需求: 删除ID为10, 20, 30的员工

SQL: DELETE FROM t_employee WHERE id IN (10, 20, 30);

Mapper接口:

	void batchDelete(@Param("ids")Longs[] ids);

Mapper文件:

	<delete>
    	DELETE FROM t_employee WHERE id IN
        <foreach collection="ids" open="(" close=")" separator="," item="id" >
        	#{id}
        </foreach>
    </delete>

批量插入

SQL:

INSERT INTO t_employee (name, salary, dept_id) VALUES ('A', 8000.00, 10),
													  ('B', 8000.00, 10), 
													  ('C', 8000.00, 20), 

Mapper接口:

	void batchInsert(List emps);

Mapper文件:

	<insert>
    	INSERT INTO t_employee (name, salary, dept_id) VALUES 
        <foreach collection="list" separator="," item="e" >
        	(#{e.name}, #{e.salary}, #{e.deptId})
        </foreach>
    </insert>

---

bind, sql, include

bind

使用OGNL创建一个变量并把它绑定到上下文中

不使用bind:

<select id="query" resultType="Employee">
	SELECT id, name,sn, salary FROM employee
    <where>
		<if test="keyword!=nul1">
			AND (name LIKE concat('%',#(keyword},'%') OR sn LiKE concat('%',#(keyword), '%'))
        </if>
    </where>
</select>

使用bind:

<select id= "query" resultType= "Employee">
	SELECT id, name, sn, salary FROM employee
    <where>
		<if test="keyword!=nul1">
			<bind name=" keywordlike" value="'%' + keyword + '%"/>
            AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
        </if>
    </where>
</select>

sql和include

使用sql可以给相同的sql片段起一个名字, 并用include元素在sql任意位置使用

<sql id="base_where">
	<where>
		<if test="keyword!=null">
			<bind name=" keywordlike" value="'%' + keyword + '%'"/>
			AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
		</if>
		<if test="minSalary!=null">
			AND salary >=#{minSalary}
		</if>
		<if test="maxsalary!=null">
			AND salary &lt;= #{maxsSalary}
		</if>
	< /where>
</sql>

<select id="query" resultType= "Employee">
	SELECT id, name, sn, salary FROM employee
	<include refid="base_where"/>
</select>