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>