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>