动态sql
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
2.1 where自动删首and
2.2 set自动删尾逗号,
2.3 choose-when相当于java中的switch-case,otherwise不是必要的
2.4 使用select时需要指定resultType或者resultMap
2.5 trim可以代替where和set使用
2.6 foreach多用于in后
2.7 include可以提高代码复用但是降低代码可读性
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjsxt.mapper.StudentMapper">
<select id="selectByChoose" resultType="Student">
select id,name,age,score from student
<where>
<choose>
<when test="name != null and name !=''">
and name like '%' #{name} '%'
</when>
<when test="age > 0">
and age >#{age}
</when>
<otherwise>
and 1=1
</otherwise>
</choose>
</where>
</select>
<update id="updateBySet">
update student
<set>
<if test="name != null and name !=''">
name=#{name},
</if>
<if test="age > 0">
age=#{age},
</if>
<if test="score > 0">
score=#{score}
</if>
</set>
where id=#{id}
</update>
<select id="selectByTrim" resultType="Student">
select id,name,age,score from student
<trim prefix="where" prefixOverrides="AND |OR">
<if test="name != null and name !=''">
and name like '%' #{name} '%'
</if>
<if test="age > 0">
and age>#{age}
</if>
</trim>
</select>
<update id="updateByTrim">
update student
<trim prefix="set" suffixOverrides=",">
<if test="name != null and name !=''">
name=#{name},
</if>
<if test="age > 0">
age=#{age},
</if>
<if test="score > 0">
score=#{score}
</if>
</trim>
where id=#{id}
</update>
<select id="forEachArray" resultType="Student">
<!-- select id,name,age,score from student where id in(1,2) -->
select id,name,age,score from student
<if test="array.length >0">
where id in
<foreach collection="array" item="myid" open="(" close=")" separator=",">
#{myid}
</foreach>
</if>
</select>
<select id="forEachList" resultType="Student">
<!-- select id,name,age,score from student where id in(1,2) -->
select id,name,age,score from student
<if test="list.size >0">
where id in
<foreach collection="list" item="myid" open="(" close=")" separator=",">
#{myid}
</foreach>
</if>
</select>
<select id="forEachList1" resultType="Student">
<!-- select id,name,age,score from student where id in(1,2) -->
<include refid="selectAll"/> from student
<if test="list.size >0">
where id in
<foreach collection="list" item="stu" open="(" close=")" separator=",">
#{stu.id}
</foreach>
</if>
</select>
<sql id="selectAll">
select id,name,age,score
</sql>
</mapper>