MyBatis(七) 动态SQL

if元素和where元素

if元素根据入参的条件进行SQL的拼接，where 元素则是在条件语句存在的情况下， 才会有where

<select id="queryStudentInfoByStudent" resultType="student" >
  	select stu_id as stuId,
  		   stu_name as stuName,
  		   stu_sex as stuSex,
  		   stu_age as stuAge
  	 from student_info 
  	<where>
  		<if test="stuId!=null and stuId!=''">
  			and stu_id = #{stuId}
  		</if>
  		<if test="stuName!=null and stuName!=''">
  			and stu_name = #{stuName}
  		</if>
  	</where>
  </select>

choose when 条件判断

相当于sql中的case when

<select id="queryStudentInfoByStudent" resultType="student" >
  	select stu_id as stuId,
  		   stu_name as stuName,
  		   stu_sex as stuSex,
  		   stu_age as stuAge
  	 from student_info 
  	<where>
  		<choose>
  			<when test="stuId!=null and stuId!=''">
  				and stu_id = #{stuId}
  			</when>
  			<when test="stuName!=null and stuName!=''">
				and stu_name = #{stuName}  			
  			</when>
  			<otherwise>
  				and stu_sex = '1'
  			</otherwise>
  		</choose>
  	</where>
  </select>

trim元素

 trim将字符串去除，prefix是加上前缀，suffix是加上的后缀，prefixOverrides是去除的前缀字符串,suffixOverrieds去除的后缀字符串

<select id="queryStudentInfoByStudent" resultType="student" >
  	select stu_id as stuId,
  		   stu_name as stuName,
  		   stu_sex as stuSex,
  		   stu_age as stuAge
  	 from student_info 
	<trim prefix="where" prefixOverrides="and">
		<if test="stuId!=null and stuId!=''">
			and stu_id = #{stuId}
		</if>
	</trim>
  </select>

update+set+if元素实现选择更新

set元素会自动将末尾的逗号消除

 <update id="updateStudentInfo" parameterType="student">
  	update student_info 
  	<set>
  		<if test="stuName!=null and stuName!=''">
		  	 stu_name = #{stuName},
  		</if>
  		<if test="stuSex!=null and stuSex!=''">
			stu_sex = #{stuSex},
		</if>
		<if test="stuAge!=null and stuAge!=''">
			stu_age = #{stuAge}
		</if>
  	</set>
	where stu_id = #{stuId}
  </update>

 上面的可以是使用trim元素替换

<update id="updateStudentInfo" parameterType="student">
  	update student_info 
  	<trim prefix="set" suffixOverrides=",">
  		<if test="stuName!=null and stuName!=''">
		  	 stu_name = #{stuName},
  		</if>
  		<if test="stuSex!=null and stuSex!=''">
			stu_sex = #{stuSex},
		</if>
		<if test="stuAge!=null and stuAge!=''">
			stu_age = #{stuAge},
		</if>
	</trim>
	where stu_id = #{stuId}
  </update>

foreach元素

在sql片段中实现参数的遍历和组装功能

属性：collection是传入的参数名称，可以是set,list和数组

open 、close、separate: 拼接字符串的前后和分隔字符

item:当前元素

index:当前索引

public Student queryStudentInfoByStudent(Map<String,Object> map);

<select id="queryStudentInfoByStudent" resultType="student" parameterType="map">
  	select stu_id as stuId,
  		   stu_name as stuName,
  		   stu_sex as stuSex,
  		   stu_age as stuAge
  	 from student_info 
	<trim prefix="where" prefixOverrides="and">
		<if test="id!=null and id!=''">
			and stu_id = #{id}
		</if>
		<if test="itemlist!=null and itemlist!=''">
		    and stu_sex in 
			<foreach collection="itemlist"
			item="obj" index="index"
			 open="(" separator="," close=")">
			 #{obj}
			</foreach>
		</if>
	</trim>
  </select>

Map<String, Object> map = new HashMap<String, Object>();
String[] strs = {"1","0"};
map.put("id", 5);
map.put("itemlist", strs);
Student qbs = mapper.queryStudentInfoByStudent(map);
System.out.println(qbs);

日志：

DEBUG 2018-07-09 16:19:06,754 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:==>  Preparing: select stu_id as stuId, stu_name as stuName, stu_sex as stuSex, stu_age as stuAge from student_info where stu_id = ? and stu_sex in ( ? , ? ) 
DEBUG 2018-07-09 16:19:06,786 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:==> Parameters: 5(Integer), 1(String), 0(String)
DEBUG 2018-07-09 16:19:06,804 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:<==      Total: 1

bind元素

定义一个元素，在上下文中使用

 <select id="queryStudentInfoByStudent" resultType="student" parameterType="map">
  	<bind name="pattern_stuName" value="'%'+stuName+'%'"/>
  	select stu_id as stuId,
  		   stu_name as stuName,
  		   stu_sex as stuSex,
  		   stu_age as stuAge
  	 from student_info 
	<trim prefix="where" prefixOverrides="and">
		<if test="stuName!=null and stuName!=''">
			and stu_name like #{pattern_stuName}
		</if>
	</trim>
  </select>