动态SQL
一、
test属性:对POJO对象的属性进行非空判断,如果传入的查询条件非空,就进行动态SQL组装。
<select id="selectEmployee" resultType="employee" parameterType="employee"> select * from employee where 1=1 <if test="id != null and id != ''"> and id = #{id} if> <if test="name != null and name != ''"> and name = #{name} if> <if test="salary != null and salary != ''"> and salary = #{salary} if> select>
二、
注意:
<select id="selectEmployee2" resultType="employee" parameterType="employee"> select * from employee where 1=1 <choose> <when test="id != null and id != ''"> and id = #{id} when> <when test="name != null and name != ''"> and name like concat('%',#{name},'%') when> <otherwise> and salary > #{salary} otherwise> choose> select>
三、
替换掉动态SQL查询时“where 1=1”的条件,衍生出来的
<select id="selectEmployee3" resultType="employee" parameterType="employee"> select * from employee <where> <if test="id != null and id != ''"> and id = #{id} if> <if test="name != null and name != ''"> and name like concat('%',#{name},'%') if> <if test="salary != null"> and salary > #{salary} if> where> select>
prefix元素:代表语句的前缀,用where连接
prefixOverrides元素:去除那些多余特殊的字符串,比如and和or
<select id="selectEmployee4" resultType="employee" parameterType="employee"> select * from employee <trim prefix="where" prefixOverrides="AND"> <if test="id != null and id != ''"> and id = #{id} if> <if test="name != null and name != ''"> and name like concat('%',#{name},'%') if> <if test="salary != null"> and salary > #{salary} if> trim> select>
四、
概述:进行动态的SQL语句更新操作,让程序更新需要更新的字段
主要作用:在动态的SQL语句前面输出set关键字,并将SQL语句中最后一个多余的逗号去除
注意:如果
<update id="updateEmployee" parameterType="employee"> update employee <set> <if test="name != null and name != ''"> name = #{name}, if> <if test="salary != null"> salary = #{salary}, if> set> <where> id = #{id} where> update>
五、
collection属性:集合为list(或collection)、数组为array
item属性:需要遍历的项
index属性:当前元素的下标
open、close属性:配置以什么符号将这些元素包装起来
separator属性:配置各个元素之间的分隔符
<select id="selectEmployee5" resultType="employee" parameterType="List"> select * from employee where id in <foreach collection="list" item="id" open="(" separator="," close=")"> #{id} foreach> select> <select id="selectEmployee6" resultType="employee" parameterType="int[]"> select * from employee where id in <foreach collection="array" item="id" open="(" separator="," close=")"> #{id} foreach> select>
六、
name属性:定义变量,直接引用该变量就可以进行动态的组装
value属性:进行拼接查询的字符串
<select id="selectEmployee7" resultType="employee" parameterType="employee"> <bind name="p_name" value="'%'+_parameter.getName()+'%'"/> select * from employee where name like ${p_name} select>