按照官网的分类,MyBatis 的动态标签主要有四类:if,choose (when, otherwise),trim (where, set),foreach。
(案例在spring-mybatis 工程中)
if —— 需要判断的时候,条件写在test 中
以下语句可以用<where>改写
<select id="selectDept" parameterType="int"
resultType="com.leon.crud.bean.Department">
select * from tbl_dept where 1=1
<if test="deptId != null">
and dept_id = #{deptId,jdbcType=INTEGER}
</if>
</select>
choose (when, otherwise) —— 需要选择一个条件的时候
<select id="getEmpList_choose" resultMap="empResultMap"
parameterType="com.leon.crud.bean.Employee">
SELECT * FROM tbl_emp e
<where>
<choose>
<when test="empId !=null">
e.emp_id = #{emp_id, jdbcType=INTEGER}
</when>
<when test="empName != null and empName != ''">
AND e.emp_name LIKE CONCAT(CONCAT('%', #{emp_name,
jdbcType=VARCHAR}),'%')
</when>
<when test="email != null ">
AND e.email = #{email, jdbcType=VARCHAR}
</when>
<otherwise>
</otherwise>
</choose>
</where>
</select>
trim (where, set)——需要去掉where、and、逗号之类的符号的时候。
注意最后一个条件dId 多了一个逗号,就是用trim 去掉的:
<update id="updateByPrimaryKeySelective"
parameterType="com.leon.crud.bean.Employee">
update tbl_emp
<set>
<if test="empName != null">
emp_name = #{empName,jdbcType=VARCHAR},
</if>
<if test="gender != null">
gender = #{gender,jdbcType=CHAR},
</if>
<if test="email != null">
email = #{email,jdbcType=VARCHAR},
</if>
<if test="dId != null">
d_id = #{dId,jdbcType=INTEGER},
</if>
</set>
where emp_id = #{empId,jdbcType=INTEGER}
</update>
trim 用来指定或者去掉前缀或者后缀:
<insert id="insertSelective" parameterType="com.leon.crud.bean.Employee">
insert into tbl_emp
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="empId != null">
emp_id,
</if>
<if test="empName != null">
emp_name,
</if>
<if test="dId != null">
d_id,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="empId != null">
#{empId,jdbcType=INTEGER},
</if>
<if test="empName != null">
#{empName,jdbcType=VARCHAR},
</if>
<if test="dId != null">
#{dId,jdbcType=INTEGER},
</if>
</trim>
</insert>
foreach —— 需要遍历集合的时候:
<delete id="deleteByList" parameterType="java.util.List">
delete from tbl_emp where emp_id in
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item.empId,jdbcType=VARCHAR}
</foreach>
</delete>
动态SQL 主要是用来解决SQL 语句生成的问题。