1、<if>语句
<!-- <select id="getEmployoeeByIf" resultType="com.atguigu.mybatis.entities.Employee"> -->
<!-- select * from employees -->
<!-- <where> -->
<!-- <if test="id != null"> -->
<!-- id = #{id} and -->
<!-- </if> -->
<!-- <if test="lastName != null and lastName != ''"> -->
<!-- last_name = #{lastName} and -->
<!-- </if> -->
<!-- <if test="email != null and email.trim() != ''"> -->
<!-- email = #{email} and -->
<!-- </if> -->
<!-- <if test="salary != 0"> -->
<!-- salary = #{salary} and -->
<!-- </if> -->
<!-- 1 = 1 -->
<!-- </where> -->
<!-- </select> -->
2、trim 语句
<select id="getEmployoeeByIf" resultType="com.atguigu.mybatis.entities.Employee">
select * from employees
<!--
prefix:设置前缀
prefixOverrides:前缀覆盖,即将前缀置空
suffix:设置后缀
suffixOverrides:后缀覆盖,即将后缀置空
-->
<trim prefix="where" suffixOverrides="and">
<if test="id != null">
id = #{id} and
</if>
<if test="lastName != null and lastName != ''">
last_name = #{lastName} and
</if>
<if test="email != null and email.trim() != ''">
email = #{email} and
</if>
<if test="salary != 0">
salary = #{salary} and
</if>
</trim>
</select>
<!--
问题一:当每个条件前面有and时
-如果第一个条件不满足,那么sql语句会有错误
解决方案一:
-在where后面添加 1=1(不推荐使用)
解决方案二:
-使用where标签,可以自动去除每个条件前面的and,但是不能去除后面的and
问题二:当每个条件后面有and时
-如果最后一个条件不满足,那么sql语句会有错误
解决方案一:
-在sql语句的最后面添加 1=1(不推荐使用)
解决方案二:
-使用trim标签
-->
三、choose标签,一个条件满足后边条件都不看了
<!-- 通过choose-->
<select id="getEmployeeByChoose" resultType="com.atgui.spring.mybatis.entities.Employee">
select * from employees where
<choose>
<when test="id !=null">
id=#{id}
</when>
<when test="lastName !=null">
last_name=#{lastName}
</when>
<when test="email !=null">
email=#{email}
</when>
<when test="salary !=0">
salary=#{salary}
</when>
</choose>
</select>
四、forech标签
public List<Employee> listEmployeeByForeach(@Param("ids")List <Integer> ids);
<select id="getEmployeesByForeach" resultType="com.atguigu.mybatis.entities.Employee">
<include refid="repeatSql"/> where id in
<!--
collection:要遍历的集合
item:指定一个变量接受遍历到的值
open:指定一个开始的符号
close:指定一个结束的符号
separator:指定一个遍历得到的每个数之间的分割符
index:指定索引
-->
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
五、sql标签
<sql id="repeatSql">
select * from employees
</sql>
<select id="listEmployeeByForeach" resultType="com.atgui.spring.mybatis.entities.Employee">
<include refid="repeatSql" /> where id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>