其实就是在sql语句上加一个判断条件而已,没啥特别的!!
where和if要结合使用:
1、若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字
2、若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方多余的and或or去掉
注意:where标签不能去掉条件最后多余的and
<select id="DynamicSQL" resultType="xxx">
select * from t_emp
<where><if test="userName !=null and userName !=''">
and user_name = #{userName}</if><if test="age !=null and age !=''">
and age = #{age}</if><if test="email !=null and email !=''">
and email = #{email}</if></where></select>
trim
trim用于去掉或添加标签中的内容
常用属性:
prefix:在trim标签中的内容的前面添加某些内容
prefixOverrides:在trim标签中的内容的前面去掉某些内容
suffix:在trim标签中的内容的后面添加某些内容
suffixOverrides:在trim标签中的内容的后面去掉某些内容
<select id="getDynamicSQL" resultType="xxx">
select * from t_emp
<trim prefix="where" suffixOverrides="and | or"><if test="name != '' and name != null">
ename = #{ename} and
</if><if test="age != '' and age != null">
age = #{age} and
</if><if test="sex != '' and sex != null">
sex = #{sex}</if></trim></select>
choose、when、otherwise
<select id="getEmpListByChoose" resultType="Emp">
select * from t_emp
<where><choose><when test="name != '' and name != null">
name = #{name}</when><when test="age != '' and age != null">
age = #{age}</when><when test="sex != '' and sex != null">
sex = #{sex}</when><when test="email != '' and email != null">
email = #{email}</when><otherwise>
did=1</otherwise></choose></where></select>
foreach
mybatis的foreach标签经常用于遍历集合,构建in条件语句或者批量操作语句。
<foreach collection="集合类型"open="开始的字符" close="结束的字符"
item="集合中的成员" separator="集合成员之间的分割符">
#{item的值}</foreach>======================================================================================================
案例:
<!--foreach循环简单类型的List--><select id="selectForeachOne" resultType="com.itjuzi.entity.Student">
select * from student
<if test="list != null and list.size>0">
where id in
<foreach collection="list"open="(" close=")" separator="," item="myId">
#{myId}</foreach></if></select>======================================================================================================<!--foreach循环对象List<Student>--><select id="selectForeachTwo" resultType="com.itjuzi.entity.Student">
select * from student
<if test="list != null and list.size>0">
where id in
<foreach collection="list"open="(" close=")" separator="," item="student">
#{student.id}</foreach></if></select>
<!--定义代码p片段--><sql id="studentSelect">
select * from student
</sql><select id="selectByIf" resultType="com.itjuzi.entity.Student"><include refid="studentSelect"/>
where id=-1<if test="name != null and name!='' ">
or name like "%" #{name}"%"</if><if test="age > 0">
or age >= #{age}</if></select>