Mybatis笔记 04：动态SQL

1. if 判断

<select id="selectEmployees" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	where
		<if test="id != null and id != ''">id = #{id}</if>
		<if test="lastName != null and lastName != ''">and last_name LIKE #{lastName}</if>
		<if test="email != null and email.trim() != ''">and email = #{email}</if>
		<if test="gender == 0 or gender == 1">and gender = #{gender}</if>
</select>

• 其中test属性的值为判断条件，使用OGNL表达式（OGNL官网）
• test内部的条件判断的是接收的参数的值
• OGNL可以自动进行数字和字符串之间的转换

此时存在一个问题，如果不传入id时，语句就会出现where and last_name =这种类似的语法错误

其中一种解决方式为：

<select id="selectEmployees" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	where 1=1
		<if test="id != null and id != ''">and id = #{id}</if>
		<if test="lastName != null and lastName != ''">and last_name LIKE #{lastName}</if>
		<if test="email != null and email.trim() != ''">and email = #{email}</if>
		<if test="gender == 0 or gender == 1">and gender = #{gender}</if>
</select>

此时任何一个条件前都会有1=1这个条件，每个条件都可以带上and

2. where标签

<select id="selectEmployees" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	<where> 
		<if test="id != null and id != ''">and id = #{id}</if>
		<if test="lastName != null and lastName != ''">and last_name LIKE #{lastName}</if>
		<if test="email != null and email.trim() != ''">and email = #{email}</if>
		<if test="gender == 0 or gender == 1">and gender = #{gender}</if>
	</where> 
</select>

此时Mybatis会自动去掉第一个多余的and或or，但是如果将and写在了查询条件的后面来达到拼接的效果，where标签无效，此时的语句仍有可能出现语法错误

3. trim标签

自定义字符串的截取规则，trim标签体中是整个字符串拼串后的结果

<select id="selectEmployees" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	<trim prefix="where" suffixOverrides="and">
		<if test="id != null and id != ''">id = #{id} and</if>
		<if test="lastName != null and lastName != ''">last_name LIKE #{lastName} and</if>
		<if test="email != null and email.trim() != ''">email = #{email} and</if>
		<if test="gender == 0 or gender == 1">gender = #{gender} and</if>
    </trim> 
</select>

• prefix属性：给拼串后的整个字符串加一个前缀
• prefixOverrides属性：前缀覆盖，去掉整个字符串前面多余的字符
• suffix属性：给拼串后的整个字符串加一个后缀
• suffixOverrides属性：后缀覆盖，去掉整个字符串后面多余的字符

4. choose标签

类似分支选择switch-case-break，每次只进入一个分支

<select id="selectEmployees" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	<where>
    	<choose>
    		<when test="id != null">id = #{id}</when>
    		<when test="lastName != null">last_name like #{lastName}</when>
    		<when test="email != null">email like #{email}</when>
    		<otherwise>gender = 0</otherwise>
    	</choose>
    </where>
</select>

5. set标签

<update id="updateEmployees">
	update tbl_employee
	<set>
    	<if test="lastName != null">last_name = #{lastName},</if>
    	<if test="email != null">email = #{email},</if>
    	<if test="gender != null">gender = #{gender},</if>
    </set>
    where id = #{id}
</update>

此时mybatis会去掉多余的逗号

6. foreach标签

<select id="selectEmployees" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	where id in 
	<foreach collection="list" item="item_id" open="(" close=")" separator=",">
		#{item_id}
	</foreach>
</select>

• collection属性：指要遍历的集合
• item属性：将当前遍历出的元素赋值给指定的变量
• separator属性：每个元素之间的分隔符
• open属性：遍历出所有结果拼接一个开始的字符
• close属性：遍历出所有结果拼接一个结束的字符
• index属性：遍历list时的索引（遍历map的时候表示的是map的key，item是值）
• #{变量名}：取出变量的值，即当前遍历出的元素

此时的SQL语句为：

select id, last_name, email, gender from tbl_employee where id in ( ? , ? , ? )

用foreach实现批量插入

<insert id="insertEmployees">
	insert into tbl_employee (last_name, email, gender, dept_id) values
	<foreach collection="list" separator="," item="item">
		(#{item.lastName}, #{item.email}, #{item.gender}, #{item.dept.id})
	</foreach>
</insert>

<insert id="insertEmployees">
	<foreach collection="list" separator=";" item="item">
		insert into tbl_employee (last_name, email, gender, dept_id) values
		(#{item.lastName}, #{item.email}, #{item.gender}, #{item.dept.id})
	</foreach>
</insert>

第二种方式需要数据库连接支持执行用分号分隔的多条SQL语句，需要在URL后加上参数allowMultiQueries=true，同时该种方式由于语句独立，还可以实现批量删除和批量修改的操作

7. 两个内置参数

• _parameter：代表整个参数
• _databaseId：如果配置了databaseIdProvider标签，该参数的值就是当前数据库的别名

<select id="selectEmployees" resultType="com.dudu.domain.Employee">
	<if test="_databaseId = mysql">
		select * from tbl_employee
		<if test="_parameter != null">
			where id = {_parameter.id}
		</if>
	</if>
</select>

8. bind标签

可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值

<select id="selectEmployees" resultType="com.dudu.domain.Employee">
	<bind name="_lastName" value=" '%' + lastName + '%' "/>
	select * from tbl_employee where last_name like #{_lastName}
</select>

9. sql标签

抽取可重用的SQL片段，方便后面引用

<sql id="columns">
	id, last_name, email, gender, ${newColumn}
</sql>
<select id="selectEmployees" resultType="com.dudu.domain.Employee">
	select
	<include refid="columns">
		<property name="newColumn" value="dept_id"/>
	</include>
	from tbl_employee
	where last_name like #{lastName}
</select>

• 通过include标签在语句中引用
• sql标签内部也可以写动态标签
• 通常情况下都会将要查询的列名或插入用的列名抽取出来
• 可以在include标签内自定义一些property，sql标签内部就可以使用自定义的属性${}