一.if,choose
<select id="queryForList" resultType="Employee">
SELECT id,name,sn,salary,deptId FROM employee
where 1= 1
<if test="keyword!=null">
<bind name="keywordLike" value="'%'+keyword + '%'"/>
AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
</if>
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <= #{maxSalary}
</if>
<if test="deptId>0">
AND deptId = #{deptId}
</if>
</select>
<select id="query" resultType="Employee">
SELECT id,name,sn,salary,deptId FROM employee where 1=1
<choose>
<when test="deptId >0">
and dept_id = #{deptId}
</when>
<otherwise>
and detp_id IS NOT NULL
</otherwise>
</choose>
</select>
二.where,trim,set
where元素,如果查询条件 没有" WHERE " 关键词,则自动在查询条件前插入" WHERE " .如果查询条件以"AND"或"OR"开头,那么就会使用WHER关键词替换
<select id="queryForList" resultType="Employee">
SELECT id,name,sn,salary,deptId FROM employee
<where>
<if test="keyword!=null">
<bind name="keywordLike" value="'%'+keyword + '%'"/>
AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
</if>
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <= #{maxSalary}
</if>
<if test="deptId>0">
AND deptId = #{deptId}
</if>
</where>
</select>
set元素同where元素相似,也能根据set中的sql动态地去掉最后的逗号,并在前面添加set关键词,如果没有内容 ,也会选择忽略set语句 ,适用于update语句
<update id="update">
update employee
<set>
<if test="name!=null">
name = #{name},
</if>
<if test="sn!=null">
sn= #{sn},
</if>
<if test="salary!=null">
salary= #{salary},
</if>
</set>
</update>
Mapper文件
<delete id="batchDelete">
delete from employee
<foreach collection="ids" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
三.foreach
批量删除
Mapper接口
void batchDelete(@Param("ids")Long[] ids)
四.bind,sql,include
sql:可以把相同的sql片段起一个名字,并使用include元素在sql任意位置使用
<!-- 多个查询共同使用的的SQL -->
<sql id="base_where">
<where>
<if test="keyword!=null">
<bind name="keywordLike" value="'%'+keyword + '%'"/>
AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
</if>
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <= #{maxSalary}
</if>
<if test="deptId>0">
AND deptId = #{deptId}
</if>
</where>
</sql>
select id="queryForList" resultType="Employee">
SELECT id,name,sn,salary,deptId FROM employee
<include refid="base_where" />
<if test="pageSize>0">
LIMIT #{start},#{pageSize}
</if>
</select>
bind:使用OGNL表达式创建一个变量并将其绑定在上下文中
<select id="queryForList" resultType="Employee">
SELECT id,name,sn,salary,deptId FROM employee
<where>
<if test="keyword!=null">
<bind name="keywordLike" value="'%'+keyword + '%'"/>
AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
</if>
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <= #{maxSalary}
</if>
<if test="deptId>0">
AND deptId = #{deptId}
</if>
</where>
</select>
上一篇: MyBatis3.x整理:(二)MyBatis拓展
下一篇: MyBatis3.x整理:(四)对象关系映射