MyBatis-动态SQL的if、choose、when、otherwise等使用

动态SQL是MyBatis最强大的特性之一。用于实现动态SQL的主要元素如下：

1、if

EmpMapper.xml配置

<select id="getEmpByIf" resultType="Emp" parameterType="Emp"> select * from emp where 1 = 1
 <if test="job != null and job != ''"> and job = #{job}
 </if>
 <if test="deptno != null "> and deptno = #{deptno}
 </if>
</select>

2、choose、when、otherwise

类似于Java中的switch case default

EmpMapper.xml配置

<select id="getEmpByChoose" resultType="Emp" parameterType="Emp"> select * from emp where 1 = 1
 <choose>
 <when test="job != null"> and job = #{job}
 </when>
 <when test="deptno != null"> and deptno = #{deptno}
 </when>
 <otherwise> and mgr = #{mgr}
 </otherwise>
 </choose>
</select>

3、where

EmpMapper.xml配置

<select id="getEmpByWhere" resultType="Emp" parameterType="Emp"> select * from emp
 <where>
 <if test="job != null and job != ''"> and job = #{job}
 </if>
 <if test="deptno != null"> and deptno = #{deptno}
 </if>
 </where>
</select>

4、set

EmpMapper.xml配置

<update id="updateEmpBySet" parameterType="Emp">
 update emp
 <set>
 <if test="ename != null and ename != ''">
 ename = #{ename},
 </if>
 <if test="job != null and job != ''">
 job = #{job},
 </if>
 </set> where empno = #{empno}</update>